Formula to spread a cost evenly across multiple months

Question

I have a sheet with columns PO #, PO Cost, Start Month, Finish Month, Duration (Months), and columns for months (e.g., Jan-2020 all the way through Dec-2024). I am looking for a formula that will divide PO Cost by Duration (Months) and automatically input the dollar amount across the month columns from Start Month to Finish Month.

For example, if a PO # has a PO Cost of $1000 and a Duration (Months) of 4 (say Jan-2022 to Apr-2022), I want my Jan-2022, Feb-2022, Mar-2022, and Apr-2022 columns to automatically input $250 in the PO # row.

I cannot provide screenshots of my sheet, as it contains confidential information, but I appreciate any feedback! Thank you!

Jason Albrecht · Accepted Answer

Hi Matt,

I wonder if using combined functions of IF, OR, AND might work?

e.g.

IF (the PO date matches the column date

OR (the column date is less than or equal to (the PO date plus duration)

AND (the column date is not less than the PO date

(PO Cost / Duration) ) ), 0.00 )

Hope this helps?