[shazydotcom]

Dear All,

I created an excel file with extension .xls and entered a forumla =if(... It had 11 levels and it successfully worked and I saved it. Next day when I have opened the file it gave the error #value. I entered the formula again but it refused to accept with the following message:

"The specified formula can not be entered because it uses more level of nesting than are allowed in current file format."

The formula is not accepting more than 9 levels while as per Excel limits, it can accept up to 64 levels. I tried to change the file format to .xltm but it also did not help.

Can any body tell me how to solve this issue?

regards,
shazy

[colbtech]

Post the spreadsheet. One of us may be able to assist

[shazydotcom]

Thanks for your reply. The file is attached herewith. You can see the table on Sheet3 Cell E6. If I try to add more "if" it does not accept. Same is the problem if I try to put a formula in cell E9. It does not accept more than 9 if.

[colbtech]

I have found websites that state you cannot nest this many if statements. As an alternative use VLOOKUP.

However the formulae appear to not require the if statements. Check the 2 Red figures and note how they are now calculated. This is far easier I think.

For the "marital status" calculation a revised formula might be...
=if(d8>2,5,0)

In other words, unless the appliciant is married 0 points

Any more problems feel free to post

Colin

[shazydotcom]

Thanks for your reply. But it is not solving my problem as if you see on Sheet2 there are points for each selection of a factor. Like for income when I have to select 13401-15000 it should return 15 points and when it goes to next bracket i.e. 15001-30000 it should return 18 points. It is not simply adding 1 point to next bracket. I request you to see Sheet2 to see how points are attached with each selection of factors. Please ignore row 10,11 of Sheet2.

Thanks in advance.

[colbtech]

From the Microsoft website:
http://office.microsoft.com/en-gb/ex...CH062528271033

My apologies, I misread the income entry. I have amended the spreadsheet. It now gives the correct values.

Hope this helps

[shazydotcom]

Thank you very much for this good answer. Logical modifications helped to work the formula.

Best regards...

[JBeaucaire]

If you want to attain value by value control of the result without having to nest IFs at all, you can use a simple LOOKUP table.

You can create a table on your sheet somewhere and refer to it, making it easy to change your tiers and values. Or, you can build it right into the formula, like this... in E6 for your sheet:

=LOOKUP(D6, {0, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11}, {0, 7, 8, 9, 10, 11, 12, 13, 14, 15, 18})

Or in E10:
=LOOKUP(D10, {0, 1, 2}, {0, 7, 8})

[shazydotcom]

Thanks JBeaucaire. I have tested formula given by you and it also worked perfectly. But in your formula I want to ask one thing that is it only for numeric expressions? Because when I tried to enter values in result vector it did not work like

=LOOKUP(D6,({0,1,2},{a,b,c})

If I try to make above formula, it will not be accepted. It works only if I keep only numbers in result vector.

What is your opinion?

[colbtech]

Thanks JBeaucaire. I have tested formula given by you and it also worked perfectly. But in your formula I want to ask one thing that is it only for numeric expressions? because when i tried to enter values in result vector it did not work like

=LOOKUP(D6,({0,1,2},{a,b,c})

If i try to make above formula, it will not be accepted. it works only if i keep only numbers in result vector.

What is your opinion?

Put quote marks around the letters. e.g. =LOOKUP(D6,({0,1,2},{"a","b","c"})

[JBeaucaire]

Remember, I mentioned you could put together a "chart" on your sheet somewhere, or on another sheet, and use the ranges of cells in place of each of the arrays. This would eliminate the text/numeric issue, too.

    A   B      C   D
1   0   0      0   a
2   2   7      1   b
3   3   8      2   c
4   4   9
5   5   10
6   6   11
7   7   12
8   8   13
9   9   14
10  10  15
11  11  18

=LOOKUP(D6, {0, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11}, {0, 7, 8, 9, 10, 11, 12, 13, 14, 15, 18})
... becomes:
=LOOKUP(D6, Sheet2!A1:A11, Sheet2!B1:B11)
... or:
=VLOOKUP(D6, Sheet2!A1:B11, 2, TRUE)



=LOOKUP(D10, {0, 1, 2}, {0, 7, 8})
... becomes:
=LOOKUP(D10, Sheet2!C1:C3, Sheet2!D1:D3)
... or:
=VLOOKUP(D10, Sheet2!C1:D3, 2, TRUE)

NOTE: Using lookup "Charts" like this means it is very easy to change your values and all the formulas using the charts simply update immediately. That could also be a bad thing. Your call.

[shazydotcom]

Thanks colbtech... that is working perfectly...

Best regards

[shazydotcom]

JB I found your post after Colbtech, hence, checked now and replying.. it was an excellent solution given by you... I am happy that I am at good place to have you guys for your kind help... thank you very much...