Formula percentage with additional formula for roll up percentages

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.

 

 

smartsheet.PNG

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!