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