04-16-2019 01:43 PM
I am trying to plan for large expenses as equipment reaches it's lifespan. I want to take a dynamic 15 year span, and if a:
date of purchase c3 plus expected lifespan i3 is more than a given year k3 to z3, I want o3 (in the case of the end of service date for row 3) to populate with a purchase price d3*e3 quantity owned to tell us we need to plan to budget in 2023 for $31455.
I got conditional formatting to work for applying Red, yellow, green for due dates in the J column.
**I am aware of how that particular math will be flawed based on the spreadsheet given to me since it was $31455 for 3 of them on the sheet, but I am dealing with what was handed to me and I will fix that math later, so please ignore that part if you see it in the picture.
04-16-2019 01:58 PM
04-16-2019 02:02 PM
I understand you want a formula in cells K3 to Z3 that calculates a number if the year in row 2 is greater than the service date year plus lifespan. I don't see any number in column I in your screenshot, but I think the following formula should do what you describe. Put the formula into K3 and copy to the right.
=IF(YEAR($C3)+$I3+1=K$2,$D3*$E3,0)
04-16-2019 02:03 PM
@Twifoo Why? That is what conditional formatting is made for. Use formatting if a condition is true. That's the point of conditional formatting.
04-16-2019 02:17 PM
04-16-2019 03:14 PM
@Twifoo , IMHO, conditional formatting in many cases is clever enough to expand the range dynamically while you are adding the data. Apply it to entire column is also costs practically nothing.
04-16-2019 10:19 PM
04-18-2019 08:19 AM
@Twifoo @Sergei Baklan @Ingeborg Hawighorst
Thank you all for your input. After trial of both, I found the one that Twifoo provided to work. Ineborg, yours worked also, but inserted the budget into the following year.
You are all wonderful. Thank you for your assistance.
04-18-2019 02:14 PM
@InOverMyHead the formula I suggested puts the amount in the following year because of the +1 in the formula. Remove it if you want. It's how I understood the requirement.
Twifoo's formula is clever and shorter, but unless you really understand how it works, you may find it hard to maintain. An IF statement is a lot clearer than a multiplication with a comparison. I prefer to keep it simple and easier to understand.