Log In
Register
Home
Dashboards
Power Query
Power Pivot
Pivot Tables Quick Start
August 11, 2023
Offline
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!
Trusted Members
Moderators
November 1, 2018
Online
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
Dashboards
Power Query
Power Pivot
Pivot Tables Quick Start
August 11, 2023
Offline
That works perfectly!
I am using Excel 365.
Moderators
January 31, 2022
Online
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.
Trusted Members
Moderators
November 1, 2018
Online
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))
Dashboards
Power Query
Power Pivot
Pivot Tables Quick Start
August 11, 2023
Offline
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!
1 Guest(s)
