Find second highestsalesprice

[jkr]

I have a spreadsheet:

Price Status
100
120 Sold
115 Sold
130
140 Sold

Now I try to find the seocnd larges amout something is sold for. I
have tried something like

=SUMPRODUCT((B2:B6="Sold")*(A2:A6=LARGE(A2:A6;2))*(A2:A6)

or

=IF(C2:C15="solgt";LARGE(D215;2))

entered as an array formula, but both (other attempts as well) returns
130 (the second highest price) where it should return120 (second
highes price, sold).

Any suggestions?

Jan

 

[Jarek Kujawa]

would this help (array-entered):

=LARGE(IF(C2:C15="sold";D215;2))

 

[Max]

A generic way, normal ENTER to confirm will do:
=INDEX(A2:A6,MATCH(1,INDEX((B2:B6="Sold")*(ISNUMBER(MATCH(LARGE(A2:A6,2),A2:A6,0))),),0))

Change the commas to semicolons to suit your delimiter
High-five? Click the Stars below (in google)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik

 

[Ashish Mathur]

Hi,

you can try this array formula (Ctrl+Shift+Enter)

LARGE((B2:B6="Sold")*(A2:A6),2)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Jan Kronsell]

Thank you all. I used the solution from Ashish as that was the shortest.

Jan

 

[Ashish Mathur]

Hi,

I'm glad my solution helped.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Jarek Kujawa]

welcome

Thank you all. I used the solution from Ashish as that was the shortest.

Jan









- Poka¿ cytowany tekst -