Problem with the AVERAGE(IF... function: it is giving me values when there are none!

Question

I have my data in a similar fashion as below:

A (Date (in number format)	B (Numbers to average)
41468.3787152778	300
41468.380162037	354
41468.3814930556	333
... (100 thousand rows)	...

I need the average of B between days 41469.5 and 41474.5, so I built this formula:

=AVERAGE(IF((A:A>41469.5)*(A:A<41474.5), B:B))

Then pressed Ctrl+Shift+Enter

The formula seemed to work fine for most of my data, until I realized it did not work when I requested the average of non existent values. For instance, there are some gaps in my data (a few days and hours are missing), but the formula still produces values, as if those days existed.

I tried doing many things to seek the "root" of the problem, but to no avail. In fact, I realized some other inconsistencies in doing so, therefore I'm in desperate need of help in order to understand if all my previous work was in vain, or if I need to do just some little adjustments.

One of the things I tried to do was counting the absent data with:

=COUNT(A:A>41469.5*A:A<41474.5)

This should have given me zero, but instead it gave me 1,048,576 (~ one million). It's an absurd, because I have only a bit less than 100,000 (100 thousand) rows. When I tried the above formulas with small data they worked fine. I use Microsoft Office 365 ProPlus. I hope this is not a software issue, because I really would not know another way of dealing with this data at the moment.

I would appreciate any suggestions,

Really big thanks in advance,

Rodrigo

Answer 1

Hi,

What result do you get with this formula?

=AVERAGEIFS($B$2:$B$100,$A$2:$A$100,">="&41469.5,$A$2:$A$100,"<="&41474.5)

Hope this helps.

Answer 2

Thanks very much for your quick response Ashish,

While your answer perfectly fixes my problem, it would be awesome if you could clarify one thing to me. Consider the following data:

A	B (data to average)
1.0000	1.0000
2.0000	2.0000
3.0000	3.0000
4.0000	4.0000
5.0000	5.0000

Now I will list different functions, with their corresponding results.

First, I'll use the function AVERAGEIFS:

=AVERAGEIFS(B:B, A:A, ">"&(4), A:A, "<"&(5)) Enter

#DIV/0!

=AVERAGEIFS(B:B, A:A, ">="&(3), A:A, "<="&(5)) Enter

4

Now, I'll try to repeat the same process using AVERAGE(IF( )):

=AVERAGE(IF(A:A>(4)*(A:A<(5)), B:B)) Ctrl+Shift+Enter

5

=AVERAGE(IF(A:A>=(3)*(A:A<=(5)), B:B)) Ctrl+Shift+Enter

4

So what might be happening with the AVERAGE(IF( )) function? Μany other functions do not have an "IF" counterpart, for example, there is no "MAXIF( )" or "STDEV.SIF( )" function, so it is suitable to use MAX(IF ( )) and STDEV.S(IF( )). This is why I need to understand what's wrong with it, because this probably will apply to many other functions.****Again, thanks so much and I'm looking forward to some more suggestions.

Rodrigo