Looking for some MS Excel help.
I am able to create a nested “IF” formula to search a prior cell for a specific condition but I want a formula or a Conditional Formatting option that searches for a specific condition until it finds that condition in any previous cell when the prior cell does not generate the correct condition. Gas in column B is what I want the formula to find.
Here is a page from the Excel chart I created for my vehicles service records.
My problem is when I get two cells in a row that generate a Service condition the MPG results are incorrect because the “IF” condition I created only jumps back one cell for the Mileage. What I need is a formula that jumps back to the first prior Gas condition.
I am aware that I could combine the Information in Cells 294 and 295 which would make my formula function correctly but I am trying to keep the information Date correct.
My current IF formula for Row 295.
The problem with this formula is caused in row 296 of this chart because there are two cells in a row that create the “Service” Condition.
I do not want to create a separate formula for each condition, or a nested IF condition that still will not solve for every situation, so I am hoping there is a Conditional formatting option that will solve my problem.