Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested subtotals don't work properly after applying recommendation | Excel Worksheet Functions | |||
Nested IF - Work Around for 7 nest Max | Excel Worksheet Functions | |||
Sumproduct help needed! | Excel Worksheet Functions | |||
Nested if, sum & vlookup Function | Excel Worksheet Functions | |||
Nested If/Then Math Expert Needed | Excel Worksheet Functions |