How to Find the Minus Kilometre

[Rohinikumar]

Hi some one can help i think...
That the i have a vehicle details sheet i mean starting kilometre and closing
kilometres, but some time that drivers are cheated like below i mean they are
mentioned less kilometre insted of correct, i mean closing K.M is 30
but they are mentioned starting 28 unnesosrly we have to paid for two
KM.some one can help me how to find minus Kilometres.

Veh NO ST KM CL KM Total KM
1245 12 19 7
2517 15 21 6
8521 18 25 7
3654 21 29 8
7895 25 45 20
3157 32 51 19
1245 45 60 15
2517 87 99 12
8521 15 25 10
3654 17 22 5
7895 28 36 8
3157 31 48 17

 

[Jacob Skaria]

The formula posted yesterday will give you the actual total kilometers done
by a vehicle (excluding the wrong entries). Try that as an array formula

 

[Rohinikumar]

Hi Jacob i tried as you said but it shown like below

Veh NO ST KM CL KM Total KM 1
2415 12 19 7 88
2517 15 21 6 1
8521 18 25 7 1
2415 17 29 12 85
7895 25 45 20 1
2415 27 51 24 85
1245 45 60 15 1
2517 87 99 12 1
8521 15 25 10 1
3654 17 22 5 1
7895 28 36 8 1
3157 31 48 17 1

how can i highlight and how can i find howmany kM they reduced insted of
correct?

 

[Jacob Skaria]

You have not applied the formula as an array formula..Use Shift+Ctrl+Enter
instead of Enter to apply the formula. The below array formula; if applied in
cell E1 will calculate the total kilometeres done for the vehicle number
mentionedin cell A1

For example maxkilmoter done by vehicle 2415 is 51; min done is 12 so the
total kilometers done by the vehile is 51-12+1 = 40

=MAX(IF($A$1:$A$100=A2,$C$1:$C$100))-
MIN(IF($A$1:$A$100=A2,$B$1:$B$100))+1

 

[Rohinikumar]

HI Jacob thx its working and i have one more doubt that how can i find which
vehicle reduce the KM and how to highlight the cab NO

 

[Jacob Skaria]

If you are looking for a total aganist each row then in cell D2; apply the
below formula and copy down as required

(Enter the formula using Shift+Ctrl+Enter)
=MAX(0,C2-MAX(IF($A$2:A2=A2,IF($C$2:C2<>C2,$C$2:C2)),B2))

 

[Jacob Skaria]

Sort your data as below...First by Vehicle number and then by St Km..

Veh NO ST KM CL KM
1245 45 60
2415 12 19
2415 17 29
2415 27 51
2517 15 21
2517 87 99
3157 31 48
3654 17 22
7895 25 45
7895 28 36
8521 15 25
8521 18 25

Select A2:A13 or what ever end range. From menu Format>Conditional
Formatting>Formula Is and then enter the below formula. Select a fill color
and apply..This should highlight all the invalid vehicles with invalid start
km

=B2<>MAX(IF($A$1:A1=A2,$C$1:C1),B2)

 

[Rohinikumar]

Thank you very much Mr Jacob,

and one more doubt how can i find how many KM they reduce per each vehicle
if there is any formula for all vehicles.i meam if they reduce one KM it show
-1 like this.

 

[Rohinikumar]

Hi Jacob could you clarifi for the below?

Thank you very much Mr Jacob,

and one more doubt how can i find how many KM they reduce per each vehicle
if there is any formula for all vehicles.i meam if they reduce one KM it show
-1 like this.

 

[Jacob Skaria]

=SUMPRODUCT(($A$2:$A$100=A2)*($C$2:$C$100-$B$2:$B$100))
will return the difference in kms for the vehicle in cell A2

So difference of the above amount from the earlier calculation should give
you what you are looking for (array formula./.)
=MAX(0,SUMPRODUCT(($A$2:$A$100=A2)*($C$2:$C$100-$B$2:$B$100))-
(MAX(IF($A$1:$A$100=A2,$C$1:$C$100))-
MIN(IF($A$1:$A$100=A2,$B$1:$B$100))))