Forum Discussion

Douglas997t's avatar
Douglas997t
Brass Contributor
Jul 22, 2022
Solved

Getting "FALSE" and cannot seem to correct

7/25/22 @ 6:00p: UPDATE...I figured it out so need for anyone to dive into this one any further. Thanks to all that made the attempt to help.

 

I have included a spreadsheet that contains a note on what I am trying to accomplish but am enclosing the notes contained in the sheet here in case the clarity is not up to par in the attached sheet.

 

Formula in HL8...

1). If HH8 ="",  HG8 will reflect $33,733 which is an accurate number for my purposes. HG8 should then be multiplied by HK8 (92%) = $31,034 with the sum will be reflected in HL8.

 

2). If HH8 >"", HG8 will reflect $40,220 which is an accurate number for my purposes. HG8 should then be multiplied by HK6 (92%) = $32,002 the sum will be reflected in HL8.

 

NOTES...HH8 contains a dropdown list that drives these calculations. In theory, if HH8 = "", HG8 makes a proper calc. If HH8 >"" the exact selection from the dropdown list drives a different calculation reflected in HI8. I have set it up so that if HG8>"" HI8 goes blank and vice versa.

 

In a perfect world when I select from HH8, "Default Assumptions" HG8 should calculate and HI8 should be blank but I get a "FALSE" in HL8 when it should generate another Calc.

 

I have even simplified the formula down to =IF(HG8>"",SUM(HG8*HK8), SUM(HI8*HK8)) and get a #Value error and just don't see why I am getting this error...

 

I have even broken out each formula, SUM(HG8*HK8)    (it calculates as intended) and

SUM(HI8*HK8)    (it calculates as intended) but when combined they freak out...???

 

I could use some help on this seemingly simple calculation and appreciate your help with this...

 

Thanks in advance!

 

Douglas

Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jul 23, 2022

    Douglas997t The formula in HL8 is like this:

    =IF(
    HF8="",
    " ",
    IF(
        AND(HF8>"",HG8>"",HI8=""),
        SUM(HG8*HK8),
        IF(
            AND(HF8>"",HG8="",HI8>""),
            IF(
                HG8="",
                SUM(HI8*HK8)
             )
         )
     )
)

    The last nested IF has no argument for [value_if_false]. So, when the IF evaluates to FALSE, Excel just returns FALSE as you didn't specify what to do otherwise.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jul 23, 2022

    Douglas997t The formula in HL8 is like this:

    =IF(
    HF8="",
    " ",
    IF(
        AND(HF8>"",HG8>"",HI8=""),
        SUM(HG8*HK8),
        IF(
            AND(HF8>"",HG8="",HI8>""),
            IF(
                HG8="",
                SUM(HI8*HK8)
             )
         )
     )
)

    The last nested IF has no argument for [value_if_false]. So, when the IF evaluates to FALSE, Excel just returns FALSE as you didn't specify what to do otherwise.

    • Douglas997t's avatar
      Douglas997t
      Brass Contributor
      Jul 23, 2022

      Hello Riny and thanks for reaching out. I tried adding the additional element that you suggested but it didn't seem to help See attached screen capture. It could be an error on my behalf on positioning. Something does seem wrong though. In an If/Then formula wouldn't that last or any element where the if this then that if then "xxx", the "xxx" being that part I left out, only be enacted if the prior , "if this then "that" element didn't solve as true? In this case the it seems like the criteria was met before getting to that element. Probably my mis-understanding of how that formula works but in the end adding it didn't change the outcome as the result was display of that newly inserted used-to-be missing element. Check my capture and let me know what you think...

       

      Thanks Riny

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Jul 23, 2022

        Douglas997t If you replace "TEST" by 0 (i.e. zero, without quotation marks) the other formulas will not give the #VALUE! errors. Now, these formulas try to calculate with a text, hence an error occurs.

         

        And to be honest, I don't really understand what you are trying to achieve with the schedule. It's incredibly complicated with all these nested IF formulas across 299 columns.

         

Resources