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]

[TABLE="width: 428"]
<tbody>[TR]
[TD]1435542[/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]7/31/2018[/TD]
[TD][/TD]
[TD]2024[/TD]
[TD]2024[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]1435543[/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]8/1/2018[/TD]
[TD][/TD]
[TD]2418[/TD]
[TD]2418[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]1435544[/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]8/2/2018[/TD]
[TD][/TD]
[TD]1720[/TD]
[TD]1732[/TD]
[TD]100.70%[/TD]
[/TR]
[TR]
[TD]1429008[/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]8/3/2018[/TD]
[TD][/TD]
[TD]1800[/TD]
[TD]1800[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]1435545[/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]8/6/2018[/TD]
[TD][/TD]
[TD]1030[/TD]
[TD]1030[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]1435546[/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]8/7/2018[/TD]
[TD][/TD]
[TD]1784[/TD]
[TD]2530[/TD]
[TD]68.18%[/TD]
[/TR]
[TR]
[TD]1435547[/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]8/8/2018[/TD]
[TD][/TD]
[TD]1500[/TD]
[TD]1594[/TD]
[TD]103.73%[/TD]
[/TR]
[TR]
[TD]1442957[/TD]
[TD][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]8/8/2018[/TD]
[TD][/TD]
[TD]125[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1435548[/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]8/9/2018[/TD]
[TD][/TD]
[TD]1784[/TD]
[TD]1749[/TD]
[TD]98.04%[/TD]
[/TR]
[TR]
[TD]1435549[/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]8/10/2018[/TD]
[TD][/TD]
[TD]1784[/TD]
[TD]2433[/TD]
[TD]73.62%[/TD]
[/TR]
[TR]
[TD]1435550[/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]8/13/2018[/TD]
[TD][/TD]
[TD]1505[/TD]
[TD]429[/TD]
[TD]28.50%[/TD]
[/TR]
[TR]
[TD]1435551[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]8/14/2018[/TD]
[TD][/TD]
[TD]1784[/TD]
[TD]0[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]1435552[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]8/15/2018[/TD]
[TD][/TD]
[TD]1505[/TD]
[TD]0[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]1435553[/TD]
[TD][/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]8/16/2018[/TD]
[TD][/TD]
[TD]1784[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1435554[/TD]
[TD][/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]8/17/2018[/TD]
[TD][/TD]
[TD]1784[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1435555[/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]8/20/2018[/TD]
[TD][/TD]
[TD]3510[/TD]
[TD]0[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]67.14%[/TD]
[/TR]
</tbody>[/TABLE]

Control+shift+enter, not just enter:

=AVERAGE(IF(C2:C17<>IF(ISNUMBER(MATCH(C2:C17,{90,95,99},0)),IF(G2:G17=0,C2:C17)),G2:G17))

 

[mrwiley]

It worked, had to change formula to calculate % below 0% - thanks so much!

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #c0c0c0"][/TD]
[TD="bgcolor: #c0c0c0"]

a​

[/TD]
[TD="bgcolor: #c0c0c0"]

b​

[/TD]
[TD="bgcolor: #c0c0c0"]

c​

[/TD]
[TD="bgcolor: #c0c0c0"]

d​

[/TD]
[TD="bgcolor: #c0c0c0"]

e​

[/TD]
[TD="bgcolor: #c0c0c0"]

f​

[/TD]
[TD="bgcolor: #c0c0c0"]

g​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

1​

[/TD]
[TD="bgcolor: #f3f3f3"][/TD]
[TD="bgcolor: #f3f3f3"][/TD]
[TD="bgcolor: #f3f3f3"][/TD]
[TD="bgcolor: #f3f3f3"][/TD]
[TD="bgcolor: #f3f3f3"][/TD]
[TD="bgcolor: #f3f3f3"][/TD]
[TD="bgcolor: #f3f3f3"][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

2​

[/TD]
[TD]

1435542​

[/TD]
[TD]

99​

[/TD]
[TD]

7/31/2018​

[/TD]
[TD]

2024​

[/TD]
[TD]

2024​

[/TD]
[TD="bgcolor: #ccffcc"]

100.00%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

3​

[/TD]
[TD]

1435543​

[/TD]
[TD]

99​

[/TD]
[TD]

8/1/2018​

[/TD]
[TD]

2418​

[/TD]
[TD]

2418​

[/TD]
[TD="bgcolor: #ccffcc"]

100.00%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

4​

[/TD]
[TD]

1435544​

[/TD]
[TD]

99​

[/TD]
[TD]

8/2/2018​

[/TD]
[TD]

1720​

[/TD]
[TD]

1732​

[/TD]
[TD="bgcolor: #ccffcc"]

100.70%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

5​

[/TD]
[TD]

1429008​

[/TD]
[TD]

40​

[/TD]
[TD]

8/3/2018​

[/TD]
[TD]

1800​

[/TD]
[TD]

1800​

[/TD]
[TD="bgcolor: #ccffcc"]

100.00%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

6​

[/TD]
[TD]

1435545​

[/TD]
[TD]

40​

[/TD]
[TD]

8/6/2018​

[/TD]
[TD]

1030​

[/TD]
[TD]

1030​

[/TD]
[TD="bgcolor: #ccffcc"]

100.00%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

7​

[/TD]
[TD]

1435546​

[/TD]
[TD]

99​

[/TD]
[TD]

8/7/2018​

[/TD]
[TD]

1784​

[/TD]
[TD]

2530​

[/TD]
[TD="bgcolor: #ccffcc"]

68.18%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

8​

[/TD]
[TD]

1435547​

[/TD]
[TD]

40​

[/TD]
[TD]

8/8/2018​

[/TD]
[TD]

1500​

[/TD]
[TD]

1594​

[/TD]
[TD="bgcolor: #ccffcc"]

103.73%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

9​

[/TD]
[TD]

1442957​

[/TD]
[TD]

99​

[/TD]
[TD]

8/8/2018​

[/TD]
[TD]

125​

[/TD]
[TD]

0​

[/TD]
[TD]

0.00%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

10​

[/TD]
[TD]

1435548​

[/TD]
[TD]

40​

[/TD]
[TD]

8/9/2018​

[/TD]
[TD]

1784​

[/TD]
[TD]

1749​

[/TD]
[TD="bgcolor: #ccffcc"]

98.04%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

11​

[/TD]
[TD]

1435549​

[/TD]
[TD]

40​

[/TD]
[TD]

8/10/2018​

[/TD]
[TD]

1784​

[/TD]
[TD]

2433​

[/TD]
[TD="bgcolor: #ccffcc"]

73.62%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

12​

[/TD]
[TD]

1435550​

[/TD]
[TD]

40​

[/TD]
[TD]

8/13/2018​

[/TD]
[TD]

1505​

[/TD]
[TD]

429​

[/TD]
[TD="bgcolor: #ccffcc"]

28.50%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

13​

[/TD]
[TD]

1435551​

[/TD]
[TD]

30​

[/TD]
[TD]

8/14/2018​

[/TD]
[TD]

1784​

[/TD]
[TD]

0​

[/TD]
[TD="bgcolor: #ccffcc"]

0.00%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

14​

[/TD]
[TD]

1435552​

[/TD]
[TD]

30​

[/TD]
[TD]

8/15/2018​

[/TD]
[TD]

1505​

[/TD]
[TD]

0​

[/TD]
[TD="bgcolor: #ccffcc"]

0.00%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

15​

[/TD]
[TD]

1435553​

[/TD]
[TD]

90​

[/TD]
[TD]

8/16/2018​

[/TD]
[TD]

1784​

[/TD]
[TD]

0​

[/TD]
[TD]

0.00%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

16​

[/TD]
[TD]

1435554​

[/TD]
[TD]

95​

[/TD]
[TD]

8/17/2018​

[/TD]
[TD]

1784​

[/TD]
[TD]

0​

[/TD]
[TD]

0.00%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

17​

[/TD]
[TD]

1435555​

[/TD]
[TD]

30​

[/TD]
[TD]

8/20/2018​

[/TD]
[TD]

3510​

[/TD]
[TD]

0​

[/TD]
[TD="bgcolor: #ccffcc"]

0.00%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

18​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #c0c0c0"]

19​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #ccffcc"]

67.14%​

[/TD]
[TD="bgcolor: #ccffcc"]f19: {=average(if((b2:b17=30) + (b2:b17=40)*(f2:f17>0) + (b2:b17>=90) * (f2:f17>0), f2:f17))}[/TD]
[/TR]
</tbody>[/TABLE]

 

[mrwiley]

Control+shift+enter, not just enter:

=AVERAGE(IF(C2:C17<>IF(ISNUMBER(MATCH(C2:C17,{90,95,99},0)),IF(G2:G17=0,C2:C17)),G2:G17))

Thanks - this also worked once i adjusted to include <>1

So grateful for you both.

 

[mrwiley]

Hello again,

I wonder, what about calculating the average but based on the first digit of the part number - I have 3 (3, 4, 8)

Is this possible to search column A looking at ALL the parts with 3's and calculate the average in column G? Would this be an index Match?

Thanks for any expertise you have

 

[mrwiley]

I have gotten my formula to work

=IF(VALUE($B$2:$B$150)>1,1,AVERAGE(IF(LEFT(A2:A150,2)="3-",1,$G1:$G150)))

- however I do have blanks within the average column I need to be included in the formula.

I did try "sum" instead of average / countif(G2:G150,"<>0" but it resulted in 1 from the "if(value)" in the beginning.

Another condition is I face multiple WO #'s in column B but part numbers in Column A.

I need all WO #'s associated with the part number. For example I could have Part #A with 7 WO's then the next part would start. I do not have a part number next to each WO #.

Thanks again if you're able to assist.

Hello again,

I wonder, what about calculating the average but based on the first digit of the part number - I have 3 (3, 4, 8)

Is this possible to search column A looking at ALL the parts with 3's and calculate the average in column G? Would this be an index Match?

Thanks for any expertise you have

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=AVERAGE(IF(LEFT($B$2:$B$150,1)+0=3,$G$2:$G$150))
​

 

[mrwiley]

Thank you for taking the time to share your formula, I do get "#VALUE when I try this...

Control+shift+enter, not just enter:

=AVERAGE(IF(LEFT($B$2:$B$150,1)+0=3,$G$2:$G$150))
​

 

[Aladin Akyurek]

Thank you for taking the time to share your formula, I do get "#VALUE when I try this...

You need to apply control+shift+enter at the same time.