how to subtract whole #s but not fractions..

J

John Doe

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?
 
J

John Doe

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?
 
J

John Doe

wrong:
photo1.gif


right:
photo2
 
T

Toppers

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
 
T

Toppers

One way:

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

HTH
 
T

Toppers

Sorry .... wrong posting!!!

Toppers said:
One way:

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

HTH
Click to expand...
 
Top