how to subtract whole #s but not fractions..

G

Guest

I'm trying to keep a over/shortage cash tally where all positive figures are
added but only whole negative numbers are subtracted (0.99 and under are not
subtracted) from the running tally.
Example: This is how it looks now
DATE O/S Where BAL PAID BALANCE
4/1 \$0.00 g \$0.00 \$0.00
4/2 (\$0.50) g (\$0.50) (\$0.50)
4/3 \$0.00 g (\$0.50) (\$0.50)
4/4 (\$0.30) g (\$0.80) (\$0.80)
4/5 (\$0.91) g (\$1.71) (\$1.71)
4/6 \$6.35 c \$4.64 \$4.64
4/8 (\$21.51) g (\$16.87) \$10.00 (\$6.87)
4/8 (\$0.10) c (\$6.97) (\$6.97)
4/9 \$5.10 g (\$1.87) (\$1.87)
4/10 \$0.09 g (\$1.78) (\$1.78)
4/11 (\$3.65) c (\$5.43) \$3.00 (\$2.43)

This is how it should auto-calculate:
DATE O/S Where BAL PAID BALANCE
4/1 \$0.00 g \$0.00 \$0.00
4/2 (\$0.50) g \$0.00 \$0.00
4/3 \$0.00 g \$0.00 \$0.00
4/4 (\$0.30) g \$0.00 \$0.00
4/5 (\$0.91) g \$0.00 \$0.00
4/6 \$6.35 c \$6.35 \$6.35
4/8 (\$21.51) g (\$15.16) \$10.00 (\$5.16)
4/8 (\$0.10) c (\$5.16) (\$5.16)
4/9 \$5.10 g (\$0.06) (\$0.06)
4/10 \$0.09 g (\$0.03) (\$0.03)
4/11 (\$3.65) c (\$3.62) \$3.00 (\$0.62)

I tried to come up with an "IF" type formula but nothing I tried would work.

Does anybody know how to do this type of formula?

G

Guest

Sorry that got all unaligned, maybe this'll look neater:

I'm trying to keep a over/shortage cash tally where all positive figures are
added but only whole negative numbers are subtracted (0.99 and under are not
subtracted) from the running tally.
Example: This is how it looks now
DATE O/S BAL PAID BALANCE
4/1 (\$0.10) (\$0.10) (\$0.10)
4/2 (\$0.50) (\$0.60) (\$0.60)
4/3 \$0.75 \$0.15 \$0.15
4/4 \$0.90 \$1.05 \$1.05
4/5 \$0.00 \$1.05 \$1.05
4/6 \$6.35 \$7.40 \$7.40
4/8 (\$21.51) (\$14.11) \$10.00 (\$4.11)
4/8 \$0.00 (\$4.11) (\$4.11)
4/9 (\$5.10) (\$9.21) (\$9.21)
4/10 \$0.00 (\$9.21) (\$9.21)
4/11 \$4.00 (\$5.21) \$5.00 (\$0.21)

This is how it should auto-calculate:
DATE O/S BAL PAID BALANCE
4/1 (\$0.10) \$0.00 \$0.00
4/2 (\$0.50) \$0.00 \$0.00
4/3 \$0.75 \$0.75 \$0.75
4/4 \$0.90 \$1.65 \$1.65
4/5 \$0.00 \$1.65 \$1.65
4/6 \$6.35 \$8.00 \$8.00
4/8 (\$21.51) (\$13.51) \$10.00 (\$3.51)
4/8 \$0.00 (\$3.51) (\$3.51)
4/9 (\$5.10) (\$8.61) (\$8.61)
4/10 \$0.00 (\$8.61) (\$8.61)
4/11 \$4.00 (\$4.61) \$5.00 \$0.39

I tried to come up with an "IF" type formula but nothing I tried would work.

Does anybody know how to do this type of formula?

G

wrong:

right:

G

Guest

Hi,

If I understood correctly, shouldn't the results be like this. Where we have
-\$21.51, don't we just subtract 21?

If this is correct, then formula starting in row 3 of first balance is:

=IF(B3>0,C2+B3,TRUNC(B3)+E2)

Date O/S Bal PAID BAL
04/01/2006 -\$0.10 \$0.00 \$0.00
04/02/2006 -\$0.50 \$0.00 \$0.00
04/03/2006 \$0.75 \$0.75 \$0.75
04/04/2006 \$0.90 \$1.65 \$1.65
04/05/2006 \$0.00 \$1.65 \$1.65
04/06/2006 \$6.35 \$8.00 \$8.00
04/07/2006 -\$21.51 -\$13.00 \$10.00 -\$3.00
04/08/2006 \$0.00 -\$3.00 -\$3.00
04/09/2006 -\$5.10 -\$8.00 -\$8.00
04/10/2006 \$0.00 -\$8.00 -\$8.00
04/11/2006 \$4.00 -\$4.00 \$5.00 \$1.00

G

Guest

One way:

col = Application.CountIf(Range("1:1"), "Actual") + 1
Set rng = Range(Cells(3, "B"), Cells(123, col))

HTH

G

Guest

Sorry .... wrong posting!!!

Toppers said:
One way:

col = Application.CountIf(Range("1:1"), "Actual") + 1
Set rng = Range(Cells(3, "B"), Cells(123, col))