Budgeting: Spread an expense across months (at the month level)

Tmorrell

Trying to figure out how spread costs equally across a period of months that my users select when entered invoices. The month in my example cannot be a date field (Smartsheet doesn't have the capability to show dates as "mmm" and several other sheets rely on this one to be "mmm").

I've figured out these pieces:

1. Part 1: convert the months to numbers, e.g., Jan = 1, Feb = 2 and subtract: Feb - Jan or 2 - 1 then add +1 to get the correct number to populate Duration (months)---that's "2" for line 1 and "4" for line 2 in my example.
2. Part 2: Simply dividing the Total USD by the Duration gets me the $amount I spread across each month.
3. Part 3: Recognize the starting month and how many months duration to spread the $amount.

I feel like CONTAINS is in there but don't see how that will spread the $amount.

Any help would be super appreciated!

example.png

Paul Newcome

In the Jan column use

=IF(AND([Start Mnth#]@row <= 1, [End Mnth#]@row >= 1), [Total USD]@row / [Duration (months)]@row)

Then just change the 1 to a 2 for the Feb column, 3 for Mar, so on and so forth.