# the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format

This is a discussion on the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format within the Excel Questions forums, part of the Question Forums category; When I type this " IF Formula", the specified formula cannot be entered because it uses more levels of nesting ...

1. ## the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format

When I type this "IF Formula", the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format:
Please help me to solve this problem or is there any other way to get the same results in excel
=IF(AND(E2<50),"0",IF(AND(E2>=50,E2<=54),"5.5",IF(AND(E2>=55,E2<=59),"6.0",IF(AND(E2>=60,E2<=64),"6.5",IF(AND(E2>=65,E2<=69),"7",IF(AND(E2>=70,E2<=74),"7.5",IF(AND(E2>=75,E2<=79),"8.0",IF(AND(E2>=80,E2<=84),"8.5",IF(AND(E2>=85,E2<=89),"9",IF(AND(E2>=90,E2<=94),"9.5",IF(AND(E2>=95,E2<=100),"10")))))))))))

2. ## Re: the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format

Try

=LOOKUP(E2,{0,50,55,60,65,70,75,80,85,90,95},{0,5.5,6,6.5,7,7.5,8,8.5,9,9.5,10})

3. ## Re: the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format

This should work for you

=IF(E2<50,0,5+(INT((E2-49)/5)+1)*0.5)

4. ## Re: the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format

VoG Sir,,

U R great.. You solved my problem within a second. thank you very much. Hope It works properly.

I will try the suggestion given by the bardd also. Thank you very much to you also sir.

Best Regards,
Nagendra Babu

5. ## Re: the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format

Dear Mr. VoG

Suggestion given by you worked clearly with the data I provided. But when I am trying to apply the same for slightly modified data, it's showing error.

Kindly help me in solving this.

The problem is as follows:

=LOOKUP(E2,{0,50,55,60,65,70,75,80,85,90,95},{F,C,C+,B,B+,A,A+,A++,S,S+,S++})
Regards,
Nagendra Babu

6. ## Re: the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format

You need to add quotation marks

=LOOKUP(E2,{0,50,55,60,65,70,75,80,85,90,95},{"F","C","C+","B","B+","A","A+","A++","S","S+","S++"})

7. ## Re: the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format

Dear Sir,

S. Your suggestions helped me a lot. Thank you very much. I learned a new thing today from you.

Once again thank you very much.

See you again

Regards,
Nagendra Babu

8. ## Re: the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format

Originally Posted by VoG
Try

=LOOKUP(E2,{0,50,55,60,65,70,75,80,85,90,95},{0,5.5,6,6.5,7,7.5,8,8.5,9,9.5,10})

=IF(\$C\$3=A2,C8,IF(\$C\$3=A3,C18,IF(\$C\$3=A4,C28,IF(\$C\$3=A5,\$C\$38,IF(\$C\$3=A6,C48,IF(\$C\$3=A7,C58,IF(\$C\$3=A8,C68,IF(\$C\$3=A9,C7 8,IF(\$C\$3=A10,C88,IF(\$C\$3=A11,C98,IF(\$C\$3=A12,C108,IF(\$C\$3=A13,C118,IF(\$C\$3=A14,C128,IF(\$C\$3=A15,C138,IF(\$C\$3=A16,C148,I F(\$C\$3=A17,C158,IF(\$C\$3=A18,C168,IF(\$C\$3=A19,C178,IF(\$C\$3=A20,C188,IF(\$C\$3=A21,C198,IF(\$C\$3=A22,C208,IF(\$C\$3=A23,C218,IF (\$C\$3=A24,C228,IF(\$C\$3=A25,C238,IF(\$C\$3=A26,C248,IF(\$C\$3=A27,C258,IF(\$C\$3=A28,C267,IF(\$C\$3=A29,C277,IF(\$C\$3=A30,C287,IF( \$C\$3=A31,C297,IF(\$C\$3=A32,\$C\$307,IF(\$C\$3=A33,\$C\$317,IF(\$C\$3=A34,\$C\$327,IF(\$C\$3=A35,\$C\$337,IF(\$C\$3=A36,\$C\$347,IF(\$C\$3=A37 ,\$C\$357,"Not Listed" ))))))))))))))))))))))))))))))))))))

9. ## Re: the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format

Hi

Welcome to the Forum.

As you are a newcomer you might like to know that hi-jacking threads is not the done thing and you should in future start a new thread however similar your problem might be.

Try this :-
Code:
`=IFERROR(INDEX(\$C\$8:\$C\$258,(MATCH(\$C\$3,\$A\$2:\$A\$27,0)-1)*10+1),IFERROR(INDEX(\$C\$267:\$C\$357,(MATCH(\$C\$3,\$A\$28:\$A\$37,0)-1)*10+1),"Not Listed"))`

This formula is for Excel 2007 and later.

hth

10. ## Re: the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format

Originally Posted by ukmikeb
Hi

As you are a newcomer you might like to know that hi-jacking threads is not the done thing and you should in future start a new thread however similar your problem might be.

hth
Sorry My Friend... I will take care of this thing in future..... and thanks heaps for the help....

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•