Thread: AverageIfs Complexity Thanks: 0 Likes: 0

1. AverageIfs Complexity

Good afternoon,

I have a problem... I am hoping someone can help.

I have a spread-sheet which contains various codes in Column C (30,40,90,95,99) and value of percentage in Column G.

I want to take the values 30, 40 and average all amounts including 0% and I also want to include values 90, 95, 99 but with a value greater than 0% (not include 0%)

Currently I have it calculating everything greater than 0%

=AVERAGEIFS(\$G\$2:\$G\$109,\$C\$2:\$C\$109,"<>30",\$G\$2:\$G\$109,">0") <
A B
 WLKITL WLDOCO WASRST WADRQJ WLUORG WLSOQS PERCENTAGE Part 1434671 99 07/31/18 630 630 100.00% 1434672 99 08/01/18 900 900 100.00% 1434673 90 08/02/18 630 630 100.00%

Basically when I average this I filter anything containing a 90, 95, 99 and delete all 0% then average like normal... want something faster.

Thank you

2. Re: AverageIfs Complexity

Care to state clearly what must column C and column must meet?

3. Re: AverageIfs Complexity

It's all about the average Column C is the stage in which the part is...

30 is beginning, all 0% is counted
40 is work started, all % greater than 0 is counted
90, 95, 99 is finished work but only % greater than 0 is counted, anything at 0% is not averaged.

Column G is the tally at the end. a total of all rows is what I show for attainment.

WO Beginning and WO End = WOE/WOB = % of completion =

Hope this helps

4. Re: AverageIfs Complexity

Care to state clearly what must column C and column must meet?
I need the average for the entire column G excluding 0% of 90,95,99 (from column c) - maybe this is better

5. Re: AverageIfs Complexity

Originally Posted by mrwiley
I need the average for the entire column G excluding 0% of 90,95,99 (from column c) - maybe this is better
That is, column C must meet either 90 or 95 or 99; Column G must meet > 0%. Let's try:

=SUM(SUMIFS(G:G,G:G,">0",C:C,{90,95,99}))/SUM(COUNTIFS(G:G,">0",C:C,{90,95,99}))

6. Re: AverageIfs Complexity

That is, column C must meet either 90 or 95 or 99; Column G must meet > 0%. Let's try:

=SUM(SUMIFS(G:G,G:G,">0",C:C,{90,95,99}))/SUM(COUNTIFS(G:G,">0",C:C,{90,95,99}))
Thank you but this caused a circular reference.

7. Re: AverageIfs Complexity

Originally Posted by mrwiley
Thank you but this caused a circular reference.
Where did you enter the formula?

8. Re: AverageIfs Complexity

Good Morning, I entered in Column G

Where did you enter the formula?

9. Re: AverageIfs Complexity

Originally Posted by mrwiley
Good Morning, I entered in Column G
That's also the range whose values you want to average. The formula should not be entered in column G or C.

10. Re: AverageIfs Complexity

That's also the range whose values you want to average. The formula should not be entered in column G or C.
Today is report day, I inputted your formula, it provides a % but its not correct.

I need to calculate all percentages while excluding 0% of ONLY 90,95,99's.

Yous calculated 96% but mine was 76% - wish I could attached a spreadsheet example. Thank you for your feedback and formula.