# Need formula to add cents and make dollars

#### PureCrazed

##### New Member
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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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

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.

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!!

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)

Replies
6
Views
96
Replies
3
Views
134
Replies
5
Views
411
Replies
3
Views
179
Replies
12
Views
560

1,203,242
Messages
6,054,353
Members
444,718
Latest member
r0nster

### 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?

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