Need formula to add cents and make dollars

PureCrazed

New Member
Joined
Oct 7, 2005
Messages
30
Ok, here is the situation. I have one cell that is "dollars" and another cell that is "cents". I'm adding a couple rows together and getting a total at the bottom. Problem is that I need a formula that takes anything over 99 cents and make it go to the cell next to it as a "dollar". Here is what I got.

E9 = .20
E10 = .20
E11 = .20
E12 = .30
E13 = .20

That makes E14 = 1.10

I want my cell (E14) to read as ".10" and have those 100 penny's move over to the cell "D14" as 1 dollar. This is the formula I created that I think will work but I'm having a problem taking the amount and moving it.

=SUM(IF(SUM(E9:E13)>99,HERE,SUM(E9:E13)))

This formula will show whatever the amount is IF it is under 1.00 but where I put HERE is where I know I could put something to take the amount and show the ".10" and take whatever is over and put it to the other cell (D14).

Does that make sense?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

goldfish

Well-known Member
Joined
Aug 23, 2005
Messages
712
For the Cents piece you could use:
Code:
MOD(SUM(E9:E13),1)
And for the Dollar part:
Code:
INT(SUM(E9:E13))

HTH,
~Gold Fish
 

PureCrazed

New Member
Joined
Oct 7, 2005
Messages
30
That isn't working for me. I've never used a "MOD" formula before. Is that suppose to be an addition to my formula or a replacement?

I tried it as a replacement, it worked with a little tweaking but now it's not. :(
 

PureCrazed

New Member
Joined
Oct 7, 2005
Messages
30
Awesome, the cents worked perfectly after a little bit of tweaking. I decided not to use the decimal on my cents so the MOD(1) wasn't working. Had to make it MOD(100).

I'll try the dollars later tonight. Thanks alot!!
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Assuming you're no longer using a decimal point for the results either:

Formula in D14 (Dollars):
=INT(SUM(E9:E13)/100)

Formula in E14: (Cents)
=SUM(E9:E13)-(D14*100)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top