Formula percentage with additional formula for roll up percentages

Anita.Lillibridge63671

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.

 

 

Mike Wilday

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? 

Anita.Lillibridge63671

100

Mike Wilday

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.