Forum Discussion
I need a cell to use a simple formula if date conditions are met
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.
Twifoo SergeiBaklan IngeborgHawighorst
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.
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.
- I advise you to refrain from using conditional formatting as a basis of whether a condition exists. I will scrutinize your data in a little while to determine my suggested course of action you should take.
Twifoo Why? That is what conditional formatting is made for. Use formatting if a condition is true. That's the point of conditional formatting.
- I personally believe that conditional formatting should be applied to reports, not on data, because such formatting can only accept a fixed range, never a dynamic range. Reports are limited to a certain range, and can therefore be conveniently applied with conditional formatting.
Conversely, data can extend to an indefinite range, thereby requiring a modification of the conditional formatting range, if and when such formatting is desired to be applied to the extended range.
With all due respect to you, I hereby stand ready to be corrected, if or when you can prove falsity hereof.
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)
- Replacement of equipment occurs when it reaches the end of its service life, not after one year from such end; otherwise, no such equipment would be available for use within that one year period.
Premised on the foregoing, the formula in K3, copied to the right, is:
=((YEAR($C3)+$I3)=K$2)*($D3*$E3)
