Dec 02 2020 10:13 PM
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)
Dec 02 2020 10:55 PM
Dec 03 2020 02:18 AM
@catherine9910
That could be like
=SUM(Aisle1:Aisle3) / COUNT(Aisle1:Aisle3)
Dec 03 2020 03:55 AM
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")*8