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

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%

<colgroup><col span="6"><col></colgroup><tbody>
</tbody>

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]

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

<tbody>
</tbody>

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!

	a​	b​	c​	d​	e​	f​	g​
1​
2​	1435542​	99​	7/31/2018​	2024​	2024​	100.00%​
3​	1435543​	99​	8/1/2018​	2418​	2418​	100.00%​
4​	1435544​	99​	8/2/2018​	1720​	1732​	100.70%​
5​	1429008​	40​	8/3/2018​	1800​	1800​	100.00%​
6​	1435545​	40​	8/6/2018​	1030​	1030​	100.00%​
7​	1435546​	99​	8/7/2018​	1784​	2530​	68.18%​
8​	1435547​	40​	8/8/2018​	1500​	1594​	103.73%​
9​	1442957​	99​	8/8/2018​	125​	0​	0.00%​
10​	1435548​	40​	8/9/2018​	1784​	1749​	98.04%​
11​	1435549​	40​	8/10/2018​	1784​	2433​	73.62%​
12​	1435550​	40​	8/13/2018​	1505​	429​	28.50%​
13​	1435551​	30​	8/14/2018​	1784​	0​	0.00%​
14​	1435552​	30​	8/15/2018​	1505​	0​	0.00%​
15​	1435553​	90​	8/16/2018​	1784​	0​	0.00%​
16​	1435554​	95​	8/17/2018​	1784​	0​	0.00%​
17​	1435555​	30​	8/20/2018​	3510​	0​	0.00%​
18​
19​						67.14%​	f19: {=average(if((b2:b17=30) + (b2:b17=40)*(f2:f17>0) + (b2:b17>=90) * (f2:f17>0), f2:f17))}

<tbody>
</tbody>

 

[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.