Forum Discussion

Jmayargentic's avatar
Jmayargentic
Copper Contributor
Sep 15, 2021
Solved

Top Down Bottom up formula

I have a debt stack with principal payments and principal losses that intersect over time.  I am having trouble getting a calc to work through the intersect and was hoping for some help on the formula.  

 

=IF(SUM(BG$4:BG13)-BG$3<0,0,IF(SUM(BG$4:BG13)-BG$3>BG13,IF(BG13-BG$1<0,0,BG13-BG$1),IF(BG$1=0,0,SUM(BG13:BG$17))-BG$1-BG$3+SUM(BG$4:BG12)))

 

Here is what I have so far but I am sure there is a better way.

 

Any help is appreciated.  example attached

Formulas and Functions
  • Yea_So's avatar
    Yea_So
    Sep 27, 2021

    Jmayargentic 

    Your original Formula error in red:

    =IF(SUM(BG$4:BG13)-BG$3<0,0,IF(SUM(BG$4:BG13)-BG$3>BG13,IF(BG13-BG$1<0,0,BG13-BG$1),IF(BG$1=0,0,SUM(BG13:BG$17))-BG$1-BG$3+SUM(BG$4:BG12)))

     

    I followed it where it wanted to go:

    So I entered the formula:

    =IF(SUM(BG$4:BG13)-BG$3<0,1,2)

    it picked 2.

    then I added the next 2nd question in the formula:

    =IF(SUM(BG$4:BG13)-BG$3<0,
    0,
    IF(SUM(BG$4:BG13)-BG$3>BG13,1,2))

    it picked 2.

    then I added the next 3rth question in the formula:

    =IF(SUM(BG$4:BG13)-BG$3<0,
          0,
          IF(SUM(BG$4:BG13)-BG$3>BG13,
              1,
              IF(BG$1=0,
                  0,
                  SUM(BG13:BG$17)-BG$1-BG$3+SUM(BG$4:BG12)
              )
          )
    )

    It picked 1=0

     

    The red #1 is the next 3rd question in the formula is this one:

    IF(
        BG13-BG$1<0,
        0,
        BG13-BG$1
    ),

    In reality, since the 3rd and 4th question are the same level, I would call them 2-a, and 2-b

    Meaning this:

     

     

    you should build your nested if statements like: IF(a=b,1,2) and go on from there building what 1 is and what 2 is so you won't get confused with the nesting and the answer the if statement returns gives you a good idea on which question you're going to ask on either 1 or 2.

     

    cheers

     

3 Replies

  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    Sep 27, 2021

    Jmayargentic 

    Your original Formula error in red:

    =IF(SUM(BG$4:BG13)-BG$3<0,0,IF(SUM(BG$4:BG13)-BG$3>BG13,IF(BG13-BG$1<0,0,BG13-BG$1),IF(BG$1=0,0,SUM(BG13:BG$17))-BG$1-BG$3+SUM(BG$4:BG12)))

     

    I followed it where it wanted to go:

    So I entered the formula:

    =IF(SUM(BG$4:BG13)-BG$3<0,1,2)

    it picked 2.

    then I added the next 2nd question in the formula:

    =IF(SUM(BG$4:BG13)-BG$3<0,
    0,
    IF(SUM(BG$4:BG13)-BG$3>BG13,1,2))

    it picked 2.

    then I added the next 3rth question in the formula:

    =IF(SUM(BG$4:BG13)-BG$3<0,
          0,
          IF(SUM(BG$4:BG13)-BG$3>BG13,
              1,
              IF(BG$1=0,
                  0,
                  SUM(BG13:BG$17)-BG$1-BG$3+SUM(BG$4:BG12)
              )
          )
    )

    It picked 1=0

     

    The red #1 is the next 3rd question in the formula is this one:

    IF(
        BG13-BG$1<0,
        0,
        BG13-BG$1
    ),

    In reality, since the 3rd and 4th question are the same level, I would call them 2-a, and 2-b

    Meaning this:

     

     

    you should build your nested if statements like: IF(a=b,1,2) and go on from there building what 1 is and what 2 is so you won't get confused with the nesting and the answer the if statement returns gives you a good idea on which question you're going to ask on either 1 or 2.

     

    cheers

     

Resources