Need help with building out a sheet based on days remaining in month
I am working to convert an excel file into smartsheets and trying to figure out where to start here (this is something that I inherited and the original owner of this file isn't around anymore so I'm kind of reverse engineering this). This is what the excel file looks like:
The formulas that I need to recreate are in the shaded cells, and reference the month columns they're in and the following month, with a formula like this:
(Cell X10) =MAX((Y$9-MAX(X$9,$J10))*$V10,0)
(Cell Y10) =MAX((Z$9-MAX(Y$9,$J10))*$V10,0)
So cell X10 references the cells for 'Jan 2021' and 'Feb 2021', while cell Y10 references 'Feb 2021' and 'Mar 2021'
In digging into the logic of the formula, I believe the intention is to find the number of days left in the month based on 'Scale Date' and apply the 'Daily' revenue to get the revenue per day for that month. I double checked the math for several of these cells and that does seem to be the case (Daily value is truncated so it can have e.g. $.0014 as the actual value)
So for cell X10 - because the scale date is on 1/2/2021 the value here would be the number of days left in Jan (inclusive of the scale date) so 30*Daily $. For cell Y10 - since this row already scaled in Jan, I am looking at the full number of days for Feb (28*Daily $) and the same for Mar (31*Daily $) and so on.
I don't think this formula can translate one to one based one how the file is structured (I don't think smartsheets would allow for this structure either) and I'm at a bit of a loss on where to start. Any ideas or help would be appreciated.
Help Article Resources
Check out the Formula Handbook template!