Brian Entz

Q: Once a certain number is reached in tally -drop to zero

I have a column with each row successively larger as each row contains the tally of the row above it plus a new figure. HOWEVER, once the tally comes to a certain number (found in a separate cell) I want all further rows to drop to zero. The tally maxes out once it reaches the figure in the separate cell and goes zero or blank thereafter.

Posted on Sep 5, 2016 12:07 AM

Close

Q: Once a certain number is reached in tally -drop to zero

  • All replies
  • Helpful answers

  • by Yellowbox,Apple recommended

    Yellowbox Yellowbox Sep 5, 2016 6:15 AM in response to Brian Entz
    Level 6 (10,430 points)
    Mac OS X
    Sep 5, 2016 6:15 AM in response to Brian Entz

    Hi Brian,

    Screen Shot 2016-09-05 at 11.09.16 PM.png

    The Threshold value is in a separate table, but it could be anywhere in the document.

    Formula in C2 of Table 1 (and Fill Down)

    =IF(B2<Threshold::A$1,B2,0)

    If a Raw Tally (Column B) is less than the Threshold value, then Raw Tally, else zero.

    You can hide Column B.

     

    Regards,

    Ian.

  • by Brian Entz,

    Brian Entz Brian Entz Sep 7, 2016 1:03 AM in response to Brian Entz
    Level 1 (5 points)
    Mac OS X
    Sep 7, 2016 1:03 AM in response to Brian Entz

    Here is the actual chart. In row B4 we see the tally from monthly rent of $897 multiplied by A4 of 24 for a total of $21,527.93. But when we pay $897 C1 multiplied by 60 at A7 that finally pays off the full amount owed of $53,819.82. So in the row below we want B2 to actually come in at zero instead of $64,583.79 because it is over the payoff of C2 $53,819.82.  Does that make sense?

     

    Monthly Payment

    $897.00

     

    Full Payoff

    $53819.82

    Mo.

    Total Cost

    Payoff

    24

    $21,527.93

    $30,844.56

    36

    $32,291.89

    $20,869.51

    48

    $43,055.86

    $10,591.07

    60

    $53,819.82

    -$0.00

    72

    $64,583.79

    -$0.00

    84

    $75,347.75

    -$0.00

    96

    $86,111.72

    -$0.00

    108

    $96,875.68

    -$0.00

    120

    $107,639.64

    -$0.00

  • by Yellowbox,

    Yellowbox Yellowbox Sep 7, 2016 3:10 AM in response to Brian Entz
    Level 6 (10,430 points)
    Mac OS X
    Sep 7, 2016 3:10 AM in response to Brian Entz

    Hi Brian,

    What is the formula in B4?

    Forgetting your original question for now,

    Using this formula in B4 (and Filled Down), =C$1×A4 I get $21,528.00

    Screen Shot 2016-09-07 at 7.54.34 PM.png

    And in C4 with this formula =C$2−B4 I get $32,291.82

    Screen Shot 2016-09-07 at 7.58.10 PM.png

    By Row 7 (60 months) the payments exceed the Full Payoff by $0.18

    Am I missing something?

     

    Regards,

    Ian.

  • by Brian Entz,

    Brian Entz Brian Entz Sep 7, 2016 9:47 AM in response to Brian Entz
    Level 1 (5 points)
    Mac OS X
    Sep 7, 2016 9:47 AM in response to Brian Entz

    Screen Shot 2016-09-07 at 9.36.31 AM.png

    The formula for B4 is Monthly payment of $897 (C1) times number of months (A4) or 24. Total of B4. And B5 is $897 times 36 months and so forth. At the end of 60 months balance is paid off. So I want B8 (the 72 month measure) to read zero because the balance is zero. I'm thinking there must be some way of writing the formula under Total Cost that will zero down when C2 (Full Payoff) is reached?