Problem with date range lookups and calculations

I have a date calculation problem, which I hope someone can help me with.

I have a spreadsheet as follows: (this is a simplified example)

……...…A……...………….B………………..C
01…..….date…………..salary….….rolling average
02….30-01-08………..£2000………….£24,000
03….28-02-08………..£2000………….£24,000
04….31-03-08………..£2300………….£25,200
05….29-04-08………..£2300………….£25,800
06….30-06-08………..£2400………………?
etc
etc
etc

I am trying to calculate my rolling average yearly potential salary (ie “how much would I have earned in the last twelve months if I was paid at my current rate for all those preceding months”?), based on amounts entered throughout the year, usually monthly. However, there can be more, or less, than 12 salary payments in a year, as sometimes I am paid weekly and sometimes not at all for a month. (I work as a musician on cruise ships, and I work short contracts ie 3 months on, 1 month off, three months on, 1 month off, etc. All salaries are paid at month end and amounts can be different from month to month. Prior to this employment, I was paid weekly.)

In the above simplified example, the calculation for C2 is easy, as I just take the monthly payment B2, multiply by 12 and there it is.

Also easy is row 04 – I add B2+B3+B4, divide by 3 to get a monthly average and then multiply by 12.

By row 05, we can see that my rolling average is increasing as my salary has increased, which is the figure I want to calculate. However, row 06 shows there was no payment in May, and I negotiated a new contract rate in June, so the calculation I want is much harder. I have to now work out a DAILY RATE, by looking at all earnings in the last 365 days, adding them, dividing by the number of days (there will be less than 365 for at least the first 12 entries) and then multiplying by 365 for the answer. In my example, this would be as follows:

£11,000 (total earned) / 153 (days between 30-01-08 and 30-06-08) = £71.89
£71.89 x 365 = £26,241.83

showing that my rolling average increased, which is good news!

I therefore need a formula that will look at all dates in column A, select those that are within a year of the most recent entry, calculate a daily rate using the amounts in column B and put the result in column C. I have found the function EDATE which I guess is the one to use (as the operator ‘-12’ will return the date exactly a year earlier), but I’m unclear how to use this for what I want. I think I need to create an array, but how do I specify parameters for the highest and lowest dates in that array? Or perhaps this might have to be done using pivot tables/calculated fields/calculated items/etc, but that is a very alien area to me and I’m totally unsure where to start if such is the case.

If anyone can offer any assistance or suggestions, then I would be most grateful.

Many Thanks

Hello Miles,

You can use array formulas in Excel to evaluate a whole range where you would normally only evaluate a single cell, so in the formula I suggested, i.e.

=SUM(IF(A$2:A3>MIN(IF(A$2:A3>=EDATE(A3,-12),A$2:A3)),B$2:B3))*365/(A3-MIN(IF(A$2:A3>=EDATE(A3,-12),A$2:A3)))

Then this part repeats twice to calculate a constant

=MIN(IF(A$2:A3>=EDATE(A3,-12),A$2:A3))

The IF formula looks at the dates in the range A2:A3 and if any are greater than the date 12 months before A3 then it returns those dates (or FALSE if the date is more than 12 months old) so the IF returns an array of either dates or FALSE values, MIN then takes the lowest of those (FALSE is ignored). In this way that part gives you the earliest date within the period you want to evaluate.

The rest of the formula sums all the amounts paid on dates later than that date (not the same) and divides by the number of days between that date and the date in A3.

If you look at the formula simplified to show the above as Mindate

=SUM(IF(A$2:A3>Mindate,B$2:B3))*365/(A3-Mindate)

You can probably see that the first part is a similar array formula to that discussed above. The dates are evaluated to see if they are greater than Mindate, if they are then the amount in column B is added to the sum, that gives your total amount and then you multiply by 365 and divide by the number of days in the period.

Note that "array formulas" need to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.

If you want the calculation to work exactly the way you suggested then you can make two very small (but significant) changes like this

=SUM(IF(A$2:A3>=MIN(IF(A$2:A3>=EDATE(A3,-12),A$2:A3)),B$2:B3))*365/(A3-MIN(IF(A$2:A3>=EDATE(A3,-12),A$2:A3))+1)

That will give exactly the amount you suggested for the C6 calculation.

If you look at the attached I used my original formula in column C and the revised one in column D. Note the large differences in the early dates, that's because of the "one month too many" discrepancy that I pointed out.

In D3 for instance the formula is taking the sum of two months, B2 and B3 but dividing by the days for one month, i.e. the difference in days between A2 and A3. Obviously the discrepancy lessens when the period is greater but even for a year you will be effectively taking 13 months earnings and dividing by 12 months' days I think.