+ Reply to Thread
Results 1 to 6 of 6

Formula Broken Upon Save

  1. #1
    Registered User
    Join Date
    01-19-2015
    Location
    Cleveland, Ohio
    MS-Off Ver
    Mac, 2011
    Posts
    6

    Formula Broken Upon Save

    When I enter my IF formula it works. When I save and reopen it shows up as =#VALUE!

    Formula is below

    =IF(P6<.60,"F",IF(P6<.6549,"D",IF(P6<.6949,"D+",IF(P6<.7349,"C-",IF(P6<.7649,"C",IF(P6<.7949,"C+",IF(P6<.8349,"B-",IF(P6<.8649,"B",IF(P6<.8949,"A-",IF(P6<=1.00,"A"))))))))))
    Last edited by Allisongw; 01-20-2015 at 06:38 PM.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Formula Broken Upon Save

    Have you considered using a table instead?

    Just sort the values from highest to lowest with corresponding letters, and use LOOKUP or INDEX(MATCH.

    I stored scores D3:D12 and letters E3:E12.

    =INDEX($E$3:$E$12,MATCH(P6,$D$3:$D$12,-1))

    or

    =LOOKUP(2,1/($D$3:$D$12>=P6),$E$3:$E$12)


    I'd be wary of nesting more than 3 or 4 IFs into one another. At that point, I usually begin to think it's time for SUMPRODUCT or LOOKUP, or something that can handle multiple arrays.
    Attached Files Attached Files
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Formula Broken Upon Save

    What value is in P6 when you see the #VALUE! error?

    Does the formula look exactly the same when you reopen the file?

    We can look at your file if you attach it.

    See if this formula causes a problem. It produces the same results:

    =LOOKUP(P6,{0,0.6,0.6549,0.6949,0.7349,0.7649,0.7949,0.8349,0.8649,0.8949,1},{"F","D","D+","C-","C","C+","B-","B","A-","A"})
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Formula Broken Upon Save

    How are you saving your file....what format? I can see no reason in the formula itself why you would be getting the error after reopening the saved file unless there is something out of the ordinary about what you are doing when saving.

    An alternative to your formula could be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by newdoverman; 01-19-2015 at 09:19 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Registered User
    Join Date
    01-19-2015
    Location
    Cleveland, Ohio
    MS-Off Ver
    Mac, 2011
    Posts
    6

    Re: Formula Broken Upon Save

    I solved this, it apparently had to many IF arguments. Changed it and it now works! Thank you!

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Formula Broken Upon Save

    I looked up the specifications for Excel 2003 and the nested function limit is 7, in Excel 2010 and 2011 it is 64. Nested IF statements is 7 for all versions so far as I can find out.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel formula works when broken in two parts, but not when added together
    By nickmax1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-25-2014, 07:52 AM
  2. [SOLVED] Broken ROUUNDUP or VLOOKUP formula
    By macrorookie in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-05-2014, 08:49 AM
  3. Broken Formula
    By mtndewgradon in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-09-2013, 02:42 PM
  4. Redo and Undo broken after save
    By jez6363 in forum Excel General
    Replies: 5
    Last Post: 04-02-2013, 08:35 PM
  5. [SOLVED] Broken Count formula
    By Notters in forum Excel General
    Replies: 1
    Last Post: 08-21-2012, 09:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1