Summing Monthly Values based on different rates between Dates

[DHHM]

Greetings all,

I have a table specifying rates which are effective between two dates (inclusive) as below.

'Rate Table'
RateTable.PNG

I list each day getting the yearly rate (Column B) the date falls in based on the Rate Table, then divide that by 365 (current calendar year days) to give me my daily rate (Column C).

'Daily Table'
DailyRate.PNG

Column B

Please Login or Register  to view this content.

Column C

Please Login or Register  to view this content.

Then to work out the value over a month basis, I do a simple Sumproduct from the Daily Table each month end as the whole month.

'Monthly Table'
MonthlyTable.PNG

Column F

Please Login or Register  to view this content.

Is there a formula that is able to what Column F calculates without the need for the 'Daily Table'?

[jason.b75]

See if this one gives the correct results.

Please Login or Register  to view this content.

It works with your sample file, but I haven't tested it beyond that.

[DHHM]

Perfect, exactly what I'm after, even when changing the date ranges.

How is Lookup determining the amount of days x rate?

[jason.b75]

If you look at this formula which takes the dates for March 2019 from your list of dates in column A, it will be easier to see how the lookup is working.

Please Login or Register  to view this content.

then compare that to the full formula,

Please Login or Register  to view this content.

where the section in red generates the list of dates in A2:A32 as an array by calculating back from the date in E2 to the start of the month.

Lookup pulls the annual rate for each individual day. Sumproduct does the division, then adds it all together.

Try using the formula evaluation tool to follow the steps (Formulas tab > Evaluate Formula). It might be easier with a smaller date range, if you set the end date in E2 to earlier in the month it should still work.

[DHHM]

Thanks Jason, I originally tried to evaluate with F9, the rang was too much. Using Evaluate explains it fine.