I have two conundrums that I'm struggling to find a sensible solution to.
1. (Daily Pricing)
I have a list of contracts with start and end dates, and I want to multiply the daily price based on the number of days within each fiscal period.
2. (Monthly Pricing)
This one is a little more complex, but I blame our accountant. For this one the Daily Price is calculated using the monthly price, divided by the number of days in the month. i.e. Price of £1,000 a month in January has a daily rate of £32.26, but in February it would be £35.71. As the fiscal periods usually spread over two months, the total for the period would be the daily rate for month 'A' multiplied by the number of days from that fall into that fiscal period, plus, the daily rate for month 'B' multiplied by the number of days from that fall into that fiscal period.
Any help is massively appreciated!
Which version of Excel do you have? It will affect which functions can be used.
For the first one, I think this formula in G5 and copied across and down will work:
=MAX(0,MIN(G$3,$C5)-MAX($B5,G$2)+1)*$D5
That works perfectly!
I am using Excel 365.
To build further on Velouria's solution for the daily pricing, I've added some helper formulas to your file (see attached) to facilitate the monthly pricing. I'm sure that you can condense it all into one formula with the LET function, but I haven't gone that far yet. Let's first see if this is really what you expect.
OK, there are almost certainly simpler formulas, but I think this works in G15 and copied across and down:
=LET(firststart,MAX($B15,G$12),firstend,MIN($C15,EOMONTH(G$12,0),G$13),numdays1,DAY(EOMONTH(G$12,0)),secondstart,firstend+1,secondend,MIN($C15,G$13),numdays2,DAY(EOMONTH(G$13,0)),result,MAX(0,firstend-firststart+1)*$D15/numdays1+MAX(0,secondend-secondstart+1)*$D15/numdays2,ROUND(result,2))
Riny / Velouria - Both of those options give the expected result. Velouria's option future proof's the formula, which I guess is effectively what you were saying about getting it all into one LET function.
Really grateful for your help!
