Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
I need to divide dollars and cents into two columns and then add the cents column into a total cents column and the dollar columns into a total dollar column which shows the total correct answer of dollars and cents.
With $1,289.99 using =int(<cell>) returns 1,289 and using =right(<cell>,2) returns 99. When adding the column of numbers, the dollar column is correct but the cents column returns "0" and the total value is the same as the total in the dollar column.
>> but the cents column returns "0" and the total value is the same as the total in the dollar column
You lost me here.
MIKE
0 
hmm yeah if I were you I'd use for the cents column
=( <cell>-int( <cell> ) )*100Using RIGHT will force the values to text, so you can't easily sum them.
0 0
Just for fun, how about a User Defined Function?
Paste this into a VBA module and use
=Cents(<cell>) in your spreadsheet.
Function Cents(ByVal Rng As Range) Cents = Right(Rng, 2) * 1 End Function
0
Thanks to all that helped. I now can separate the dollars and cents into two columns but still can not get them to add the way I need.
I'll use 1,289.99 and 1,345.78 as an example. When I add 1,289.99 and 1,345.78, I get a total of 177 in the cents column and 2,634 in the dollar column. What I need in the cents column is 77 and 2,635 in the dollar column.
0
For column A your regular total it is simply
=SUM(A1:A2)
For column B your Whole Dollar amounts
=INT(SUM(C1:C2))+SUM(B1:B2)
For column C your Cents only amounts
=A3-B3
MIKE
0
Mike
Thank you for your response; however, I don't understand it. I have only two columns, one for dollars and the other for cents.
0
Where do you enter the original amount, 1,289.99 and 1,345.78?
A B C
1,289.99 1,289.00 0.99
1,345.78 1,345.00 0.782,635.77 2,635.00 0.77
Can't get the formatting right.....
MIKE
0
Each number is entered in one cell on an input spreadsheet that is linked to the spreadsheet that has the two columns, one for dollars and the other for cents which need to be added.
0
Maybe it would help if you explained why you need to add these numbers after they are separated.
If the 1.00 from 1.77 needs to be added to the dollar column, why can't you add all the numbers up first and then separate out the cents?
1,289.99
1,345.78
------------
2,635.77=INT(SUM(<cells>) returns 2635
=RIGHT(SUM(<cells>,2)*1 returns 77Note: You could still put the numbers in separate columns for viewing, but you may not need to add the columns separately.
0
So you do not keep a total of the Original amount, it is only input then discarded. Correct?
The only two values we have to arrive at a Total with are the Whole Dollar and Remainder Cents figures.MIKE
0
Try this:
Your Whole numbers are in column B
Your Cent numbers are in column C
For your whole numbers:=SUM(B1:B2)+INT(SUM(C1:C2))
For you cents:
=MOD(SUM(C1:C2),1)
MIKE
0
Mike:
Your MOD formula will work only if the values in C are in the form of .xy
However, Brian said he has 177 and needs it to be 77, not .77
So assuming he has:
B C 1289 99 1345 78This might work:
=SUM(B1:B2)+(LEFT(SUM(C1:C2),LEN(SUM(C1:C2))-2)*1)
Returns 2635
=RIGHT(SUM(C1:C2),2)*1
Returns 77
0
I'm not sure it's important or not, although he did specify 77 without the decimal, he was talking about dollars and cents which normally are represented with the decimal.
So Brian which is it? With the decimal or with out or it's not important?
And as an afterthought you can use the MOD function on all of the Cents cells:
=MOD(<cell>,1)
where your Input cell = 1,289.99 will return just .99 (With the decimal point.)
MIKE
0
Curiosity more than anything,
but it's always nice to know it was your idea that worked. :-)MIKE
0
My joy was short-lived; the formulas do not work with zeros in the cents column.
A= original numbers
B = dollar column
C = cents column1,289.00 returns 1,289 and 89
1,345.02 returns 1,345 and 2
total returns 2,643 and 91for dollars:
=int(A1)
=int(A2)for cents:
=right(A1,2)*1
=right(A2,2)*1total for dollars:
=sum(B1:B2)+(left(sum(C1:C2))))total for cents:
=right(sum(C1:C2),2)
0
The reason the formulae did not work is because the Forum Gods punished you. Mike and I both asked you to let us know which solution you chose to go with and you never responded - that is until you had another problem. That'll teach ya! ;)
First off, this formula from your latest post will never work because you are missing the num_chars argument for the LEFT function.
=sum(B1:B2)+(left(sum(C1:C2))))
However, the formulae I offered, which included a num_chars argument, won't work if the length of the sum of cents column is less that 2 digits. My fault...I should have tested more cases than just your example. That'll teach me!
So, to separate out the Cents, this should handle .00.
=IF(INT(A1)-A1=0,0,RIGHT(A1,2)*1)
If there are no Cents, you'll get 0, if there are Cents, you'll get them.
Finally, to handle the instances where the SUM of the cells is less than 100, we can't use the original formula because we have to check and see if the length of SUM(Cents) is greater than 2 characters or not.
Try this to sum the Dollars plus the overflow from the Cents column:
=SUM(B1:B2)+(IF(LEN(SUM(C1:C2))>2,LEFT(SUM(C1:C2),LEN(SUM(C1:C2))-2)*1,0))
The best test is your own data, so let me know if I missed something else and I'll try again.
0
If your willing to keep the decimal points then the =MOD(A1,1) for cents works and everything totals.
Apparently you can't use a decimal with the =RIGHT function.
MIKE
0
Mike,
What did you mean by Apparently you can't use a decimal with the =RIGHT function?
I'm not agreeing or disagreeing...I'm just curious as to your thoughts behind that statement.
0
I tried several different ways to get =RIGHT to return the double zero after the decimal and all of them failed.
My best guess is that the =RIGHT function is reading the number 1,289.00 as 1,289, with no zeros.
I tried different formats but NaDa.MIKE
0
I'll agree that the RIGHT function by itself will not return a 00, but I played with this and it seemed to work...
You'll get 00 for 1289 and 1289.00, but still get 2 for 1289.02.
=IF(INT(A1)-A1=0,0,RIGHT(A1,2)*1)
Format the cell as Custom 0;;00
That said, I'm not sure why we want to display double 00.
0
I've always found it is best to Keep It Simple.
I'm not sure what you gain by
=IF(INT(A1)-A1=0,0,RIGHT(A1,2)*1)
to get 00 as opposed to
=MOD(SUM(C1:C2),1)
and getting 0.00
As I said before, if your dealing with Dollars and Cents I would probably prefer showing the decimal point. Reminds the viewer that it's pennies their looking at.
MIKE
0
Mike, my fellow formulator, I don't gain anything by using my formula to get 00.
I was simply responding to your comment that you couldn't get the RIGHT function to return a 00.
In a recent post you said I tried several different ways to get =RIGHT to return the double zero and I tried different formats but NaDa. Therefore I assumed you had a reason so I offered a way to do it. That's all, nothing more.
You know, we still haven't heard from the OP as to why he needs to separate the numbers and then sum them. I feel like we are trying to solve a secondary problem. Perhaps if we knew why he asked for these formulae we could come up with a totally different way to get him the results he is looking for.
0
>>I don't gain anything by using my formula to get 00.
Please excuse my wording. I did not mean you in the personal sense.>>Perhaps if we knew why he asked for these formulae we could come up with a totally different way to get him the results he is looking for.
Very true.
Perhaps we'll get an answer this time?Well Brian?????
MIKE
0
Sorry for any confusion. The original input spreadsheet had dollars and cents i.e., "1,234.56" in one cell This was linked to the original output spreadsheet which also had dollars and cents in one cell with a total given by using the SUM function.
The original input spreadsheet needs to remain the same but the revised output spreadsheet has divided dollars and cents into two cells, one for dollars and the other for cents with a combined total needed.
0
Let me try this again...in bold and italics and ALL CAPS (something I never do).
WHY?
WHY do you need the values split into 2 cells?
You've told us WHAT you need a number of times, but you've never explained WHY.
A couple of us have spent a considerable amount of time on your question...don't you think we deserve an answer as to WHY the dollars and cents need to be split before they are summed?
0
You know, my last post reads harsher than I meant it to be, especially the last paragraph. Sorry.
But I am still curious as to why...
0
The revised spreadsheet is actually a facsimile of an actual form that is needed. This form splits dollars and cents in two columns which is then summed.
0
Dear DerbyDad03,
I don't know what else to say. This is a project I'm trying to do for my son-in-law who is required to use certain forms that tally different expenditures. He needs to complete two different forms each month and he has received permission to use Excel facsmile forms in place of the original pre-printed ones that he completes by hand. The first facsmile form is used for input and places the data in specific locations on in. This form is linked to the second form. However, the first form needs to keep dollar and cents in one cell while the second form needs to divide the one dollar and cell cell from the first form into two separate cells, one for dollars and one for cents. These two columns then need to be added as if one did "regular' addition; the cents column gives the total for the numbers in the tens and ones place and the hundreds value, if any, is added to the total from the dollar column.
I would appreciate any further assistance.
Thank you
Brian
0
re: I would appreciate any further assistance
What further assistance do you need? Between Mike, Jon and myself, we have offered various solutions, some of which work if the "decimal" is left intact, and others that accomplish your goal without the decimal.
What else do you need?
0
Brian thanks for the explanation of what you’re doing. It's appreciated.
The central question I had, and you may not have the answer, is why do you have to split off the decimal point?
Does the actual form, made by hand, require that the cents be entered sans decimal point?
Is there a reason for this or is it just "the ways it's done"?Getting you an answer was easy, stripping off the decimal was a bit more complicated. :-)
MIKE
0
I first want to thank all that have helped me with this problem. I have tried all various combinations of dollara and cents and unfortunately numbers that end in a zero do not give the correct answer.
A1 = 1,234.20. This returns 1,234 in the dollar column (B1) and 00 in the cents column (C1)A2 = 1,234.70. This returns 1,234 in the dollar column (B2) and 01 in the cents column(C2)
The total returned is 2,468 in the dollar column and 01 in the cents column
Here are the formulas I used;
For A1 dollars: =int(A1)
For A1 cents: =if(int(A1)-A1=0,0,right(A1,2)*1For A2 dollars: = int(A2)
For A2 cents: =if(int(A2)-A2=0,0,right)A2,2)*1Total dollars: =sum(B1:B2)+(if(len(sum(C1:C2))>2,left(sum(C1:C2)),len(sum(C1:C2))-2*1,0))
Total cents: =right(sum(C1:C2),2)*1
Thank you.
0
Mike,
The actual form that is completed by hand, states that decimal points are not to be included.
Thanks,
Brian
0
With complicated formulas, you have to be very careful.
It is usually better to copy & paste until your sure how it works.You were close though:
Yours:
=if(int(A1)-A1=0,0,right(A1,2)*1You left off the last paren:
=if(int(A1)-A1=0,0,right(A1,2)*1)
Yours:
=if(int(A2)-A2=0,0,right)A2,2)*1You left of the last paren & you inverted the paren next to the word RIGHT.
=if(int(A2)-A2=0,0,right(A2,2)*1)
Yours:=sum(B1:B2)+ if(len(sum(C1:C2))>2,left(sum(C1:C2)),len(sum(C1:C2))-2*1,0))
You added a paren after left(sum(c1:c2)) and forgot the paren after len(sum(C1:C2))-2
=SUM(B1:B2)+(IF(LEN(SUM(C1:C2))>2,LEFT(SUM(C1:C2),LEN(SUM(C1:C2))-2)*1,0))
MIKE
0
Mike,
I made the corrections that you suggested but still get the wrong answer which I described in Response Number 37.
I tried rewriting =int(int(A1)-A1=0,0,right(A1,2)*1) as this is the formula that parses the combined dollar/cents into cents. When I changed "*1" to "*100", I get the correct number in the cents cell; however, this formula doesn't work correctly if the cents end in any other number than 0.
Thank you.
0
How this for the cents column?
=IF(INT(A1)-A1=0,0,IF(MOD(A1-INT(A1),10)<>0,MOD(A1-INT(A1),10)*100,RIGHT(A1,2)*1))
1236.00 returned 0
1236.01 returned 1
1236.10 returned 10
1235.11 returned 11
0
Thank you. This worked for the cents column; however, it affected the total dollar and total cents answers.
1,245.70 returned 1,245 in the dollar column and 70 in the cents column; and 1,234.20 returned 1,234 in the dollar column and 20 in the cents column; all of these are correct.
It returned 2,569 in the total dollar cell and 91 in the total cents cell.
0
You know, sometimes you end up so far down a rat hole that you can't see your way out. You try something, it doesn't work and you keep trying to make it work instead of stepping back and looking at the problem anew.
Try these:
To separate out the Dollars:
=INT(A1)
To separate out the Cents:
=RIGHT(A1*100,2)*1
To SUM the Dollars and include the overflow from the Cents:
=SUM(B1:B101)+(IF(LEN(SUM(C1:C101))>2,LEFT(SUM(C1:C101),LEN(SUM(C1:C101))-2)*1,0))
To SUM the Cents:
=RIGHT(SUM(C1:C101),2)*1
God, I hope those work!
0
Here's my best shot:
Column A
Format as Accounting, 2 Decimal places, $ symbol.
Column B
Format as Number, Zero Decimal places.
Column C
Format as Custom Format, Zero Zero.
In Column A, enter regular Dollar amounts.
$ 123.45
$1,234.50
$ 987.00
$ 987.60
$ 123.01In Column B, enter the formula
=INT(<cell>)
In Column C, enter the formula
=MOD(<cell>,1)*100
Your sheet should now look somthing like:$ 123.45 _____ 123 ___ 45
$1,234.50 ___ 1234 ___ 50
$ 987.00 _____ 987 ___ 00
$ 987.60 _____ 987 ___ 60
$ 123.01 _____ 123 ___ 01
For the Totals:In Column A enter the forumla
=SUM(A1:A5)
In Column B enter the formula=SUM(B1:B5)+INT(SUM(C1:C5)/100)
In Column C enter the formula=MOD(A7,1)*100
(NOTE: This is the same formula as the regular cell)
The only two columns that actually get summed are
Column A which has the original dollar amounts and
Column B, because we have to carry over the dollars from the cents column.In column C, instead of trying to sum then cut & paste, I just multiplied the remainders, calculated using =MOD(), by 100 to make them whole dollars and used the Custom Format to get rid of the pesky decimal points.
It worked for the amounts displayed.
My brain is now mush......
MIKE
0
This thread is hilarious...
Right, my two cents (ha!)
Amounts in general format are in cell A2-A5. Dollars are in column B, cents are in column C.
Dollars
=INT(A2)Cents
=VALUE(RIGHT(TEXT(A2,"0.00"),2))Total Dollars
=SUM(B2:B5)+INT(SUM(C2:C5)/100)Total Cents
=VALUE(RIGHT(TEXT(SUM(C2:C5)/100,"0.00"),2))this can be modified to stretch over any range. Will work with .00, .60, .55, anything. If you have 3-digit decimal places, this formula will round them *by line*, i.e. 1.005 + 1.005 will equal 1.02, rather than 1.01.
If I've missed anything, pass me the revolver...
0
Has anybody tried my suggestions from Response 45?
I tested 1234.00 through 1234.99 and they seem to work just fine.
0
DerbyDad03,
I started to test out the formulas you developed in Response 45; so far, they have all been correct in regard to the dollars total, the cents total, and the grand total. I am continuing to use other groups of numbers and will get back to you tomorrow with my results.
Thank you.
0
I would like to thank everyone, particularly DerbyDad03 and Mike, for their expertise and perservance in resolving this problem. I have tested the formulas in Response 45 and they worked correctly with all groups of numbers that I used.
0
Good. Now I can finally get some sleep.
Granted, the question of why these numbers have to summed after they are separated has never been fully explained.
I'm just glad this ordeal is finally over.
0
DerbyDad03,
The numbers need to be summed after they were separated because on the revised output spreadsheet, the numbers were for expeditures. The office that receives the revised output spreadsheet wants the dollar and cents broken into two columns because they believe this approach will lessen the probability for issuing inaccurate checks.
Thanks again for your help!
0
Let me see if I understand this:
We start with a column of numbers in a standard number format well known by Excel - a number with 2 decimal places.
We also know that Excel has built in functions to SUM these numbers directly.
We are now going to use (at least) 4 user created formulae to separate the numbers into 2 coulmns, SUM those columns, deal with overflow from one column to the next and then (I think) put the total back together so a check can be issued.
...and "they believe this approach will lessen the probability for issuing inaccurate checks"?
Wow!
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
