# Calculating Percentages Formula for Multiple Rows?

Options

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?

• ✭✭✭✭✭✭
Options

Try an AVG/COLLECT combo.

• Options

Hi Paul,

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

• ✭✭✭✭✭✭
Options

You would use something like

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

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options
• ✭✭✭✭✭✭
Options

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

• Options

Thats part of the raw data imported into the Smartsheet.

• ✭✭✭✭✭✭
Options

What happens if you manually type over those percentages?

• Options

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.

• ✭✭✭✭✭✭
Options

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.

• Options

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!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!