software

I'm in the process of transitioning my work office from filing cabinets
full of paper and piles of ledger books to doing everything on the PC.
I am currently keeping records on paper to record the ACTUAL negatives
but not add them to the total. Once I can make Excell record the
negative fractions but not subtract them from the running total I can
stop using notebooks to keep records.

I need to keep a running account tally where all positive and partial
dollar amounts are added the the tally but only whole negative numbers
are subtracted (not fractions).

Example: starting balance is $1.00 and the next entry is -$0.90, the
new balance should still be $1.00

Photo 1 shows how my worksheet looks now and photo 2 is how it should
look.

[image: home.comcast.net/~johndoe_69/photo1.gif]
[image: home.comcast.net/~johndoe_69/photo2.gif]

Every time I think about how I keep records I picture Ebenezer
Scrooge's office with Quill pens and layers of dust everywhere.

Any help would be appreciated, thanks in advance, - John Doe 69--
John_Doe69
------------------------------------------------------------------------
John_Doe69's Profile: www.excelforum.com/member.php...oamp;userid=33439
View this thread: www.excelforum.com/showthread...hreadid=532524Maybe something like

=IF(D1lt;1,TRUNC(D1),D1)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Nothwest Excel Solutions
www.nwexcelsolutions.com
quot;It is a good thing to follow the first law of holes;
if you are in one stop digging.quot; Lord Healeyquot;John_Doe69quot; gt; wrote
in message ...
gt;
gt; I'm in the process of transitioning my work office from filing cabinets
gt; full of paper and piles of ledger books to doing everything on the PC.
gt; I am currently keeping records on paper to record the ACTUAL negatives
gt; but not add them to the total. Once I can make Excell record the
gt; negative fractions but not subtract them from the running total I can
gt; stop using notebooks to keep records.
gt;
gt; I need to keep a running account tally where all positive and partial
gt; dollar amounts are added the the tally but only whole negative numbers
gt; are subtracted (not fractions).
gt;
gt; Example: starting balance is $1.00 and the next entry is -$0.90, the
gt; new balance should still be $1.00
gt;
gt; Photo 1 shows how my worksheet looks now and photo 2 is how it should
gt; look.
gt;
gt; [image: home.comcast.net/~johndoe_69/photo1.gif]
gt; [image: home.comcast.net/~johndoe_69/photo2.gif]
gt;
gt; Every time I think about how I keep records I picture Ebenezer
gt; Scrooge's office with Quill pens and layers of dust everywhere.
gt;
gt; Any help would be appreciated, thanks in advance, - John Doe 69
gt;
gt;
gt; --
gt; John_Doe69
gt; ------------------------------------------------------------------------
gt; John_Doe69's Profile:
gt; www.excelforum.com/member.php...oamp;userid=33439
gt; View this thread: www.excelforum.com/showthread...hreadid=532524
gt;

Thanks for the help.

That seems to work to make it consider anything between -$0.01 and
-$0.99 to be $0.00, my only remaining problem is that anything from
$0.01 amp; up is not added to the total in column E.

In other words, any positive number in column B is added to the balance
in column E (one row up) but only negative numbers in column B that are
-$1.00 or more are added to the total in column E. Any time column B
-$0.99 or less than it is considered $0.00 and the previous balance of
column E is carried down to the current row.

In short -any- Overage is credited to the balance but only shortages of
$1 or -more- are charged back to the balance.

Does that make sense? It's confusing me as I type it.

Can I have two =IF(s) in a formula? As in: =IF it's more negative than
-$0.99 than subtract it from the Total and =IF it's a positive of any
amount then it is added to the Total.--
John_Doe69
------------------------------------------------------------------------
John_Doe69's Profile: www.excelforum.com/member.php...oamp;userid=33439
View this thread: www.excelforum.com/showthread...hreadid=532524
I can't see your spreadsheet pic, but this should do what you need.

enter this formula in column E (where your total is) and copy down

=IF(B2gt;0,B2 E1,IF(B2lt;-.99,B2 E1,E1))

Hope that helps.

Celt--
Celt
------------------------------------------------------------------------
Celt's Profile: www.excelforum.com/member.php...oamp;userid=19413
View this thread: www.excelforum.com/showthread...hreadid=532524
=IF(B2gt;0,B2 E1,IF(B2lt;-.99,B2 E1,E1)) works! Thanks a bunch! You don't
know how close this is to perfect. But,there is still a problem.
It doesn't reflect $ paid back in column D. I wish I had explained
what each column is for.

I removed one column,what was once in column D was an unnecessary
Balance column. I've posted the worksheet with the revisions and the
problem filled in with yellow.
image: Attached, note the Formula used to add the Paid-in amount.
You said you can't view the picture so I'll try to explain it as
clearly as I can...

Column A is DATE,
Column B is O/S (the =/- column we're making the =IF argument for)
Column C can be ignored,
Column D is $ paid back for shortages
Column E is the new Balance
The ROWs are one row for each shift worked, usually one a day,
sometimes in different locations during the day (that is what Column C
is for, Location)
(A)Date - (B)O/S - (C)ignored - (D)Paid Back - (E)New Balance

The problem (in yellow) is that money paid-in in Column D is only added
to the New Balance(E) when the O/S /- (B) is above 0 or below -$0.99.
Anything in (B) between $0 and -$0.99, the Paid-In (D) is ignored and
the New Bal (E) does not change.

Example:
If the Previous Balance is a $2.50 Credit and the new O/S is -$0.50 and
there is $1 paid in, the New Balance is still $2.50 instead of $3.50. -------------------------------------------------------------------
|Filename: Photo4.gif |
|Download: www.excelforum.com/attachment.php?postid=4629 |
-------------------------------------------------------------------

--
John_Doe69
------------------------------------------------------------------------
John_Doe69's Profile: www.excelforum.com/member.php...oamp;userid=33439
View this thread: www.excelforum.com/showthread...hreadid=532524
OK, I figured out what I was doing wrong.
I changed the Formula to: =IF(B9gt;0,B9 D9 E8,IF(B9lt;-0.99,B9 E8,E8) D9)
(See Attached Pic)
All cells seem to contain the correct info now.

Thanks much Peo Sjobom and Celt for all the help. I've been trying to
figure this out for a couple of months now and have been banging my
head on my desk in frustration. Now I can get rid of the Ledger Books
and do it all on the PC. -------------------------------------------------------------------
|Filename: Photo5.gif |
|Download: www.excelforum.com/attachment.php?postid=4630 |
-------------------------------------------------------------------

--
John_Doe69
------------------------------------------------------------------------
John_Doe69's Profile: www.excelforum.com/member.php...oamp;userid=33439
View this thread: www.excelforum.com/showthread...hreadid=532524