How to make excell ignore non-whole numbers

[John_Doe69]

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: http://home.comcast.net/~johndoe_69/photo1.gif]
[image: http://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

 

[Peo Sjobom]

Maybe something like

=IF(D1<1,TRUNC(D1),D1)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Nothwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[John_Doe69]

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 & 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.

 

[Celt]

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(B2>0,B2+E1,IF(B2<-.99,B2+E1,E1))

Hope that helps.

Cel

 

[John_Doe69]

=IF(B2>0,B2+E1,IF(B2<-.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: http://www.excelforum.com/attachment.php?postid=4629 |
+-------------------------------------------------------------------+

 

[John_Doe69]

OK, I figured out what I was doing wrong.
I changed the Formula to: =IF(B9>0,B9+D9+E8,IF(B9<-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: http://www.excelforum.com/attachment.php?postid=4630 |
+-------------------------------------------------------------------+