Averaging with empty cells

[Monicalemaire]

I have a worksheet that calculates the number of days between when we received a customer return in versus the date we sent back to the customer. The first part of my question is why I am getting a large negative number as a result. (I think I need to filter out using an IF statement) My second question is I need to be able to include in my average the items not yet sent back otherwise I am misstating my average. Any help would be appreciated.

[Ochimus]

Monica,

If you can attach a sample from your worksheet, just enough data to show what it IS doing, and a note or "dummy set" of numbers showing what you WANT it to do, it would be a real help,

Ochimus

[Ochimus]

Sorry- something odd there, as my machine never showed your attachment at first. Apologies.

Ochimus

[Ochimus]

Monica,

The "negative" number is because you are taking date number in Col B from "Zero" (no number) in Col C.

Formula in Col D needs to exclude any rows without a date: Put this in D3 and copy down as many rows as you want.

=IF(C3="","",DAYS360(B3,C3))

And put this in D8 - it will ignore the blanks.

=AVERAGE(D3:D5)

Ochimus

[Monicalemaire]

I fixed the formula to make the date outs blank however that makes my average 4 days which doesn't account for the two that have not been sent back out. How do I include these into the equation?

[JohnTopley]

If you haven't a "Date Out" how can you average it?

[Ochimus]

Monica,

More apologies, as I misunderstood what you wanted to show in the "average"

Try the attached. Col D checks whether the item is still in stock (no date on Col C).
If there is no date, it gives the number of days between receiving the product (Col B) and today:
If there is a date, D3 stays blank.

=IF(C3<>"","",TODAY()-B3)

Col E then checks if you have returned the items (date in Col C).
If there is a date in Col C it calculates the number of days between receiving it and returning it.
If there is no date in Col C, E3 remains blank.

=IF(C3="","",DAYS360(B3,C3))

Copy those down as many rows as you want.

H2 then calculates the averaga as "total days you have held the current stock" (Col D) + "total days before you returned items" (Col E), divided by "number of items in Col B":

=ROUNDDOWN((SUM(D:D)+SUM(E:E))/COUNT(B:B),0)

Ochimus