Need formula to add cents and make dollars

[PureCrazed]

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?

 

[goldfish]

For the Cents piece you could use:

MOD(SUM(E9:E13),1)

And for the Dollar part:

INT(SUM(E9:E13))

HTH,
~Gold Fish

 

[PureCrazed]

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]

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]

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)