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

       

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

        Check the capture attached for the results of the change to 0.

         

        The myriad nested if/thens and all other columns across this admittedly wide sheet are all there for a reason. They are partially to breakdown complex and inter-related raw computations of commercial real estate investments and partially their function is to avoid massive #value and other unsightly mishaps that occur when a value in a particular formula is missing, because it hasn't yet been entered somewhere upstream of that formula, and is awaiting its entry this clearing the #value etc.. More cosmetic you might say. I have no doubt there is a far more elegant method of accomplishing the same result, maybe a better result but I am an asset and portfolio manager for a small private equity group and don't need to spend a bunch of time learning the newest greatest ways to accomplish such things. I generally know how to get to most solutions I need aside from a couple of recent blocks where I just became endlessly stuck, such as this time.

         

        Douglas

         

         

Resources