Forum Discussion
Average and Percentage Help
Hello,
I know how to do an average function, but I am having a hard time doing the average if the variables changes. So what I have is 3 aisles that I rate on a scale of 0-3s or N/A with 3 being perfect score. The score is an average of the 3 aisles, with a percentage Pass/Fail. I have learned that sometimes I only rate an area based on 2 aisles. What I need help with is an average formula and percent formula if aisle 3 is blank.
Normally I can base the perfect score off of an average of 24 points or 21 points. I will definitely have an N/A score like in aisle 1, but it is also possible to have a perfect score like in aisle 2 with a second N/A.
So as you can see there are many different variables and scenarios that I need an average formula that will only count scores of 0-3 and average them between 1-3 aisles. I hope this makes since. Below is an example of what I am looking at.
24 possible points p0ossible (24 points per row x 3 rows = 72 now divide by 3 = average of 24 possible points)
3 Replies
Given your perfect score of 24, the score should be calculated like this to return 24 and to result in a percentage of 100%:
=AVERAGEIF(Aisle1:Aisle3, ">=0")*8catherine9910
That could be like=SUM(Aisle1:Aisle3) / COUNT(Aisle1:Aisle3)
