if problem

[eran3185]

hi
i have a table taht contain 3 columns :
d- car number
e-PART PRICE IN $ (every car has some parts)
f-price
i want a formula that calculate column f in this way :

if there is more then 2 parts in a car -> the 2 expensive will get the price of the part (maximum 300$) , the other parts will get "-".

for example : in car 100 there are 5 parts , the 2 expensive are 500$ (f will get 300$)and 250$ (f will get 250$).

what formula can i use ?

[Bob Phillips]

You are going to have to explain that one again. Why 300 and 250?

[eran3185]

what i mean : if the 2 expensive parts are greater then 300$ then the price will be 300$ , but if it's less then 300$ the price will be same as the part price in column e.
in car 100 there are 5 parts , the 2 expensive are :
1) 500$ (f will get 300$) 500>300 ---> 300
2) 250$ (f will get 250$) 250<300 ---> 250

[Bob Phillips]

Okay, here you go.

Select cell F3 and add this range name (Insert>=Name>Define...) with a name of matches and a ReferTo value of

=MATCH('1'!$E$3:$E$15,LARGE(IF('1'!$D$3:$D$15='1'!A3,'1'!$E$3:$E$15),{1,2}) ,0)

and then in F3 itself add

=MIN(IF(ISNUMBER(MATCH(ROW(),LARGE(IF(($D$3:$D$15=D3)*(ISNUMBER(matches)),R OW($E$3:$E$15)),{1,2}),0)),E3,0),$H$2)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

Copy F3 down.

[eran3185]

i get 0 ...

[Bob Phillips]

Okay, here you go.

Select cell F3 and add this range name (Insert>=Name>Define...) with a name of matches and a ReferTo value of

=MATCH('1'!$E$3:$E$15,LARGE(IF('1'!$D$3:$D$15='1'!A3,'1'!$E$3:$E$15),{1,2}) ,0)

It won't work without this bit, you cannot call ir PRICE and then use matches in the formula.

But I admit to a small error here, it is not ='1'!A3 but ='1'!D3

[Shazam]

Maybe...


Input formula in cell F3 and copy down.

=IF(OR(E3=LARGE(IF($D$3:$D$15=D3,$E$3:$E$15),1),E3=LARGE(IF($D$3:$D$15=D3,$ E$3:$E$15),2)),IF(E3>300,300,IF(E3<300,E3,"")),"")

Formula is an-array need to hold down:

Ctrl,Shift,Enter


Hope it helps!