+ Reply to Thread
Results 1 to 8 of 8

Changing cell value produces "#DIV/0!" error message

  1. #1
    Registered User
    Join Date
    06-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Question Changing cell value produces "#DIV/0!" error message

    Can't work out why this error appears in a number of columns in my [refer attachment] spreadsheet.

    I am trying to have flexibility in Cell S23. However, whenever I type-in a value that is "not" 2.00% or 1.5%, this error occurs.

    Have tried two different formulas to test if either would make a difference: refers Cells J80 and J81 respectively. Neither works!


    A solution would be most welcome.
    Preceptor

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Changing cell value produces "#DIV/0!" error message

    The FALSE part of your IF statement in cell J80 (which is triggered by any value other than 2% in cell S23) is:

    SUM((J79+H80+I80)-N80)-4700)

    which is evaluating to #DIV/0!...
    since cell I80 is evaluating to #DIV/0!...
    since cell T24 is evaluating to #DIV/0!...
    since cell T22 is evaluating to #DIV/0!...

    Forgive me if I don't follow this chain any further, though this is precisely my recommendation to you in order to find the root cause of your problem.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    06-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Changing cell value produces "#DIV/0!" error message

    Thank you for your response and recommendation.

    The "chain" bit I follow... no show without Punch!

    I must admit that I can't spot anything wrong with the FALSE part as, when I read through it, is supposed to "do" what would be required if the Cell was "not" 2%.

    To add to my confusion, the amended formula in Cell J81 splits this into two distinct choices, i.e., 1.5% or 2%. In "theory", the formula should "do nothing" if a value other than the previous two were to be inputted in S23. In practice... no.

    Since this also has the same formula string as the specific one you have identified, it must be having the same effect.

    Presumably there is something there that I just can't see. Could have been looking at it for too long!

    Regards,
    Preceptor

  4. #4
    Registered User
    Join Date
    06-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Changing cell value produces "#DIV/0!" error message

    Reflected on that string and tried re-working the formula as:

    =IF($S$23>2%,SUM(((J79-4700)+H80+I80))-N80,SUM(J79+H80+I80)-N80)

    Seems to return the same answer!

    Regards,
    Preceptor

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Changing cell value produces "#DIV/0!" error message

    Sorry - I'm really not sure what you're trying to achieve in these cells. Cells J81-85 appear fine when S23 contains any value (in the sense that they return 0, not an error).

    Cell J80 is your problem, since the FALSE part is following the chain of #DIV/0!s that I outlined above - you didn't mention if you'd followed this to the root to determine its cause?

    Regards

  6. #6
    Registered User
    Join Date
    06-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Changing cell value produces "#DIV/0!" error message

    Quote Originally Posted by XOR LX View Post
    Sorry - I'm really not sure what you're trying to achieve in these cells. Cells J81-85 appear fine when S23 contains any value (in the sense that they return 0, not an error).

    Cell J80 is your problem, since the FALSE part is following the chain of #DIV/0!s that I outlined above - you didn't mention if you'd followed this to the root to determine its cause?

    Regards
    The current situation is that a student must make a decision between recommending Super Fund 1 (2.0% [Fund management Fee]) or Super Fund 2 (1.5% [Fund management Fee]). Being cleverer than me, some students have thought-up "Plan C... none of the above", aka: combine elements of "both" Funds. This means that the sheet (Cell) should actually be flexible enough to input a value "between" 1.5% and 2%. (I could then suggest to the "thinkers" that they come up with an "average" percentage to input into S23.)

    Thus, the amount in J80:J84 (and final Superannuation fund Balance) is dependent on the percentage in S23. If a student selects Fund 1, there is the deduction for Insurance Premiums; this is the "4700". Fund 2 has no insurances and therefore does not have a $4,700 annual deduction.

    If I separate each of the IF statements and test them individually, a 2% or 1.5% value in S23 respectively, each works like a charm. But that does not help if a student comes up with "that Plan C".

    I "thought" I was on to a good thing with formula #3 =IF($S$23>2%,SUM(((J79-4700)+H80+I80))-N80,SUM(J79+H80+I80)-N80) as it seemed to provide the flexibility I was seeking, i.e., any value "between" 1.5% and 2%, without the [potential?] need for a "bucket-load" of +IF statements. Unfortunately, it also does not work.

    You identified J80 as being the problem, but for the life of me, I can't "see" what you refer to as ..."the FALSE part is following the chain of #DIV/0!s". Thus, I have been unable to follow this to the root.

    Thanks,
    Preceptor

    p.s. I included the "0" in case there was nothing in S23... but didn't test it out!


    Regards,
    Preceptor

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Changing cell value produces "#DIV/0!" error message

    Ok,

    Then I'll extend the 'chain' which is leading to your error which I started previously to its root:

    The FALSE part of your IF statement in cell J80 (which is triggered by any value other than 2% in cell S23) is:

    SUM((J79+H80+I80)-N80)-4700)

    which is evaluating to #DIV/0!...
    since cell I80 is evaluating to #DIV/0!...
    since cell T24 is evaluating to #DIV/0!...
    since cell T22 is evaluating to #DIV/0!...
    since cell J85 is 0...
    since the formula in J85 is: =SUM((J84+H85+I85)+((J84+H85)*T29))-N85 and...
    J84,H85,I85,J84,H85,T29,N85 are all equal to zero.

    This last step then is your culprit. You need to analyse why these are all zero in these cases, whether they should be, and, if they shouldn't, what they should be. But currently, as they are all equal to zero, this makes J85 0, etc, etc. back up the chain all the way to J80.

    Regards

  8. #8
    Registered User
    Join Date
    06-04-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Changing cell value produces "#DIV/0!" error message

    Hi XOR LX,

    Thank you very much for your step-by-step deconstruction!

    I have printed this out and will spend time working my way through it so that it makes sense to me.

    Even though I really appreciated the time you have spent on my problem, I will not be in a position to respond to your analysis quickly. I have a number of full-days ahead of me (at least two days of which my wife has generously assigned against her "list of things for me to do".

    Cheers,
    Preceptor

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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