Calculating Percentages Formula for Multiple Rows?

Cyrus Baluyot

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?

Paul Newcome

Try an AVG/COLLECT combo.

Cyrus Baluyot

Hi Paul,

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

Paul Newcome

You would use something like

=AVG(COLLECT([% Column]:[% Column], [Name Column]:[Name Column], @cell = [Name Column]@row))

Cyrus Baluyot

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

Paul Newcome

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

Cyrus Baluyot

Paul Newcome

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

Cyrus Baluyot

Thats part of the raw data imported into the Smartsheet.

Paul Newcome

What happens if you manually type over those percentages?

Cyrus Baluyot

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.

Paul Newcome

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.

Cyrus Baluyot

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!

Paul Newcome

Happy to help. 👍️