| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Formula not working
The following works:
=IF(AND(B22=150,B22<=159),18.5,IF(AND(B22=160,B2 2<=169),15.9,IF(AND(B22=1 70,B22<=179),13.8,IF(AND(B22=180,B22<=189),12.5,I F(AND(B22=190,B22<=199),1 0.7,IF(AND(B22=200,B22<=209),9.6,IF(AND(B22=210, B22<=219),9.2)))))))/2 However, the below does NOT =IF(ISNUMBER(B2),IF(AND(B22=150,B22<=159),18.5,IF (AND(B22=160,B22<=169),15 ..9,IF(AND(B22=170,B22<=179),13.8,IF(AND(B22=180 ,B22<=189),12.5,IF(AND(B22 =190,B22<=199),10.7,IF(AND(B22=200,B22<=209),9.6, IF(AND(B22=210,B22<=219), 9.2)))))))/2,"") By adding the ISNUMBER and first IF, casuses the formula to not work... Is it the length of the formula? Carl |
|
#2
|
|||
|
|||
|
Carl,
Yes it is. There is a limit of 7 nested IFs. You could try this though =IF(AND(ISNUMBER(B2),B2=150,B2<=220),VLOOKUP(B2,{ 0,18.5;160,15.9;170,13.8;1 80,12.5;190,10.7;200,9.6;220,9.2},2)/2,0) -- HTH RP (remove nothere from the email address if mailing direct) "Carl Hilton" wrote in message ... The following works: =IF(AND(B22=150,B22<=159),18.5,IF(AND(B22=160,B2 2<=169),15.9,IF(AND(B22=1 70,B22<=179),13.8,IF(AND(B22=180,B22<=189),12.5,I F(AND(B22=190,B22<=199),1 0.7,IF(AND(B22=200,B22<=209),9.6,IF(AND(B22=210, B22<=219),9.2)))))))/2 However, the below does NOT =IF(ISNUMBER(B2),IF(AND(B22=150,B22<=159),18.5,IF (AND(B22=160,B22<=169),15 ..9,IF(AND(B22=170,B22<=179),13.8,IF(AND(B22=180 ,B22<=189),12.5,IF(AND(B22 =190,B22<=199),10.7,IF(AND(B22=200,B22<=209),9.6, IF(AND(B22=210,B22<=219), 9.2)))))))/2,"") By adding the ISNUMBER and first IF, casuses the formula to not work... Is it the length of the formula? Carl |
|
#3
|
|||
|
|||
|
OK, I see there is a 7 nested limit... How can I get around this for this
formula?? THANKS "Carl Hilton" wrote in message ... The following works: =IF(AND(B22=150,B22<=159),18.5,IF(AND(B22=160,B2 2<=169),15.9,IF(AND(B22=1 70,B22<=179),13.8,IF(AND(B22=180,B22<=189),12.5,I F(AND(B22=190,B22<=199),1 0.7,IF(AND(B22=200,B22<=209),9.6,IF(AND(B22=210, B22<=219),9.2)))))))/2 However, the below does NOT =IF(ISNUMBER(B2),IF(AND(B22=150,B22<=159),18.5,IF (AND(B22=160,B22<=169),15 ..9,IF(AND(B22=170,B22<=179),13.8,IF(AND(B22=180 ,B22<=189),12.5,IF(AND(B22 =190,B22<=199),10.7,IF(AND(B22=200,B22<=209),9.6, IF(AND(B22=210,B22<=219), 9.2)))))))/2,"") By adding the ISNUMBER and first IF, casuses the formula to not work... Is it the length of the formula? Carl |
|
#4
|
|||
|
|||
|
That was quick... I looked up the VLOOKUP and still can not determine how
your formula works. Carl "Bob Phillips" wrote in message ... Carl, Yes it is. There is a limit of 7 nested IFs. You could try this though =IF(AND(ISNUMBER(B2),B2=150,B2<=220),VLOOKUP(B2,{ 0,18.5;160,15.9;170,13.8;1 80,12.5;190,10.7;200,9.6;220,9.2},2)/2,0) -- HTH RP (remove nothere from the email address if mailing direct) "Carl Hilton" wrote in message ... The following works: =IF(AND(B22=150,B22<=159),18.5,IF(AND(B22=160,B2 2<=169),15.9,IF(AND(B22=1 70,B22<=179),13.8,IF(AND(B22=180,B22<=189),12.5,I F(AND(B22=190,B22<=199),1 0.7,IF(AND(B22=200,B22<=209),9.6,IF(AND(B22=210, B22<=219),9.2)))))))/2 However, the below does NOT =IF(ISNUMBER(B2),IF(AND(B22=150,B22<=159),18.5,IF (AND(B22=160,B22<=169),15 ..9,IF(AND(B22=170,B22<=179),13.8,IF(AND(B22=180 ,B22<=189),12.5,IF(AND(B22 =190,B22<=199),10.7,IF(AND(B22=200,B22<=209),9.6, IF(AND(B22=210,B22<=219), 9.2)))))))/2,"") By adding the ISNUMBER and first IF, casuses the formula to not work... Is it the length of the formula? Carl |
|
#5
|
|||
|
|||
|
As Bob suggested, use VLOOKUP. He provided an example that looks like it will
do what you are attempting. For more, see: http://www.contextures.com/xlFunctions02.html tj "Carl Hilton" wrote: OK, I see there is a 7 nested limit... How can I get around this for this formula?? THANKS "Carl Hilton" wrote in message ... The following works: =IF(AND(B22=150,B22<=159),18.5,IF(AND(B22=160,B2 2<=169),15.9,IF(AND(B22=1 70,B22<=179),13.8,IF(AND(B22=180,B22<=189),12.5,I F(AND(B22=190,B22<=199),1 0.7,IF(AND(B22=200,B22<=209),9.6,IF(AND(B22=210, B22<=219),9.2)))))))/2 However, the below does NOT =IF(ISNUMBER(B2),IF(AND(B22=150,B22<=159),18.5,IF (AND(B22=160,B22<=169),15 ..9,IF(AND(B22=170,B22<=179),13.8,IF(AND(B22=180 ,B22<=189),12.5,IF(AND(B22 =190,B22<=199),10.7,IF(AND(B22=200,B22<=209),9.6, IF(AND(B22=210,B22<=219), 9.2)))))))/2,"") By adding the ISNUMBER and first IF, casuses the formula to not work... Is it the length of the formula? Carl |
|
#6
|
|||
|
|||
|
Trying Bob's forumla works with all my values except for a value of 218...
My formula should return 4.6 but Bob's returns 4.8... I will try to figure out how his forumla works so I can troubleshoot it. "tjtjjtjt" wrote in message ... As Bob suggested, use VLOOKUP. He provided an example that looks like it will do what you are attempting. For more, see: http://www.contextures.com/xlFunctions02.html tj "Carl Hilton" wrote: OK, I see there is a 7 nested limit... How can I get around this for this formula?? THANKS "Carl Hilton" wrote in message ... The following works: =IF(AND(B22=150,B22<=159),18.5,IF(AND(B22=160,B2 2<=169),15.9,IF(AND(B22=1 70,B22<=179),13.8,IF(AND(B22=180,B22<=189),12.5,I F(AND(B22=190,B22<=199),1 0.7,IF(AND(B22=200,B22<=209),9.6,IF(AND(B22=210, B22<=219),9.2)))))))/2 However, the below does NOT =IF(ISNUMBER(B2),IF(AND(B22=150,B22<=159),18.5,IF (AND(B22=160,B22<=169),15 ...9,IF(AND(B22=170,B22<=179),13.8,IF(AND(B22=18 0,B22<=189),12.5,IF(AND(B22 =190,B22<=199),10.7,IF(AND(B22=200,B22<=209),9.6, IF(AND(B22=210,B22<=219), 9.2)))))))/2,"") By adding the ISNUMBER and first IF, casuses the formula to not work... Is it the length of the formula? Carl |
|
#7
|
|||
|
|||
|
Never mind, I found the error... and think I undersdand... Where is a
discussion about making arrays with {}? "Carl Hilton" wrote in message ... Trying Bob's forumla works with all my values except for a value of 218... My formula should return 4.6 but Bob's returns 4.8... I will try to figure out how his forumla works so I can troubleshoot it. "tjtjjtjt" wrote in message ... As Bob suggested, use VLOOKUP. He provided an example that looks like it will do what you are attempting. For more, see: http://www.contextures.com/xlFunctions02.html tj "Carl Hilton" wrote: OK, I see there is a 7 nested limit... How can I get around this for this formula?? THANKS "Carl Hilton" wrote in message ... The following works: =IF(AND(B22=150,B22<=159),18.5,IF(AND(B22=160,B2 2<=169),15.9,IF(AND(B22=1 70,B22<=179),13.8,IF(AND(B22=180,B22<=189),12.5,I F(AND(B22=190,B22<=199),1 0.7,IF(AND(B22=200,B22<=209),9.6,IF(AND(B22=210, B22<=219),9.2)))))))/2 However, the below does NOT =IF(ISNUMBER(B2),IF(AND(B22=150,B22<=159),18.5,IF (AND(B22=160,B22<=169),15 ...9,IF(AND(B22=170,B22<=179),13.8,IF(AND(B22=18 0,B22<=189),12.5,IF(AND(B22 =190,B22<=199),10.7,IF(AND(B22=200,B22<=209),9.6, IF(AND(B22=210,B22<=219), 9.2)))))))/2,"") By adding the ISNUMBER and first IF, casuses the formula to not work... Is it the length of the formula? Carl |
|
#8
|
|||
|
|||
|
Change the 220 in Bob's formula to 210.
Is it this part of the formula that is throwing you off: {0,18.5;160,15.9;170,13.8;180,12.5;190,10.7;200,9. 6;210,9.2}, What this does is create what amounts to a 2 column by 7 row array of data. Each comma means go to the next value in the row. Each semicolon means go to the beginning of the next row. The VLOOKUP then looks for the value in (as Bob wrote it) B2 in the first column of that array. It then returns the value in the second column from the same row. This may be easier to understand if you create a data range on your spreadhseet to store the values in. Also, this site: http://www.contextures.com/xlFunctions02.html should help. The example use mostly cell ranges instead of typed arrays--the values in the {}. hth, tj "Carl Hilton" wrote: Trying Bob's forumla works with all my values except for a value of 218... My formula should return 4.6 but Bob's returns 4.8... I will try to figure out how his forumla works so I can troubleshoot it. "tjtjjtjt" wrote in message ... As Bob suggested, use VLOOKUP. He provided an example that looks like it will do what you are attempting. For more, see: http://www.contextures.com/xlFunctions02.html tj "Carl Hilton" wrote: OK, I see there is a 7 nested limit... How can I get around this for this formula?? THANKS "Carl Hilton" wrote in message ... The following works: =IF(AND(B22=150,B22<=159),18.5,IF(AND(B22=160,B2 2<=169),15.9,IF(AND(B22=1 70,B22<=179),13.8,IF(AND(B22=180,B22<=189),12.5,I F(AND(B22=190,B22<=199),1 0.7,IF(AND(B22=200,B22<=209),9.6,IF(AND(B22=210, B22<=219),9.2)))))))/2 However, the below does NOT =IF(ISNUMBER(B2),IF(AND(B22=150,B22<=159),18.5,IF (AND(B22=160,B22<=169),15 ...9,IF(AND(B22=170,B22<=179),13.8,IF(AND(B22=18 0,B22<=189),12.5,IF(AND(B22 =190,B22<=199),10.7,IF(AND(B22=200,B22<=209),9.6, IF(AND(B22=210,B22<=219), 9.2)))))))/2,"") By adding the ISNUMBER and first IF, casuses the formula to not work... Is it the length of the formula? Carl |
|
#9
|
|||
|
|||
|
The limit for nested "IF" functions is seven- you have eight.So, in a sense
, yes, the formula is too 'long' as you have too many nested "IF"s.Plus, what do you want returned for "True" in the first "IF"? "Carl Hilton" wrote in message ... The following works: =IF(AND(B22=150,B22<=159),18.5,IF(AND(B22=160,B2 2<=169),15.9,IF(AND(B22=1 70,B22<=179),13.8,IF(AND(B22=180,B22<=189),12.5,I F(AND(B22=190,B22<=199),1 0.7,IF(AND(B22=200,B22<=209),9.6,IF(AND(B22=210, B22<=219),9.2)))))))/2 However, the below does NOT =IF(ISNUMBER(B2),IF(AND(B22=150,B22<=159),18.5,IF (AND(B22=160,B22<=169),15 .9,IF(AND(B22=170,B22<=179),13.8,IF(AND(B22=180, B22<=189),12.5,IF(AND(B22 =190,B22<=199),10.7,IF(AND(B22=200,B22<=209),9.6, IF(AND(B22=210,B22<=219), 9.2)))))))/2,"") By adding the ISNUMBER and first IF, casuses the formula to not work... Is it the length of the formula? Carl |
|
#10
|
|||
|
|||
|
These may help. Your question is specifically about array constants. These
articles talk about Array Functions more broadly. Look at the beginning of the second article for a quick rundown of Array Constants. http://office.microsoft.com/en-us/as...872901033.aspx http://office.microsoft.com/en-us/as...872911033.aspx tj "Carl Hilton" wrote: Never mind, I found the error... and think I undersdand... Where is a discussion about making arrays with {}? "Carl Hilton" wrote in message ... Trying Bob's forumla works with all my values except for a value of 218... My formula should return 4.6 but Bob's returns 4.8... I will try to figure out how his forumla works so I can troubleshoot it. "tjtjjtjt" wrote in message ... As Bob suggested, use VLOOKUP. He provided an example that looks like it will do what you are attempting. For more, see: http://www.contextures.com/xlFunctions02.html tj "Carl Hilton" wrote: OK, I see there is a 7 nested limit... How can I get around this for this formula?? THANKS "Carl Hilton" wrote in message ... The following works: =IF(AND(B22=150,B22<=159),18.5,IF(AND(B22=160,B2 2<=169),15.9,IF(AND(B22=1 70,B22<=179),13.8,IF(AND(B22=180,B22<=189),12.5,I F(AND(B22=190,B22<=199),1 0.7,IF(AND(B22=200,B22<=209),9.6,IF(AND(B22=210, B22<=219),9.2)))))))/2 However, the below does NOT =IF(ISNUMBER(B2),IF(AND(B22=150,B22<=159),18.5,IF (AND(B22=160,B22<=169),15 ...9,IF(AND(B22=170,B22<=179),13.8,IF(AND(B22=18 0,B22<=189),12.5,IF(AND(B22 =190,B22<=199),10.7,IF(AND(B22=200,B22<=209),9.6, IF(AND(B22=210,B22<=219), 9.2)))))))/2,"") By adding the ISNUMBER and first IF, casuses the formula to not work... Is it the length of the formula? Carl |
|
#11
|
|||
|
|||
|
Small mistake, it should be
=IF(AND(ISNUMBER(B2),B2=150,B2<=220),VLOOKUP(B2,{ 0,18.5;160,15.9;170,13.8;1 80,12.5;190,10.7;200,9.6;210,9.2},2)/2,0) -- HTH RP (remove nothere from the email address if mailing direct) "Carl Hilton" wrote in message ... Trying Bob's forumla works with all my values except for a value of 218... My formula should return 4.6 but Bob's returns 4.8... I will try to figure out how his forumla works so I can troubleshoot it. "tjtjjtjt" wrote in message ... As Bob suggested, use VLOOKUP. He provided an example that looks like it will do what you are attempting. For more, see: http://www.contextures.com/xlFunctions02.html tj "Carl Hilton" wrote: OK, I see there is a 7 nested limit... How can I get around this for this formula?? THANKS "Carl Hilton" wrote in message ... The following works: =IF(AND(B22=150,B22<=159),18.5,IF(AND(B22=160,B2 2<=169),15.9,IF(AND(B22=1 70,B22<=179),13.8,IF(AND(B22=180,B22<=189),12.5,I F(AND(B22=190,B22<=199),1 0.7,IF(AND(B22=200,B22<=209),9.6,IF(AND(B22=210, B22<=219),9.2)))))))/2 However, the below does NOT =IF(ISNUMBER(B2),IF(AND(B22=150,B22<=159),18.5,IF (AND(B22=160,B22<=169),15 ...9,IF(AND(B22=170,B22<=179),13.8,IF(AND(B22=18 0,B22<=189),12.5,IF(AND(B22 =190,B22<=199),10.7,IF(AND(B22=200,B22<=209),9.6, IF(AND(B22=210,B22<=219), 9.2)))))))/2,"") By adding the ISNUMBER and first IF, casuses the formula to not work... Is it the length of the formula? Carl |
|
#12
|
|||
|
|||
|
Thanks a lot Bob... I would say that your solution is ingenious.. and fits
my needs exactly, especially with the continum of the number range. I had three other sets of numbers to work with and was able to adapt your method to all solutions. Carl "Bob Phillips" wrote in message ... Small mistake, it should be =IF(AND(ISNUMBER(B2),B2=150,B2<=220),VLOOKUP(B2,{ 0,18.5;160,15.9;170,13.8;1 80,12.5;190,10.7;200,9.6;210,9.2},2)/2,0) -- HTH RP (remove nothere from the email address if mailing direct) "Carl Hilton" wrote in message ... Trying Bob's forumla works with all my values except for a value of 218... My formula should return 4.6 but Bob's returns 4.8... I will try to figure out how his forumla works so I can troubleshoot it. "tjtjjtjt" wrote in message ... As Bob suggested, use VLOOKUP. He provided an example that looks like it will do what you are attempting. For more, see: http://www.contextures.com/xlFunctions02.html tj "Carl Hilton" wrote: OK, I see there is a 7 nested limit... How can I get around this for this formula?? THANKS "Carl Hilton" wrote in message ... The following works: =IF(AND(B22=150,B22<=159),18.5,IF(AND(B22=160,B2 2<=169),15.9,IF(AND(B22=1 70,B22<=179),13.8,IF(AND(B22=180,B22<=189),12.5,I F(AND(B22=190,B22<=199),1 0.7,IF(AND(B22=200,B22<=209),9.6,IF(AND(B22=210, B22<=219),9.2)))))))/2 However, the below does NOT =IF(ISNUMBER(B2),IF(AND(B22=150,B22<=159),18.5,IF (AND(B22=160,B22<=169),15 ...9,IF(AND(B22=170,B22<=179),13.8,IF(AND(B22=18 0,B22<=189),12.5,IF(AND(B22 =190,B22<=199),10.7,IF(AND(B22=200,B22<=209),9.6, IF(AND(B22=210,B22<=219), 9.2)))))))/2,"") By adding the ISNUMBER and first IF, casuses the formula to not work... Is it the length of the formula? Carl |
|
#13
|
|||
|
|||
|
=IF(ISNUMBER(B22),LOOKUP(B22,{-9.99999999999999E+307;150;160;170;180;190;200;210; 220},{0;18.5;15.9;13.8;12.5;10.7;9.6;9.2;0})/2,"")
Carl Hilton wrote: The following works: =IF(AND(B22=150,B22<=159),18.5,IF(AND(B22=160,B2 2<=169),15.9,IF(AND(B22=1 70,B22<=179),13.8,IF(AND(B22=180,B22<=189),12.5,I F(AND(B22=190,B22<=199),1 0.7,IF(AND(B22=200,B22<=209),9.6,IF(AND(B22=210, B22<=219),9.2)))))))/2 However, the below does NOT =IF(ISNUMBER(B2),IF(AND(B22=150,B22<=159),18.5,IF (AND(B22=160,B22<=169),15 .9,IF(AND(B22=170,B22<=179),13.8,IF(AND(B22=180, B22<=189),12.5,IF(AND(B22 =190,B22<=199),10.7,IF(AND(B22=200,B22<=209),9.6, IF(AND(B22=210,B22<=219), 9.2)))))))/2,"") By adding the ISNUMBER and first IF, casuses the formula to not work... Is it the length of the formula? Carl |
|
#14
|
|||
|
|||
|
Thanks for the feedback Carl.
Bob "Carl Hilton" wrote in message ... Thanks a lot Bob... I would say that your solution is ingenious.. and fits my needs exactly, especially with the continum of the number range. I had three other sets of numbers to work with and was able to adapt your method to all solutions. Carl "Bob Phillips" wrote in message ... Small mistake, it should be =IF(AND(ISNUMBER(B2),B2=150,B2<=220),VLOOKUP(B2,{ 0,18.5;160,15.9;170,13.8;1 80,12.5;190,10.7;200,9.6;210,9.2},2)/2,0) -- HTH RP (remove nothere from the email address if mailing direct) "Carl Hilton" wrote in message ... Trying Bob's forumla works with all my values except for a value of 218... My formula should return 4.6 but Bob's returns 4.8... I will try to figure out how his forumla works so I can troubleshoot it. "tjtjjtjt" wrote in message ... As Bob suggested, use VLOOKUP. He provided an example that looks like it will do what you are attempting. For more, see: http://www.contextures.com/xlFunctions02.html tj "Carl Hilton" wrote: OK, I see there is a 7 nested limit... How can I get around this for this formula?? THANKS "Carl Hilton" wrote in message ... The following works: =IF(AND(B22=150,B22<=159),18.5,IF(AND(B22=160,B2 2<=169),15.9,IF(AND(B22=1 70,B22<=179),13.8,IF(AND(B22=180,B22<=189),12.5,I F(AND(B22=190,B22<=199),1 0.7,IF(AND(B22=200,B22<=209),9.6,IF(AND(B22=210, B22<=219),9.2)))))))/2 However, the below does NOT =IF(ISNUMBER(B2),IF(AND(B22=150,B22<=159),18.5,IF (AND(B22=160,B22<=169),15 ...9,IF(AND(B22=170,B22<=179),13.8,IF(AND(B22=18 0,B22<=189),12.5,IF(AND(B22 =190,B22<=199),10.7,IF(AND(B22=200,B22<=209),9.6, IF(AND(B22=210,B22<=219), 9.2)))))))/2,"") By adding the ISNUMBER and first IF, casuses the formula to not work... Is it the length of the formula? Carl |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| revert formula insertion to old method | Setting up and Configuration of Excel | |||
| Help with macro formula and variable | Excel Worksheet Functions | |||
| Excel: Formula worked last week but now stoppped working. | Excel Worksheet Functions | |||
| Excel formula not working | Excel Discussion (Misc queries) | |||
| Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
Linear Mode
