AverageIfs Complexity

[mrwiley]

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") <<this works but omits any 0%

A B
[TABLE="width: 527"]
<colgroup><col span="6"><col></colgroup><tbody>[TR]
[TD]WLKITL[/TD]
[TD]WLDOCO[/TD]
[TD] WASRST[/TD]
[TD]WADRQJ[/TD]
[TD]WLUORG[/TD]
[TD="colspan: 2"] WLSOQS PERCENTAGE[/TD]
[/TR]
[TR]
[TD]Part [/TD]
[TD="align: right"]1434671[/TD]
[TD] 99[/TD]
[TD]07/31/18[/TD]
[TD="align: right"]630[/TD]
[TD="align: right"]630[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1434672[/TD]
[TD] 99[/TD]
[TD]08/01/18[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1434673[/TD]
[TD] 90[/TD]
[TD]08/02/18[/TD]
[TD="align: right"]630[/TD]
[TD="align: right"]630[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Aladin Akyurek]

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

 

[mrwiley]

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

 

[mrwiley]

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

 

[Aladin Akyurek]

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}))

 

[mrwiley]

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.

 

[Aladin Akyurek]

Thank you but this caused a circular reference.

Where did you enter the formula?

 

[mrwiley]

Good Morning, I entered in Column G

Where did you enter the formula?

 

[Aladin Akyurek]

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.

 

[mrwiley]

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.