Averageifs reciprocal

[schtebben]

I am trying to use averageifs to find the average of the reciprocal without success. I can easily do this by creating extra columns with the reciprocal and taking the averageifs of those columns but I want to do it without any extra columns. I am using =AVERAGEIFS(GS_Data,GS_Data[Diff],"<"&L10,GS_Data[Diff],">="&K10) on the reciprocal column B without problems but I'd like something like =AVERAGEIFS(1/GS_Data[A],GS_Data[Diff],"<"&L10,GS_Data[Diff],">="&K10). Any idea how to do this?

A	B
2.35	0.43
1.77	0.56
1.93	0.52
1.45	0.69
1.36	0.74
3.28	0.30
2.00	0.50
4.69	0.21
1.72	0.58
1.49	0.67
5.75	0.17
1.93	0.52
2.22	0.45
2.17	0.46
1.23	0.81
1.65	0.61
2.13	0.47
1.78	0.56
4.29	0.23

 

[bebo021999]

What is GS_Data, GS_Data[Diff], L10, K10 those are not displayed in screenshot.

 

[schtebben]

GS_Data is the table, GS_Diff is a column with a rating value and L10, K10 are upper and lower bounds for the rating values. I've created a new sample ... I'm trying to get the average of the reciprocal between particular values. The formula in this sample is: =AVERAGEIFS(GS_Data[F %],GS_Data[GS Diff],"<"&F2,GS_Data[GS Diff],">="&E2) which works fine on column F% and gives the answer 0.638 but I need it to work on column F H. If there was a Harmean IFS that would work but there isn't so I thought of averageifs 1/FH but that's not a valid range ...


Thanks.

F H	F %	GS Diff		Lower	Upper
2.35	0.43	252.20		100.00	150.00
1.77	0.56	187.43
1.93	0.52	168.34		0.638
1.45	0.69	178.69
1.36	0.74	100.99
3.28	0.30	181.57
2.00	0.50	193.14
4.69	0.21	85.00
1.72	0.58	193.75
1.49	0.67	141.04
5.75	0.17	167.17
1.93	0.52	102.63
2.22	0.45	118.48
2.17	0.46	83.61
1.23	0.81	116.33
1.65	0.61	15.39
2.13	0.47	295.95
1.78	0.56	193.01
4.29	0.23	62.53

 

[bebo021999]

Change the average column from F% to F H:
=AVERAGEIFS(GS_Data[F H],GS_Data[GS Diff],"<"&F2,GS_Data[GS Diff],">="&E2)
Does it work?

 

[schtebben]

That just gives the average of F H which is not what I want. I want the average of 1/F H i.e. the reciprocal of F H which is what I've shown in F %. Put another way I want the harmonic mean of F H between the upper and lower ranges. Thanks.

 

[bebo021999]

OK, Got it. AVERAGE(IF,IFS) does not support array from formula (1/range). Try sum/count instead:

VBA sample.xlsx
	A	B	C	D	E	F
1	F H		GS DIFF		Lower	Upper
2	2.35		120		100	150
3	1.77		187.43
4	1.93		168.34		0.557594
5	1.45		110
Sheet2

Cell Formulas
Range	Formula
E4	E4	=SUMPRODUCT((C2:C5>=E2)*(C2:C5<=F2)/A2:A5)/SUMPRODUCT((C2:C5>=E2)*(C2:C5<=F2))

 

[schtebben]

Whoohoo! Thank you - that's exactly what I wanted. I've been struggling with this for some time now. Thanks again.