Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|