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.

Attached Files

Point Scoring Parameters.xls (52.0 KB, 25 views)

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

Attached Files

Copy2 of Point Scoring Parameters.xls (52.5 KB, 19 views)

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

Attached Files

Copy2 of Point Scoring Parameters.xls (53.0 KB, 30 views)

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

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