# Formula percentage with additional formula for roll up percentages

Options
edited 12/09/19

I am trying to calculate a rolled up percentage of how many students have taken a test, scoring above 80% to those who have not taken the test at all. I have tried an AVG formula as a test for 5 takers, but the % is incorrect. Here is the formula I have used:

=AVG([TEST 1: Establish Point to Point Connection]3:[TEST 1: Establish Point to Point Connection]7, (5))

The result here is 81%. However, when I use the same formula for a field where only (1) student took the test out 5, I get a result of 52.5. By math, the correct answer should be 20%. Here is this formula:

=AVG([TEST 2: Setting IP Address]3:[TEST 2: Setting IP Address]7, (5))

Can anyone help me with what I am doing wrong? I will have many tests by the time this project is complete. My next step is writing a formula to rollup across multiple test takers. Do you have any advice for calculating roll up percentages as well? Here is a snip of what I am working on.

Tags:

• ✭✭✭✭✭✭
Options

try removing the comma and 5.

=AVG([TEST 2: Setting IP Address]3:[TEST 2: Setting IP Address]7

What results do you get with that formula?

• Options
• ✭✭✭✭✭✭
Options

Hmmm. it appears that Smartsheet doesn't count blank cells in the Average. We'll have to do this manually. Try this...

=count([TEST 2: Setting IP Address]3:[TEST 2: Setting IP Address]7) / count([Name, First]3:[Name, First]7)

That should count the number of people in your range and divide it by the number of first names appearing in your range.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!