Apr 16 2019 01:43 PM
Apr 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.
Apr 16 2019 01:58 PM
Apr 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.
Apr 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.
Apr 16 2019 02:17 PM
Apr 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.
Apr 16 2019 10:19 PM
Apr 18 2019 08:19 AM
Apr 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.