Too many nested "IF"'s: a work around needed for this example...
 

Hello,

I have a list of ithe names of the tems I have had quoted on by
suppliers in colum range A2:A10. I have a row of the names of
suppliers in cells B1:H1. The supplier quoted prices fill the cells
B2:H10. Not all suppliers have quoted on every item so some cells are
blank (zero).

I have been trying to get Excel to tell me the "Best Priced" and
"Second Best Priced" supplier name for each item in the cell ranges
I2:I10 and J2:J10 respectively, without it selling zero/blank cells as
the lowest price.

The limitation on nested if formulas has prevented me from using a
forumla like this to determine the Best Priced supplier name for Item
listed in A1:

=IF($B2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$B$1,I F($C2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$C$1,IF( $D2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$D$1,IF($E 2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,IF($E2= SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,IF($E2=SM ALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,IF($E2=SMAL L($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,"ERROR")))))) )

Note: the COUNTIF(x,0) is required to prevent any blanks or zeros from
being counted as the "Best Price"/lowest price.

This formula would work otherwise.

I have tried using the work around mentioned here
(http://www.cpearson.com/excel/nested.htm) but fo rsome reason I have
been getting #VALUE! error.

Would be great it someone could a work around.

TIA

Bullman

Too many nested "IF"'s: a work around needed for this example...
 

Hi!

Enter this in I2:

=INDEX($B$1:$H$1,MATCH(SMALL($B2:$H2,COUNTIF($B2:$ H2,0)+COLUMNS($A:A)),$B2:$H2,0))

Copy across to J2. Then select both I2 and J2 and copy down to row 10.

Biff

"Bullman" wrote in message
oups.com...
Hello,

I have a list of ithe names of the tems I have had quoted on by
suppliers in colum range A2:A10. I have a row of the names of
suppliers in cells B1:H1. The supplier quoted prices fill the cells
B2:H10. Not all suppliers have quoted on every item so some cells are
blank (zero).

I have been trying to get Excel to tell me the "Best Priced" and
"Second Best Priced" supplier name for each item in the cell ranges
I2:I10 and J2:J10 respectively, without it selling zero/blank cells as
the lowest price.

The limitation on nested if formulas has prevented me from using a
forumla like this to determine the Best Priced supplier name for Item
listed in A1:

=IF($B2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$B$1,I F($C2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$C$1,IF( $D2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$D$1,IF($E 2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,IF($E2= SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,IF($E2=SM ALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,IF($E2=SMAL L($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,"ERROR")))))) )

Note: the COUNTIF(x,0) is required to prevent any blanks or zeros from
being counted as the "Best Price"/lowest price.

This formula would work otherwise.

I have tried using the work around mentioned here
(http://www.cpearson.com/excel/nested.htm) but fo rsome reason I have
been getting #VALUE! error.

Would be great it someone could a work around.

TIA

Bullman

Too many nested "IF"'s: a work around needed for this example...
 

We tested these formulae are not affected by "blank" cells

=min(J2:J10)
=small(J2:J10, 2)

"Bullman" wrote in message
oups.com...
Hello,

I have a list of ithe names of the tems I have had quoted on by
suppliers in colum range A2:A10. I have a row of the names of
suppliers in cells B1:H1. The supplier quoted prices fill the cells
B2:H10. Not all suppliers have quoted on every item so some cells are
blank (zero).

I have been trying to get Excel to tell me the "Best Priced" and
"Second Best Priced" supplier name for each item in the cell ranges
I2:I10 and J2:J10 respectively, without it selling zero/blank cells as
the lowest price.

The limitation on nested if formulas has prevented me from using a
forumla like this to determine the Best Priced supplier name for Item
listed in A1:


=IF($B2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$B$1,I F($C2=SMALL($B2:$H2,COUNTI
F($B2:$H2,0)+1),$C$1,IF($D2=SMALL($B2:$H2,COUNTIF( $B2:$H2,0)+1),$D$1,IF($E2=
SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,IF($E2=SM ALL($B2:$H2,COUNTIF($B2:$H
2,0)+1),$E$4,IF($E2=SMALL($B2:$H2,COUNTIF($B2:$H2, 0)+1),$E$4,IF($E2=SMALL($B
2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,"ERROR")))))))

Note: the COUNTIF(x,0) is required to prevent any blanks or zeros from
being counted as the "Best Price"/lowest price.

This formula would work otherwise.

I have tried using the work around mentioned here
(http://www.cpearson.com/excel/nested.htm) but fo rsome reason I have
been getting #VALUE! error.

Would be great it someone could a work around.

TIA

Bullman

Too many nested "IF"'s: a work around needed for this example...
 

how about?

in cell i3 (for row 3 with names in row 2)
=OFFSET($A2,0,MATCH(SMALL(IF($B$3:$H$30,$B$3:$H$3 ),1),$B$3:$H$3,0))
in cell j3
=OFFSET($A2,0,MATCH(SMALL(IF($B$3:$H$30,$B$3:$H$3 ),2),$B$3:$H$3,0))

both are array formulas

the idea is to id the 2 smallest non zero entries in row 3, and find the
corresponding name in row 2 with the offset/match combo

does this work for you?

"Bullman" wrote:

Hello,

I have a list of ithe names of the tems I have had quoted on by
suppliers in colum range A2:A10. I have a row of the names of
suppliers in cells B1:H1. The supplier quoted prices fill the cells
B2:H10. Not all suppliers have quoted on every item so some cells are
blank (zero).

I have been trying to get Excel to tell me the "Best Priced" and
"Second Best Priced" supplier name for each item in the cell ranges
I2:I10 and J2:J10 respectively, without it selling zero/blank cells as
the lowest price.

The limitation on nested if formulas has prevented me from using a
forumla like this to determine the Best Priced supplier name for Item
listed in A1:

=IF($B2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$B$1,I F($C2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$C$1,IF( $D2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$D$1,IF($E 2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,IF($E2= SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,IF($E2=SM ALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,IF($E2=SMAL L($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,"ERROR")))))) )

Note: the COUNTIF(x,0) is required to prevent any blanks or zeros from
being counted as the "Best Price"/lowest price.

This formula would work otherwise.

I have tried using the work around mentioned here
(http://www.cpearson.com/excel/nested.htm) but fo rsome reason I have
been getting #VALUE! error.

Would be great it someone could a work around.

TIA

Bullman

Too many nested "IF"'s: a work around needed for this example...
 

Thanks all!
Will check em out when I get back to work next week.
Will let you knwo how I go.

Cheers

Bullman

PS: This is one heck of a friendly and helpful NG. :D Thanks all again.

Too many nested "IF"'s: a work around needed for this example...
 

Biff wrote:

Hi!

Enter this in I2:

=INDEX($B$1:$H$1,MATCH(SMALL($B2:$H2,COUNTIF($B2:$ H2,0)+COLUMNS($A:A)),$B2:$H2,0))

Copy across to J2. Then select both I2 and J2 and copy down to row 10.

Biff

This seems to do the trick!! Nice work around. Thank you Biff.

All the other suggestions also worked as well. Thanks all.

Bullman