Calculating Percentages Formula for Multiple Rows?
Is there a formula for when add I raw data, the average can be calculated for multiple rows? See my screenshot:
I would want the Adherence Average column to show the average percentage of Break and Lunch for Agent Name. I did a basic AVG function for Person A's Break and Lunch to get their Adherence Average, but would it be possible for the rest of the Adherence Averages to show for the other Persons?
Answers

Try an AVG/COLLECT combo.

Hi Paul,
Not sure what that formula would look like. Would I also need a help column to help differentiate the different Agents?

You would use something like
=AVG(COLLECT([% Column]:[% Column], [Name Column]:[Name Column], @cell = [Name Column]@row))

i get the DIVIDE BY ZERO Error. It only works for the first person:

Can you show the formula actually in the sheet similar to the screenshot below?


That's odd. How is the % column being populated?

Thats part of the raw data imported into the Smartsheet.

What happens if you manually type over those percentages?

If I type over the percentages and change them to decimal form, then it changes the Adherence Average:
This might be what I'm looking for.

So it sounds like some of the percentages are being stored as text values.
When you import, you may need to have a helper column that strips the "%" and divides the VALUE by 100 then calculate from there.
=VALUE(SUBSTITUTE([% Column]@row, "%", "")) / 100
Then reference the helper column in your average formula and adjust the column settings to show as a percentage.

We got it to work. Before copying and pasting the excel data into smartsheet, we formatted the percentage cells into number up to 4 decimal spots, so once copied and pasted into the smartsheet, the formula is able to average out the percentages. Thanks again, Paul!

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!