Formula problem

[Dave]

I have created a drivers system in excel, I require this system to
include mileages of the cars. I currently put the mileage reading into
a column and compare it to the last reading. I want the spreadsheet to
this automatically, which would save a great deal of time. So using
the example below the reading would get the new value for C which is
5890 and the of 5888 and deduce a figure of 2.

Hear is an example with the first four values being the start up
Mileages of the drivers.

Car Mileage Days Mileage
A 10145 -
B 8999 -
C 5888 -
D 7885 -
B 9019 20
D 7906 21
A 10190 45
D 7935 29
C 5890 2

 

[Guest]

Hi,

Try this:

=MAX(IF($A$2:A6=A6,$B$2:B6))-MAX(IF(($A$2:A6=A6)*($B$2:B6<MAX(IF($A$2:A6=A6,$B$2:B6))),$B$2:B6))

enter using Ctrl+Shift+Enter

put this formula in the cell with the first daily reading (not the starting
reading), in your example it would be C6 don't forget to adjust the ranges to
suit your setup then copy down.

HTH
Jean-Guy

 

[Guest]

Maybe something like this:

With
A1:B10 containing your posted sample data

This formula returns the latest mileage increment
C6: =B6-LARGE(INDEX(($A$2:A6=A6)*$B$2:B6,0),2)
Copy that formula down

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Dave]

Hi,

Try this:

=MAX(IF($A$2:A6=A6,$B$2:B6))-MAX(IF(($A$2:A6=A6)*($B$2:B6<MAX(IF($A$2:A6=A6,$B$2:B6))),$B$2:B6))

enter using Ctrl+Shift+Enter

put this formula in the cell with the first daily reading (not the starting
reading), in your example it would be C6 don't forget to adjust the ranges to
suit your setup then copy down.

HTH
Jean-Guy

Thanks guys for your time they both work a treat. Definetly something
I can use in the spreadsheet.