View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips Bob Phillips is offline
external usenet poster
 
Posts: 1,726
Default 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