Excel 2002 : How to deduct the first and last number ?
 

Dear Sir,

I have the following worksheet :

A B C D
E
1 TRUCK DOC DATE QUANTITY METER MILEAGE
2 BDT7198 01/12/2006 204.00 294151
3 BDT7198 12/12/2006 299.00 294251
4 BDT7198 20/12/2006 295.00 294455
5 BDT7198 27/12/2006 273.00 294852
6 BDT7198 Quantity sub-total 1,071.00 =(D5-D2)

7 BEF4050 07/12/2006 100.00 447528
8 BEF4050 15/12/2006 102.00 448300
9 BEF4050 202.00
=(D8-D7)
10 BEK8152 12/12/2006 331.00 228919
11 BEK8152 15/12/2006 273.00 229455
12 BEK8152 20/12/2006 298.00 230060
13 BEK8152 27/12/2006 253.00 230486
14 BEK8152 1,155.00
=(D13-D10)
15 BEL6718 06/12/2006 287.00 613726
16 BEL6718 07/12/2006 281.00 613999
17 BEL6718 12/12/2006 329.00 614479
18 BEL6718 14/12/2006 293.00 614927
19 BEL6718 16/12/2006 309.00 615371
1,499.00
=(D19-D15)


There is a blank between different truch number, may I know what formula I
must enter at E2 and copy downwards to get the answers at E6, E9 and E13 ?

These answers are the last meter reading minus the first meter reading of
the same truck.

I try using formula : IF(A2=A3,"",D2-VLOOKUP(A2,A$2:D$19,4,FALSE)), but it
does not work.


Thanks
Low




--
A36B58K641

Excel 2002 : How to deduct the first and last number ?
 

Enter this in E2

