Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumproduct , array or countif?
this is my problem: the number for tyre replacement is the location of the tyres(1,2,3,4,5) vehicle no start km end km tyre replacement(if any) x1234 45 67 1 z4567 37 65 x1234 67 85 z4567 65 100 3 x1234 85 112 1 result :112-67= The key to this problem is that if one month later, x1234 has tyre change at 1 and the end km is recorded at 250( e.g) The result wil be 250-11. Hope someone can help me... -------------------------------------------------------------------------------- -- cjjoo ------------------------------------------------------------------------ cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916 View this thread: http://www.excelforum.com/showthread...hreadid=474645 |
#2
|
|||
|
|||
=MAX(IF((A2:A20="x1234")*(D2:D20=1),C2:C20))-MIN(IF((A2:A20="x124")*(D2:D20=
1),C2:C20)) -- HTH Bob Phillips "cjjoo" wrote in message ... this is my problem: the number for tyre replacement is the location of the tyres(1,2,3,4,5) vehicle no start km end km tyre replacement(if any) x1234 45 67 1 z4567 37 65 x1234 67 85 z4567 65 100 3 x1234 85 112 1 result :112-67= The key to this problem is that if one month later, x1234 has tyre change at 1 and the end km is recorded at 250( e.g) The result wil be 250-11. Hope someone can help me... -------------------------------------------------------------------------- ------ -- cjjoo ------------------------------------------------------------------------ cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916 View this thread: http://www.excelforum.com/showthread...hreadid=474645 |
#3
|
|||
|
|||
Forgot to mention it is an array formula so commit with Ctrl-Shift-Enter.
-- HTH Bob Phillips "cjjoo" wrote in message ... this is my problem: the number for tyre replacement is the location of the tyres(1,2,3,4,5) vehicle no start km end km tyre replacement(if any) x1234 45 67 1 z4567 37 65 x1234 67 85 z4567 65 100 3 x1234 85 112 1 result :112-67= The key to this problem is that if one month later, x1234 has tyre change at 1 and the end km is recorded at 250( e.g) The result wil be 250-11. Hope someone can help me... -------------------------------------------------------------------------- ------ -- cjjoo ------------------------------------------------------------------------ cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916 View this thread: http://www.excelforum.com/showthread...hreadid=474645 |
#4
|
|||
|
|||
the formula soves the first part of the problem but it only calculates the difference of the max and the min distance for lcation 1. If there is another entry for x1234 with start km =112 end km=250, can there be a formula to give a result of 250-112? -- cjjoo ------------------------------------------------------------------------ cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916 View this thread: http://www.excelforum.com/showthread...hreadid=474645 |
#5
|
|||
|
|||
data in a3:d15, tire # in g3 =SMALL(IF((D3:D15=G3)*(A3:A15="x1234"),C3:C15),COU NTIF(D3:D15,G3))-SMALL(IF((D3:D15=G3)*(A3:A15="x1234"),C3:C15),COUN TIF(D3:D15,G3)-1) should give delta of two highest mileage for tire # in g3 cntl+shift+enter -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=474645 |
#6
|
|||
|
|||
sorry - the count needs to be only for the car in question, as well as the tire #. =SMALL(IF(($D$3:$D$15=G3)*($A$3:$A$15="x1234"),$C$ 3:$C$15),COUNT(IF(($D$3:$D$15=G3)*($A$3:$A$15="x12 34"),$D$3:$D$15)))-SMALL(IF(($D$3:$D$15=G3)*($A$3:$A$15="x1234"),$C$3 :$C$15),COUNT(IF(($D$3:$D$15=G3)*($A$3:$A$15="x123 4"),$D$3:$D$15))-1) -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=474645 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct + Array Function? | Excel Worksheet Functions | |||
countif, sumproduct | New Users to Excel | |||
Changing a range of an array in a SUMPRODUCT formula gives a #N/A error | Excel Discussion (Misc queries) | |||
Using COUNTIF with 2 criteria - SUMPRODUCT? | Excel Worksheet Functions | |||
SUMPRODUCT & COUNTIF | Excel Worksheet Functions |