Dividing $/cents into 2 columns

Dell / INSPIRION
March 4, 2009 at 10:55:53
Specs: Windows XP, 1.0 Gb
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.


See More: Dividing $/cents into 2 columns

Report •


#1
March 4, 2009 at 11:04:08
>> 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


Report •

#2
March 4, 2009 at 11:12:00
hmm yeah if I were you I'd use for the cents column

=( <cell>-int( <cell> ) )*100

Using RIGHT will force the values to text, so you can't easily sum them.


Report •

#3
March 4, 2009 at 11:21:35
You can also just subtract the =int(a1) value from a1

Col A Col B Col C
175.60 175.00 =a1-b1

MIKE


Report •

Related Solutions

#4
March 4, 2009 at 11:26:41
or...

=RIGHT(<cell>,2)*1


Report •

#5
March 4, 2009 at 12:42:43
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


Report •

#6
March 5, 2009 at 05:14:52
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.


Report •

#7
March 5, 2009 at 05:38:25
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


Report •

#8
March 5, 2009 at 05:57:40
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.


Report •

#9
March 5, 2009 at 06:04:48
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.78

2,635.77 2,635.00 0.77

Can't get the formatting right.....
MIKE


Report •

#10
March 5, 2009 at 06:14:55
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.

Report •

#11
March 5, 2009 at 06:30:32
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 77

Note: You could still put the numbers in separate columns for viewing, but you may not need to add the columns separately.


Report •

#12
March 5, 2009 at 06:31:53
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


Report •

#13
March 5, 2009 at 07:03:18
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


Report •

#14
March 5, 2009 at 07:20:29
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   78

This 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


Report •

#15
March 5, 2009 at 07:48:37
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


Report •

#16
March 5, 2009 at 09:42:45
Thanks to all. The formulas are now working correctly.

Report •

#17
March 5, 2009 at 10:01:37
You don't get off that easy......

How did you finally set it up???

MIKE


Report •

#18
March 5, 2009 at 11:27:55
Mike:

You looking for bragging rights? ;)


Report •

#19
March 5, 2009 at 11:42:23
Curiosity more than anything,
but it's always nice to know it was your idea that worked. :-)

MIKE


Report •

#20
March 7, 2009 at 06:52:00
My joy was short-lived; the formulas do not work with zeros in the cents column.

A= original numbers
B = dollar column
C = cents column

1,289.00 returns 1,289 and 89
1,345.02 returns 1,345 and 2
total returns 2,643 and 91

for dollars:
=int(A1)
=int(A2)

for cents:
=right(A1,2)*1
=right(A2,2)*1

total for dollars:
=sum(B1:B2)+(left(sum(C1:C2))))

total for cents:
=right(sum(C1:C2),2)



Report •

#21
March 7, 2009 at 11:09:16
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.



Report •

#22
March 7, 2009 at 11:15:40
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


Report •

#23
March 7, 2009 at 13:02:35
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.


Report •

#24
March 7, 2009 at 13:29:54
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


Report •

#25
March 7, 2009 at 15:19:57
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.


Report •

#26
March 7, 2009 at 15:48:08
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


Report •

#27
March 7, 2009 at 18:11:14
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.


Report •

#28
March 7, 2009 at 20:34:48
>>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


Report •

#29
March 9, 2009 at 07:48:01
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.


Report •

#30
March 9, 2009 at 13:07:39
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?


Report •

#31
March 9, 2009 at 14:30:17
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...


Report •

#32
March 10, 2009 at 05:38:14
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.

Report •

#33
March 10, 2009 at 06:32:09
OK, I give up.

Report •

#34
March 10, 2009 at 06:58:30
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


Report •

#35
March 10, 2009 at 07:20:21
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?


Report •

#36
March 10, 2009 at 08:47:23
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


Report •

#37
March 10, 2009 at 08:51:41
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)*1

For A2 dollars: = int(A2)
For A2 cents: =if(int(A2)-A2=0,0,right)A2,2)*1

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


Report •

#38
March 10, 2009 at 08:55:28
Mike,

The actual form that is completed by hand, states that decimal points are not to be included.

Thanks,
Brian


Report •

#39
March 10, 2009 at 09:04:10
Thank you, Thank you, Thank you........

Glad we could help. :-)

MIKE


Report •

#40
March 10, 2009 at 09:10:10
Mike,

Please look at Response Number 37; I'm still having a problem.

Thanks,
Brian


Report •

#41
March 10, 2009 at 09:52:34
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)*1

You 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)*1

You 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


Report •

#42
March 10, 2009 at 10:28:29
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.


Report •

#43
March 10, 2009 at 11:23:26
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


Report •

#44
March 10, 2009 at 14:28:52
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.


Report •

#45
March 10, 2009 at 16:57:52
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!


Report •

#46
March 11, 2009 at 14:16:27
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.01

In 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


Report •

#47
March 12, 2009 at 08:47:56
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...


Report •

#48
March 12, 2009 at 08:49:56
*bang!*

It will equal 2.02, not 2.01. Or 1.02, or 1.01 which i mistakenly typed above


Report •

#49
March 12, 2009 at 13:14:19
I'm so glad we could amuse you.

MIKE


Report •

#50
March 12, 2009 at 13:28:46
mumble mumble babada babada babada

twirling hair around finger

face twitching

drooling

http://www.youtube.com/watch?v=3iDl...


Report •

#51
March 12, 2009 at 13:56:40
Has anybody tried my suggestions from Response 45?

I tested 1234.00 through 1234.99 and they seem to work just fine.


Report •

#52
March 12, 2009 at 15:48:28
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.


Report •

#53
March 13, 2009 at 06:20:20
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.

Report •

#54
March 13, 2009 at 06:28:27
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.


Report •

#55
March 13, 2009 at 06:36:59
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!


Report •

#56
March 13, 2009 at 10:14:12
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!


Report •

#57
March 13, 2009 at 11:35:51
You got it.

Report •


Ask Question