Average and Percentage Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1951266%22%20slang%3D%22en-US%22%3EAverage%20and%20Percentage%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1951266%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20know%20how%20to%20do%20an%20average%20function%2C%20but%20I%20am%20having%20a%20hard%20time%20doing%20the%20average%20if%20the%20variables%20changes.%20So%20what%20I%20have%20is%203%20aisles%20that%20I%20rate%20on%20a%20scale%20of%200-3s%20or%20N%2FA%20with%203%20being%20perfect%20score.%20The%20score%20is%20an%20average%20of%20the%203%20aisles%2C%20with%20a%20percentage%20Pass%2FFail.%20I%20have%20learned%20that%20sometimes%20I%20only%20rate%20an%20area%20based%20on%202%20aisles.%20What%20I%20need%20help%20with%20is%20an%20average%20formula%20and%20percent%20formula%20if%20aisle%203%20is%20blank.%26nbsp%3B%3C%2FP%3E%3CP%3ENormally%20I%20can%20base%20the%20perfect%20score%20off%20of%20an%20average%20of%2024%20points%20or%2021%20points.%20I%20will%20definitely%20have%20an%20N%2FA%20score%20like%20in%20aisle%201%2C%20but%20it%20is%20also%20possible%20to%20have%20a%20perfect%20score%20like%20in%20aisle%202%20with%20a%20second%20N%2FA.%3C%2FP%3E%3CP%3ESo%20as%20you%20can%20see%20there%20are%20many%20different%20variables%20and%20scenarios%20that%20I%20need%20an%20average%20formula%20that%20will%20only%20count%20scores%20of%200-3%20and%20average%20them%20between%201-3%20aisles.%20I%20hope%20this%20makes%20since.%20Below%20is%20an%20example%20of%20what%20I%20am%20looking%20at.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Question.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237463iF1BFAF6B7DFCB7BA%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Question.PNG%22%20alt%3D%22Question.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%8324%20possible%20points%20p0ossible%20(24%20points%20per%20row%20x%203%20rows%20%3D%2072%20now%20divide%20by%203%20%3D%20average%20of%2024%20possible%20points)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1951266%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1951309%22%20slang%3D%22en-US%22%3ERe%3A%20Average%20and%20Percentage%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1951309%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F888027%22%20target%3D%22_blank%22%3E%40catherine9910%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20if%20formula%20for%20sum%20only%20if%20value%20is%20greater%20than%200%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1951772%22%20slang%3D%22en-US%22%3ERe%3A%20Average%20and%20Percentage%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1951772%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F888027%22%20target%3D%22_blank%22%3E%40catherine9910%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(Aisle1%3AAisle3)%20%2F%20COUNT(Aisle1%3AAisle3)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1952016%22%20slang%3D%22en-US%22%3ERe%3A%20Average%20and%20Percentage%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1952016%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F888027%22%20target%3D%22_blank%22%3E%40catherine9910%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGiven%20your%20perfect%20score%20of%20%3CSTRONG%3E24%3C%2FSTRONG%3E%2C%20the%20score%20should%20be%20calculated%20like%20this%20to%20return%20%3CSTRONG%3E24%3C%2FSTRONG%3E%20and%20to%20result%20in%20a%20percentage%20of%20%3CSTRONG%3E100%25%3C%2FSTRONG%3E%3A%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DAVERAGEIF(Aisle1%3AAisle3%2C%0A%22%26gt%3B%3D0%22)*8%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

Question.PNG

 24 possible points p0ossible (24 points per row x 3 rows = 72 now divide by 3 = average of 24 possible points)

3 Replies

@catherine9910 

 

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

@catherine9910 
That could be like

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

@catherine9910 

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