Averageifs return #DIV/0! based on multiple criteria from a single range

[ammykhan]

Hi, there
Trying to apply AVERAGEIFS to my Shipping_Mode column, the formula appears to be like =Averageifs(Total,Ship_Mode,"Regular Air",Ship_Mode,"Express Air") but always returns a #DIV/0!. Had a lot of deliberation on it but of no avail. Waiting for some hindsight from senior excel gurus, pls help me in this regards, thanks ?

 

[bebo021999]

try

=average(Averageif(Ship_Mode,"Regular Air",Total),Averageif(Ship_Mode,"Express Air",Total))

 

[ammykhan]

try

=average(Averageif(Ship_Mode,"Regular Air",Total),Averageif(Ship_Mode,"Express Air",Total))

Thanks for suggestion and prompt response, apparently the formula worked but when I reduced the size of data to see whether it is working or not, due to some unknown reason the result doesn't match with the expected value which makes me believe whether we are applying it correctly or not, can you review it to see whether any rectification is required to remove the unexpected error

 

[Peter_SSs]

Welcome to the MrExcel board!

when I reduced the size of data to see whether it is working or not, due to some unknown reason the result doesn't match with the expected value

Yes, averaging averages will almost certainly always give incorrect results.

Try this instead

=AVERAGE(FILTER(X4:X1042,(M4:M1042="Regular Air")+(M4:M1042="Express Air")))

BTW, for the future BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

 

[ammykhan]

Thanks Peter_SSs, it worked now, though i hear about this excel add_in but since I joined just now, I have to say sorry for the inconvenience that may be caused to you due to my naivety on the forum.

 

[Peter_SSs]

Thanks Peter_SSs, it worked now,

You're welcome. Glad to help. Thanks for letting us know.

though i hear about this excel add_in but since I joined just now, I have to say sorry for the inconvenience that may be caused to you due to my naivety on the forum.

Yes, we understand that you are new here and it takes a while to find out how things work. Still if you can make it as easy as possible for helpers, you tend to get faster help most times.
You can try out XL2BB in the Test Here forum so that if/when you have another question, you will be ready to go with it.