averageifs

[Mr2017]

Hi

I need to use an averageifs formula to get the average UNIT price of Item SKUs greater than 30000 in the data below and ensure that Quantities of 0 are excluded.

I've tried to use the following formula

=AVERAGEIFS($B$5:$D$24,$B$5:$B$24,">30000",$C$5:$C$24,"<>0")

but get a value error. Can anyone please point out where I've gone wrong?

The data starts in cell A4 ie the CustomerID text in cell A4 and the UnitPrice text is in cell D4.

Thanks in advance.

CustomerID	ItemSKU	Quantity	UnitPrice
8725801	12345	100	10.5
8825268	23456	22	2.5
8048929	58910	93	17.5
8032245	11121	21	22.5
8147770	31415	12	17.5
8836323	16171	16	20
8286399	81920	40	8.75
8243640	21222	21	21.89
8200881	32425	1	2.0
8158122	26272	6	89.0
8115363	82930	4	50.0
8072604	31323	33	38.3
8029845	33435	0	50.8
7987086	36373	10	54.7
7944327	83940	0	58.7
7901567	41424	21	62.7
7858808	34445	26	66.6
7816049	46474	0	70.6
7773290	84950	37	74.5
7730531	51525	0	78.5

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

 

[jtakw]

Hi,

If you're trying to Average "UnitPrice", you have a typo/mistake in your formula for "Average Range".

Should be:

=AVERAGEIFS($D$5:$D$24,$B$5:$B$24,">30000",$C$5:$C$24,"<>0")

 

[steve the fish]

Its here:

=AVERAGEIFS($D$5:$D$24,$B$5:$B$24,">30000",$C$5:$C$24,"<>0")

 

[Mr2017]

Thank you jtakw

 

[Mr2017]

Thank you steve the fish!

 

[jtakw]

You're welcome, glad to help.