Average days over range of dates?

Try this.

Ssumes your dates are in B2, B3 & B4

=DAYS360(B2,B4)/COUNTA(B2:B4)

Hi

Originally Posted by timbo1957

Try this.

Ssumes your dates are in B2, B3 & B4

=DAYS360(B2,B4)/COUNTA(B2:B4)

Converted it to =DAYS360(C3,J3)/COUNTA(C3:J3) as the dates run horizontally rather than vertically. However for just two dates (23rd Feb '09 and 10th of Dec '10) it gives a number of minus 19646.5 clearly this isn't right, what am I doing wrong? (the dates are in C3 and D3 for this instance).

-John

Hi John,

You aren't comparing apples with apples -

Converted it to =DAYS360(C3,J3)/COUNTA(C3:J3)

the dates are in C3 and D3

Your range for your sum DAYS360(C3,J3)/COUNTA(C3:J3) is greater than the range of data which you say is in C3:D3.

What this does is include a blank cell and the DAYS360 function can't find date in E,F,G,H,I,or J.

If you need to include blank cells you could get around it by using CHOOSE

The CHOOSE function in this case would use up the number generated by the COUNTA function to work out which sum to use the same for the second part of the sum.

=CHOOSE(COUNTA(C3:J3),DAYS360(C3,C3),DAYS360(C3,D3),DAYS360(C3,E3),DAYS360(C3,F3),DAYS360(C3,G3),DAYS360(C3,H3),DAYS360(C3,I3),DAYS360(C3,J3))/CHOOSE(COUNTA(C3:J3),COUNTA(C3),COUNTA(C3:D3),COUNTA(C3:E3),COUNTA(C3:F3),COUNTA(C3:G3),COUNTA(C3:H3),COUNTA(C3:I3),COUNTA(C3:J3))

Originally Posted by timbo1957

Hi John,

You aren't comparing apples with apples -





Your range for your sum DAYS360(C3,J3)/COUNTA(C3:J3) is greater than the range of data which you say is in C3:D3.

What this does is include a blank cell and the DAYS360 function can't find date in E,F,G,H,I,or J.

If you need to include blank cells you could get around it by using CHOOSE

The CHOOSE function in this case would use up the number generated by the COUNTA function to work out which sum to use the same for the second part of the sum.

=CHOOSE(COUNTA(C3:J3),DAYS360(C3,C3),DAYS360(C3,D3),DAYS360(C3,E3),DAYS360(C3,F3),DAYS360(C3,G3),DAYS360(C3,H3),DAYS360(C3,I3),DAYS360(C3,J3))/CHOOSE(COUNTA(C3:J3),COUNTA(C3),COUNTA(C3:D3),COUNTA(C3:E3),COUNTA(C3:F3),COUNTA(C3:G3),COUNTA(C3:H3),COUNTA(C3:I3),COUNTA(C3:J3))

The idea was to just add dates when the ink was changed and let the spreadsheet work out the average and update itself. This seems a very un-elegant solution, but at least it seems to work, far more so than any of my attempts LOL the only problem now is I have two averages ending in point five (oddly enough one of which is from the two dates I listed earlier). Any idea what is happening there?

Thank you so much for your help so far too.

-John.

Hi John,

The difference in days between 23/07/10 and 10/12/10 is 137.

You have made two purchases 137/ 2 = 68.5.

Understood.

Hi

All sorted, thanks for the help guy, much appreciated.

-John