Finding second largest, conditional value

[jkrons]

In a spreadsheet I have two columns, one with prices for all items
(Column D) and one with remarks (Column C). The rfemakrs can be emty
or Sold. Now i want to find the second most expensive item sold. I
have tried with

=SUMPRODUCT((C2:C150="sold")*(D2150=LARGE(D2150,2))*(D2150))

any other combinations like this, but without luck. It always seems
like i find the second most expensive price in the D-coumn, no matter
if the item is sold or not.

Any ideas

Jan

 

[Ron Rosenfeld]

In a spreadsheet I have two columns, one with prices for all items
(Column D) and one with remarks (Column C). The rfemakrs can be emty
or Sold. Now i want to find the second most expensive item sold. I
have tried with

=SUMPRODUCT((C2:C150="sold")*(D2150=LARGE(D2150,2))*(D2150))

any other combinations like this, but without luck. It always seems
like i find the second most expensive price in the D-coumn, no matter
if the item is sold or not.

Any ideas

Jan

Sometimes you should return a zero, with that formula.

Try this:

This formula must be **array-entered**:

=LARGE(IF(C2:C150="sold",D2150),2)
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
--ron

 

[jkrons]

Thank you. Why didn't I thik ogf using an array formula

Jan

 

[Aaron C]

I see this a lot in this group, and have stared at the help les for
serveral hours at a time, and am still lost. When is an array formula
needed, and when is it not? Basically, what are they for?

Thanks,
Aaron C

 

[Ron Rosenfeld]

Thank you. Why didn't I thik ogf using an array formula

Jan

You're welcome. Glad to help.
--ron

 

[T. Valko]

See this:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

--
Biff
Microsoft Excel MVP


I see this a lot in this group, and have stared at the help les for
serveral hours at a time, and am still lost. When is an array formula
needed, and when is it not? Basically, what are they for?

Thanks,
Aaron C