Results 1 to 10 of 10
Like Tree1Likes
  • 1 Post By ukmikeb

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. #1
    New Member
    Join Date
    Apr 2012
    Posts
    4

    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 04:17 AM.

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,867

    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
    510

    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

    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 04:31 AM.

  5. #5
    New Member
    Join Date
    Apr 2012
    Posts
    4

    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
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,867

    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

    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
    8

    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
    1,866

    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
    umer_what likes this.
    Mike

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

    When posting code wrap your code between [CODE] tags.

    Paste your Excel data...

    MrExcel HTML Maker or Excel Jeanie

  10. #10
    New Member
    Join Date
    Mar 2013
    Posts
    8

    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....

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