# Average and Percentage Help

Occasional Contributor

# 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

# Re: Average and Percentage Help

Try if formula for sum only if value is greater than 0

# Re: Average and Percentage Help

@catherine9910
That could be like

``=SUM(Aisle1:Aisle3) / COUNT(Aisle1:Aisle3)``

# Re: Average and Percentage Help

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``````