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

Hi Brian,

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.

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

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

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

Am I missing something?

Regards,

Ian.

Brian wrote:

The formula for B4 is Monthly payment of $897 (C1) times number of months (A4) or 24

Hi Brian,

With respect, something seems amiss with your table.

$897.00 times 24 equals $21,528.00 (my table), not $21,527.93 (your table).

Have you used Format Panel > Cell > Data Format to display only two decimal places?

Here is Currency format with Decimals Auto

Here is Currency format with Decimals 2

The display is $897.00 but the value (used in formulas) is still $896.997083

Regards,

Ian.

One of those Aha! moments.

I am thinking that the number displayed in C1 is the result of a formula. Changing the display does not change the value. If you want to round, you need the ROUND function.

Please reply with what you want to do with the Monthly Payments value. To round or not to round. Then we can find a solution to your original question:

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.

Regards,

Ian.