Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make excell ignore non-whole numbers
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 -- John_Doe69 ------------------------------------------------------------------------ John_Doe69's Profile: http://www.excelforum.com/member.php...o&userid=33439 View this thread: http://www.excelforum.com/showthread...hreadid=532524 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make excell ignore non-whole numbers
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" wrote in message ... 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 -- John_Doe69 ------------------------------------------------------------------------ John_Doe69's Profile: http://www.excelforum.com/member.php...o&userid=33439 View this thread: http://www.excelforum.com/showthread...hreadid=532524 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make excell ignore non-whole numbers
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. :D 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: http://www.excelforum.com/member.php...o&userid=33439 View this thread: http://www.excelforum.com/showthread...hreadid=532524 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make excell ignore non-whole numbers
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(B20,B2+E1,IF(B2<-.99,B2+E1,E1)) Hope that helps. Celt -- Celt ------------------------------------------------------------------------ Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413 View this thread: http://www.excelforum.com/showthread...hreadid=532524 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make excell ignore non-whole numbers
=IF(B20,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 ------------------------------------------------------------------------ John_Doe69's Profile: http://www.excelforum.com/member.php...o&userid=33439 View this thread: http://www.excelforum.com/showthread...hreadid=532524 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to make excell ignore non-whole numbers
OK, I figured out what I was doing wrong. I changed the Formula to: =IF(B90,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. :D +-------------------------------------------------------------------+ |Filename: Photo5.gif | |Download: http://www.excelforum.com/attachment.php?postid=4630 | +-------------------------------------------------------------------+ -- John_Doe69 ------------------------------------------------------------------------ John_Doe69's Profile: http://www.excelforum.com/member.php...o&userid=33439 View this thread: http://www.excelforum.com/showthread...hreadid=532524 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i make a sound in XL whenever certain numbers are reach? | Excel Discussion (Misc queries) | |||
use "button" to make calculation ignore a cell | Excel Worksheet Functions | |||
How do I make a cell required to edit in Excell? | Excel Discussion (Misc queries) | |||
How to make Microsoft Outlook default e-mail when sending excell . | Excel Discussion (Misc queries) | |||
How do I make Excel stop rounding off my numbers that are 16 digi. | Excel Discussion (Misc queries) |