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

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

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

  1. #1
    New Member
    Join Date
    Apr 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking 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")))))))))))
    Last edited by nagendrababu; Apr 25th, 2012 at 05:17 AM.

  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default 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})
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Jan 2012
    Posts
    751
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    New Member
    Join Date
    Apr 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile 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
    Last edited by nagendrababu; Apr 25th, 2012 at 05:31 AM.

  5. #5
    New Member
    Join Date
    Apr 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking 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++})
    When I type this, its showing error. What can I do? Please help me.
    Regards,
    Nagendra Babu

  6. #6
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default 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++"})
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #7
    New Member
    Join Date
    Apr 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    New Member
    Join Date
    Mar 2013
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by VoG View Post
    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})

    Very Helpful... Can you please solve my problem too....???

    =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. #9
    Board Regular
    Join Date
    Jul 2009
    Location
    Woking UK
    Posts
    2,748
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Mike

    -----------------------------------------------
    Some solutions don't require an IF!

    When posting code wrap your code between [CODE] tags, eg. [ code ] your code [ /code] - no spaces .

    Paste your Excel data...

    MrExcel HTML Maker or Excel Jeanie

  10. #10
    New Member
    Join Date
    Mar 2013
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

      
    Quote Originally Posted by ukmikeb View Post
    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....

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com