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.

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

 

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