nesting functions within functions

[34laraider]

the excel help function says i can nest no more than seven functions within a function. is there a way around this?
thanks

[daddylonglegs]

There are ways round that limitation but normally there's a more efficient way too - e.g. if you're looking to nest 7 IFs you'll often be better off with some sort of LOOKUP function.

If you can describe in a little more detail what you want to do then it'll be easier to give a more specific answer.....

[34laraider]

This is what I need to do.
I work at a golf course, and we have a chart to convert a person's handicap index to their course handicap.
For example,
0 thru 0.4 -> 0
0.5 thru 1.3 -> 1
1.4 thru 2.1 -> 2
2.2 thru 3.0 -> 3
The chart carries on through 42 handicap
I want to enter an index (ex. 0.7) in one column, and have the function in an adjacent column give me the course handicap (ex. 1)

The function I was using looks like this
=IF(A1="","",IF(A1<=0.4,0,IF(AND(A1>0.4,A1<=1.3),1)

Thanks for any help

[mikerickson]

This kind of construct might help

=CHOOSE(MATCH(a1,{0,.5,1.4,2.1,3.1,,,}),0,1,2,3,...)

[daddylonglegs]

I'd suggest a simple LOOKUP formula.

If you create a table with the lower bound of each range in one column and the relevant course handicap in another you can use a very simple formula.

This setup allows you to easily change the table any time you want without having to change any formulas.

See attached example. Obviously you need to make the table larger and then alter the formula to reference the whole table

[daddylonglegs]

...sorry, I should have use some $ signs in my suggested formula to stop the table range changing when formula is copied down the page.

Formula in E2 should be

=LOOKUP(D2,A$2:B$9)

which can be copied down the column

[34laraider]

Thank you so much. That lookup function is exactly what I needed.

[34laraider]

what are the $'s for?

[daddylonglegs]

It just ensures that when the formula is "dragged down" the reference to the table doesn't change. You don't need them if you only want the formula in a single cell