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!