levels of nesting help

**jamiemu** · 05-15-2012, 05:28 PM

Hello Everyone,

Would appreciate some help on this...

i have created a spreadsheet to determine how many miles i drive on a monthly basis and i wanted to create a formula to do this for me.
For example, when i first started i used the following information:

h-w (home to work) 4 Miles
h-gf (home to girlfriends) 8 Miles
h-f (home to football) 10 Miles
h-c (home to cinema) 17 Miles

...and i used the formula =IF(B11="h-w","4",IF(B11="h-gf","8",IF(B11="h-f","10",IF(B11="h-c","17"))))

meaning that if write "h-w" in B11 then C11 shows "4", if i put "h-gf" it would show "8", and so on...

This worked fine until i added more circumstances. Now i have 15, it comes up with an error message " The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format", i can only put in 7 before this error message comes up...

How can i do i increase the amount of levels of nesting?
Or is there a different formula that u can use?

Please help,

Thanks!
Jamie

**JieJenn** · 05-15-2012, 05:36 PM

Use choose. Easier to expand.

=CHOOSE(MATCH(B11,{"h-w","h-gf","h-f","h-c"},0),4,8,10,17)

**Palmetto** · 05-15-2012, 06:05 PM

If you expect to have a growing list of destinations and want to vary start and ending points (work to girlfriend versus home to girlfriend), then you should consider creating a lookup table.

If you're just tracking from home to whereever, then just use the input cell to show the destination only (i.e w, c, gf, etc) since the starting location is always the same.

=LOOKUP(B11,{"c","f","gf","w"},{17,10,8,4})

**ATLGator** · 05-15-2012, 06:08 PM

Try creating a matrix with the locations (H, W, GF, F, C along the row and column and fill in the numbers). Then you can reference this matrix everytime you want to calculate distance. Also, if you name the range, you will have an easier time adding locations than if you just use the range. If this doesn't make sense, post a file and I'll show you.

**Marcol** · 05-15-2012, 06:44 PM

As a matter of interest ...

i can only put in 7 before this error message comes up...

Your profile states you are using 2007, this version, and above, allows 64 nested IF()s, though why you would ever need that many I don't know.
Better to use the above suggestions.

In 2003 and earlier the limit is 7, so I guess you must be using an earlier version than 2007, if not then you are running in compatibility mode this will limit you to 2003 specifications.