MS Excel formula question

Hello Mudpikers,

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. :sunglasses:

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.


I could probably solve this fairly quickly if I was at home

I think invoking isnumber in the if statement would help but do this on my phone is not optimal.

Can you email me the file? Or link me via Google? I can see what going on in sheets.

I’m not an expert but I mess with excel on an intermediate+ basis daily.

Here is the file @Rhinosaurus . Thanks for the help. :sunglasses: (6.4 KB)


Will have access to a computer this evening so I’ll take a look then.

Rather than do the next row formula thing in your data table, couldn’t you have a raw data table/tab and then use a separate tab with a new table and lookup formula in the if statement to pull out the values from the master table? #thatmakesmoresensetomewheniwroteit

If I get time I’ll have a look tomorrow, with @Rhinosaurus looking at it as well, hopefully you’ll get a quick solution.

So I’m clear on how you want to do this, you want to take the amount of gas added in column D, and using the mileage difference calucated from column C figure out the MPG in column E.

So for say E304, it’s (C304-C303)/D304 Do I have that right?

I’m not sure if I’m just too tired or what but I am not getting this to work. Interested to see what others come up with.

I am open to any ideas @keets . I am a novice when it comes to using Excel and entirely self taught so my choices are likely to be some of the least efficient.

That is the correct formula for those cells @jenrick.
If all I was doing with the chart was calculating the gas mileage the formula would be a simple one like you show but once I throw the service records into the chart I create my problem. For row 296 I need a formula that jumps to row 293 since that is the prior Gas entry.

E296, it’s (C296-C293)/D296

Have a few hours into this math teaser myself so I understand. :sunglasses:

Thanks for the help everyone.


Try this… @wheelsup_cavu (10.2 KB)

What I’ve done is create an extra column, mileage at last fill up, which returns the last mileage when Gas was in column B.

Try this

So in short we have a couple things we need to do:

1)Figure out if we we need to even run the MPG calculation, your first IF statement does this just fine.

2)Next if we do need to run the MPG calculation we need to find the previous mileage to use. To do this we need to find the most recent valid gallons of gas entry. LOOKUP will sort through a column/row/array looking for a value and then return it when it finds it. What we want is when it doesn’t find it. It will respond with the last valid entry. So in this case we tell it to look for an gas value of 1000, which it obviously won’t find. So it will go to the end of the list of entries and take the last value. All we need to do is set it so it only searches the list of all gas values BEFORE the current record. So if we are on record 19, we only want it to check records 1-18, and so on. It doesn’t count blanks so it simply skips them back up to the last row with a valid numerical entry, thus handling the multiple service entries.

So far we have (assuming we are on record 19) =LOOKUP(1000,D$1:D18) This will return the last gas value in column B for row 1-18, which should give us our most recent fill up record.

Now returning the gas value itself doesn’t do much for us, but we can tell the formula to instead return the value in another part of the worksheet that has the same row/column as the set we’re searching. We need the mileage in column C, so our formula now reads =LOOKUP(1000,D$1:D18,C$1:C18). This will return the mileage value in the corresponding cell in column C.

Now you may note the $ before the cell number, this is to force excel to not try and get smart about updating that reference when copying and pasting, or filling. If I fill down 10 cells to record 29, excel will update the formula, but wont change the items that have the $ symbol. So the formula in record 29 is =LOOKUP(1000,D$1:D29,C$1:C29) automatically without having to go in an update the formula.

C) The last step is to simply do the math to determine MPG.

So the whole formula looks like this:


It works in sheets, so let me know if it doesn’t run in excel. To extend the records down, either copy and past it into the next record, or just fill down as needed.

I wonder what I had wrong here because that’s essentially what I was doing (not using 1000 but same formula) and I kept getting an error.

Depending on how you had the lookup referenced, column or array it can get picky.

The file I posted up there has the solution working in a similar way @jenrick.

Thanks for the ideas. :sunglasses:
Will have to work with them later Saturday, going to Burbank airport first thing Saturday morning, or Sunday.


© 2021 | Articles Website | Forums Rules & FAQ