=IF(C2="",SUM(INDEX($C$1:C2,MAX(MAX(IF($C$1:$C1="" ,ROW($C$1:$C1))),1)):C2)

it is an array formula, so commit with Ctrl-Shift-enter, not just Enter

copy E2 down

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet :

A B C D
E
1 TRUCK DOC DATE QUANTITY METER MILEAGE
2 BDT7198 01/12/2006 204.00 294151
3 BDT7198 12/12/2006 299.00 294251
4 BDT7198 20/12/2006 295.00 294455
5 BDT7198 27/12/2006 273.00 294852
6 BDT7198 Quantity sub-total 1,071.00
=(D5-D2)

7 BEF4050 07/12/2006 100.00 447528
8 BEF4050 15/12/2006 102.00 448300
9 BEF4050 202.00
=(D8-D7)
10 BEK8152 12/12/2006 331.00 228919
11 BEK8152 15/12/2006 273.00 229455
12 BEK8152 20/12/2006 298.00 230060
13 BEK8152 27/12/2006 253.00 230486
14 BEK8152 1,155.00
=(D13-D10)
15 BEL6718 06/12/2006 287.00 613726
16 BEL6718 07/12/2006 281.00 613999
17 BEL6718 12/12/2006 329.00 614479
18 BEL6718 14/12/2006 293.00 614927
19 BEL6718 16/12/2006 309.00 615371
1,499.00
=(D19-D15)


There is a blank between different truch number, may I know what formula I
must enter at E2 and copy downwards to get the answers at E6, E9 and E13 ?

These answers are the last meter reading minus the first meter reading of
the same truck.

I try using formula : IF(A2=A3,"",D2-VLOOKUP(A2,A$2:D$19,4,FALSE)), but it
does not work.


Thanks
Low




--
A36B58K641

Excel 2002 : How to deduct the first and last number ?
 

It looks like your data is sorted by Column A (Truck Number), in
ascending order. If so, maybe...

E2, copied down:

=IF((D2="")*(D3=""),INDEX($D$2:D2,MATCH(A2,$A$2:A2 )-1)-INDEX($D$2:D2,MATC
H(A2,$A$2:A2,0)),"")

It assumes that there is in fact a blank row between different truck
numbers, as you've indicated.

Hope this helps!

In article ,
Mr. Low wrote:

Dear Sir,

I have the following worksheet :

A B C D
E
1 TRUCK DOC DATE QUANTITY METER MILEAGE
2 BDT7198 01/12/2006 204.00 294151
3 BDT7198 12/12/2006 299.00 294251
4 BDT7198 20/12/2006 295.00 294455
5 BDT7198 27/12/2006 273.00 294852
6 BDT7198 Quantity sub-total 1,071.00 =(D5-D2)

7 BEF4050 07/12/2006 100.00 447528
8 BEF4050 15/12/2006 102.00 448300
9 BEF4050 202.00
=(D8-D7)
10 BEK8152 12/12/2006 331.00 228919
11 BEK8152 15/12/2006 273.00 229455
12 BEK8152 20/12/2006 298.00 230060
13 BEK8152 27/12/2006 253.00 230486
14 BEK8152 1,155.00
=(D13-D10)
15 BEL6718 06/12/2006 287.00 613726
16 BEL6718 07/12/2006 281.00 613999
17 BEL6718 12/12/2006 329.00 614479
18 BEL6718 14/12/2006 293.00 614927
19 BEL6718 16/12/2006 309.00 615371
1,499.00
=(D19-D15)


There is a blank between different truch number, may I know what formula I
must enter at E2 and copy downwards to get the answers at E6, E9 and E13 ?

These answers are the last meter reading minus the first meter reading of
the same truck.

I try using formula : IF(A2=A3,"",D2-VLOOKUP(A2,A$2:D$19,4,FALSE)), but it
does not work.


Thanks
Low

Excel 2002 : How to deduct the first and last number ?
 

Try filling down from E2:

=IF(D2,IF(N(D1),D2-D1+E1,0),N(E1))

As an alternative to the formula approach, copy column D to E, choose
Edit Goto Special blanks and click the autosum button then
replace "sum" by "min" in column D and by "max" in column E. The
difference of the two columns gives you the result.
(Note: Autosum only works on multiple selections when the sum option is
used.)


Mr. Low wrote:

Dear Sir,

I have the following worksheet :

A B C D
E
1 TRUCK DOC DATE QUANTITY METER MILEAGE
2 BDT7198 01/12/2006 204.00 294151
3 BDT7198 12/12/2006 299.00 294251
4 BDT7198 20/12/2006 295.00 294455
5 BDT7198 27/12/2006 273.00 294852
6 BDT7198 Quantity sub-total 1,071.00 =(D5-D2)

7 BEF4050 07/12/2006 100.00 447528
8 BEF4050 15/12/2006 102.00 448300
9 BEF4050 202.00
=(D8-D7)
10 BEK8152 12/12/2006 331.00 228919
11 BEK8152 15/12/2006 273.00 229455
12 BEK8152 20/12/2006 298.00 230060
13 BEK8152 27/12/2006 253.00 230486
14 BEK8152 1,155.00
=(D13-D10)
15 BEL6718 06/12/2006 287.00 613726
16 BEL6718 07/12/2006 281.00 613999
17 BEL6718 12/12/2006 329.00 614479
18 BEL6718 14/12/2006 293.00 614927
19 BEL6718 16/12/2006 309.00 615371
1,499.00
=(D19-D15)


There is a blank between different truch number, may I know what formula I
must enter at E2 and copy downwards to get the answers at E6, E9 and E13 ?

These answers are the last meter reading minus the first meter reading of
the same truck.

I try using formula : IF(A2=A3,"",D2-VLOOKUP(A2,A$2:D$19,4,FALSE)), but it
does not work.


Thanks
Low




--
A36B58K641

Excel 2002 : How to deduct the first and last number ?
 

And to add to your confusion, and choices available, I came up with this one
which you can put into E2 and then fill down:
=IF(AND(N(D1)0,N(D2)0,N(B2)0),D2-OFFSET(A$1,MATCH(OFFSET(D2,0,-3),A$2:A$19,0),3),"")

Like Lori's formula, it gives cumulative mileage at each point. The AND()
portion checks that values in column D on current row and row above it are
numeric, and that the value in column B on same row is numeric (date). If
those 3 conditions are met, the value is calculated.

Also, a formula to use to get the subtotal at D6, D9, D14 and D20 is (place
in D6, then copy to each of the other cells)
=OFFSET(D6,-1,0)-OFFSET(A$1,MATCH(OFFSET(D6,-1,-3),A$2:A$25,0),3)
Although that will give you same answer that is last one for that truck in
column E so you could write it as
=Offset(D6,-1,1)



"Mr. Low" wrote:

Dear Sir,

I have the following worksheet :

A B C D
E
1 TRUCK DOC DATE QUANTITY METER MILEAGE
2 BDT7198 01/12/2006 204.00 294151
3 BDT7198 12/12/2006 299.00 294251
4 BDT7198 20/12/2006 295.00 294455
5 BDT7198 27/12/2006 273.00 294852
6 BDT7198 Quantity sub-total 1,071.00 =(D5-D2)

7 BEF4050 07/12/2006 100.00 447528
8 BEF4050 15/12/2006 102.00 448300
9 BEF4050 202.00
=(D8-D7)
10 BEK8152 12/12/2006 331.00 228919
11 BEK8152 15/12/2006 273.00 229455
12 BEK8152 20/12/2006 298.00 230060
13 BEK8152 27/12/2006 253.00 230486
14 BEK8152 1,155.00
=(D13-D10)
15 BEL6718 06/12/2006 287.00 613726
16 BEL6718 07/12/2006 281.00 613999
17 BEL6718 12/12/2006 329.00 614479
18 BEL6718 14/12/2006 293.00 614927
19 BEL6718 16/12/2006 309.00 615371
1,499.00
=(D19-D15)


There is a blank between different truch number, may I know what formula I
must enter at E2 and copy downwards to get the answers at E6, E9 and E13 ?

These answers are the last meter reading minus the first meter reading of
the same truck.

I try using formula : IF(A2=A3,"",D2-VLOOKUP(A2,A$2:D$19,4,FALSE)), but it
does not work.


Thanks
Low




--
A36B58K641