=#VALUE! Error

**DavidStewardUK** · 02-22-2013, 01:31 PM

When I type in the following nested IF I get a =#VALUE! Error after the spreadsheet has been saved and reloaded. The contents of the cell are as I expected but the formula has gone and been replaced by =#VALUE!.

The formula is
=IF(B4=21,"£21 Subscription",IF(B4=42,"£42 Subscription",IF(B4=44.5,"£42 + £2.50 joining fee",IF(B4=23.5,"£21 Subscription + £2.50 joining fee",IF(B4=26.5,"£24 Subscription + £2.50 joining fee",IF(B4=14.5,"£12 Subscription + £2.50 joining fee",IF(B4=8.5,"£6 Subscription + £2.50 joining fee",IF(B4=26,"£23.50 Subscription + £2.50 joining fee",IF(B4=52,"£49.50 Subscription + £2.50 joining fee","Check this")))))))))

If I remove two of the nested IF statement if works as can be saved and reloaded. Have I reached a maximum length or depth for the IF statement?

Is there a better way of doing this? I test the contents of B4 for 7 valid responses, if they are not 21 or 42 then I need to split off the 2.50 joining fee. If not one of the 7 valid resp
onses then I need an error prompt

Thanks
David

**K m** · 02-22-2013, 01:37 PM

Prior to Excel 2007, Excel formulas were limited to seven levels of nesting. In Excel 2007 and 2010, the maximum number of levels of nesting that a formula can have has been raised to 64. A related issue is that in Excel 2003 and earlier, formulas were limited to 255 characters; they are now limited to 1,023 characters in Excel 2007.

I put the formula in Excel 2010 and had no errors

**francelita** · 02-22-2013, 01:39 PM

I need to create a drop down menu that also allow me to enter some just a couple words and it has to narrow down the options.
Please I need some help with that

**K m** · 02-22-2013, 01:41 PM

Francelita do not hijack someone else's thread. Start you own thread/question

**DavidStewardUK** · 02-22-2013, 01:45 PM

Thanks for pointing me in the right direction. I am using Excel Mac 2010 but need to save it as a .xls version to pass on to a user who has an older version, hence the limitation comes into play. That explains why is works until it is saved and reloaded.

Thanks again.

David

**arlu1201** · 02-22-2013, 02:06 PM

Thanks K m.

Francelita,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.