Formula to spread a cost evenly across multiple months
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!
Best 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?
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
Answers
-
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?
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
Thanks for the feedback, I was able to use your example to create a formula that works now!
Stay well
Help Article Resources
Categories
Check out the Formula Handbook template!