AVERAGEIFS not giving expected value

[Pumperkin]

Hi,

I am trying to do an AVERAGEIFS formula but the provided answer is not correct. Current formula I have is

=AVERAGEIFS(D:D, B:B, ">="&A2, C:C, "<="&A2)

I have a long list of sequential dates in Column A, column B is the date a device was sent (or today if not sent), column C the date it was received, and column D is the difference between the two (so I can see how long it has been in stock).

I want to run a formula by date to see whether stock is on average getting older or younger.

My logic is, I want to average the differences, provided the device was received on or before X date, and sent on or after X date - this would give the average age of in stock items on that date (once a device has been sent, it is immediately not in stock).

The figure I am getting is about 7 days higher than it should be (I have taken today's date as an example, as everything currently in stock is easy enough to calculate).

Does anyone have any idea why this is happening and/or if there's a better way to do it?

 

[RoryA]

The answer to that lies in your data. The formula itself is set up correctly.

 

[Pumperkin]

Fixed it The problem was twofold. Firstly I hadn't copied down so not all devices were pulled across.

Secondly, and this is the big difference, I totally forgot network days... kinda explains why everything was older than it should be (weekends don't count in the first sheet, but completely forgot that this was needed on the second sheet).

3 days later and I'm sorted