Can anyone recommend a formula...

Hi all. I'm using nested "IF" functions in a formula, and I know there's a better way. I just don't know what it is. In the attached screenshot, I need a formula to put in D18 that compares the current "Unit" number (B18) to the previous matching unit number (B15), then subtract the "Odometer" reading matching that unit number (C15) from the current "Odometer" reading (C18). The records will never be entered in the same order, so I can't just look back 3 rows. The previous odometer will always be the MAX in the field for the corresponding unit number. Basically, I need to track the daily mileage for three different vehicles.

Thanks for your help.

Maybe someone has a better idea, but I would suggest an array formula.

If you have used array formulas before, then just enter this:
=MAX((B$2:B17=B18)*((C$2:C17)))

If you have not used Array formulas before, let me explain.

If you add another column that has this formula:
=B2=B$18
you will get a column of TRUE and FALSE

Let's say you used column F for that. If in the column next to it you put
=F2*C2 and filled that down you would get a column that had odometer readings only for the one unit. Let's say that was column G.

What you want is the max of column G. To get all of that in a single formula, you use an array formula. When you enter (or edit) an array formula, you must always press Ctrl+Shift+Enter.

When you do that, Excel will enclose the formula in {} brackets (which you can see in the formula bar).

Perfect! I couldn't ask for anything better. You must have spent a whole 3 minutes on that formula. Wish I had that kind of talent. Wish I had found this forum 5 days ago! I think I need to buy some books!
Thank you very much.

You could also try this formula in d18

=C18-LOOKUP(2,1/(B$2:B17=B18),C$2:C17)

which doesn't require CTRL+SHIFT+ENTER

Hi dll,

I agree that if there is any way to avoid using an Array Formula, one should use that way. Not only because of the troublesome requirement of using Ctrl+Shift+Enter, but also because Array Formulas are memory hogs.

I love the simplicity of the formula you provided. But, I do not quite understand it. Could you explain it?

I suspect the MAX formula you posted is actually the best way to go, it's a little more transparent than my suggestion, but I know some people prefer to avoid CSE.

It looks simple but it's quite tricky to explain. Lets assume the formula is actually

=C18-LOOKUP(2,1/(B$14:B17=B18),C$14:C17)

the part (B$14:B17=B18) just gives an array of TRUE/FALSE values but when you divide 1 by this array, because FALSE becomes 0 and TRUE 1 this is converted to an array of 1s and #DIV/0! errors. so given the example used above you'd get

e.g.

1/{FALSE;TRUE;FALSE,FALSE}

becomes

{#DIV/0!;1;#DIV/0!;#DIV/0!}

The next part exploits a quirk of LOOKUP

=LOOKUP(2,{#DIV/0!;1;#DIV/0!;#DIV/0!},C$14:C17)

This lookups up 2 in the first vector and returns the corresponding value from the second.

Obviously 2 won't be found in the first vector, but when it isn't the value returned is the LAST numeric, i.e. the 1 in second place, thus C15 [in the corresponding poistion in the second vector] is returned

Thanks for the explanation. Quirky or not, I like it.

I played around with it a bit and could not for the life of me understand why this exact formulation worked while very similar ones that avoided the divide by zero did not work.

I am very accustomed to using errors to help find answers in VBA. But, I had never thought to intentionally create errors in formulas. It's brilliant!