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.
Comments

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?

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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!