Find second highestsalesprice
 

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(D2:D15;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

Find second highestsalesprice
 

would this help (array-entered):

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


On 25 Lut, 09:39, wrote:
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(D2:D15;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

Find second highestsalesprice
 

A generic way, normal ENTER to confirm will do:
=INDEX(A2:A6,MATCH(1,INDEX((B2:B6="Sold")*(ISNUMBE R(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
---
wrote in message
...
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(D2:D15;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

Find second highestsalesprice
 

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

wrote in message
...
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(D2:D15;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

Find second highestsalesprice
 

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

Jan

wrote:
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(D2:D15;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

Find second highestsalesprice
 

Hi,

I'm glad my solution helped.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Jan Kronsell" wrote in message
...
Thank you all. I used the solution from Ashish as that was the shortest.

Jan

wrote:
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(D2:D15;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

Find second highestsalesprice
 

welcome

On 25 Lut, 17:26, "Jan Kronsell"
wrote:
Thank you all. I used the solution from Ashish as that was the shortest.

Jan



wrote:
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(D2:D15;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- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -