AVERAGEIF function

[willow1985]

Hello,

It is my first time using the AverageIf function and I seem to be having trouble getting it to work.

I want the formula to calculate the average of all numbers in sheet "warranty report" column O that have a date of 2019 in column L. The sheet that the formula is on has the date of 2019 in cell B1.

=AVERAGEIF('Warranty Report'!O:O,B1,'Warranty Report'!L:L)

However this formula is giving me a #DIV/0! error.

Any help would be appreciated.

Thank you

Carla

 

[joeu2004]

Re: Help with AVERAGEIF function

First, generally, it is better to use a reasonably limited range (e.g. $O$1:$O$10000) instead of a whole-column range (O:O).

Second, the #DIV/0 errors means that there is no row that meets the criteria.

In this case, it is because you have the parameters reversed. You can write:

=AVERAGEIF('Warranty Report'!$L$1:$L$10000, B1, 'Warranty Report'!$O$1:$O$10000)
or
=AVERAGEIFS('Warranty Report'!$O$1:$O$10000, 'Warranty Report'!$L$1:$L$10000, B1)

I assume that column L contains just year numbers, not Excel dates that might be formatted to display just the year number.

 

[willow1985]

Re: Help with AVERAGEIF function

I tried a limited range at first but then saw an example online with whole-column ranges so tried that. I see though that I had the averageif and averageifs mixed up. It is working now with the averageif formula you provided.

Thank you very much!

Carla

 

[joeu2004]

Re: Help with AVERAGEIF function

I tried a limited range at first but then saw an example online with whole-column ranges so tried that

Yes, I see even so-called Excel "experts" that use them. It is lazy. More importantly, it is the most common cause of the complaint that Excel is "non-responding". Really, Excel is taking an inordinate amount of time to recalculate, which it might do with each edit.

Just because people post it, that doesn't make it better, much less right. It might not be an issue for AVERAGEIF; Excel might be smart enough to determine the last row of data first. But it is a bad habit, especially when it is applied (all too often) to SUMPRODUCT and array-entered IF expressions (e.g. STDEV(IF(....)) ).

A word to the wise....