# Format and what the total is coming out to

#### fatbob

##### New Member
A B C
\$530.00 \$5.30 \$5.30
\$562.50 \$5.63 \$5.63
\$562.50 \$5.63 \$5.63
\$562.50 \$5.63 \$5.63
\$562.50 \$5.63 \$5.63
\$2,780.00 \$27.80 \$27.82

Here is the code for row B
=SUM(D3*0.01)

But the C row has no formula.
Just the numbers. And the total is correct.

Can anyone figure out how to fix this using the A and B to make the totals right?

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### barry houdini

##### MrExcel MVP
Try using ROUND

=ROUND(D3*0.01,2)

#### stanleydgromjr

##### Banned
For a starter:

What is in cell 'A1'?

What is in cell 'B1'?

What is in cell 'C1'?

Please explain in detail what you are trying to do in row 1?

Have a great day,
Stan

#### fatbob

##### New Member
Sorry it didnt come out right on the page when pasting.

A B C
\$530.00 \$5.30 \$5.30
\$562.50 \$5.63 \$5.63
\$562.50 \$5.63 \$5.63
\$562.50 \$5.63 \$5.63
\$562.50 \$5.63 \$5.63
\$2,780.00 \$27.80 \$27.82

A Cell is just whats here.
B Cell =SUM(A1*0.01)
And when B cell is totaled up at the bottom the 27.80 is wrong.
But if I just type in like the C cell just the numbers and add its 27.82 which is what I want but I need the B cell formula for this to happen and give me the 27.82 result.

And I tried Barrys suggestion of rounding and that didnt do anything.

#### barry houdini

##### MrExcel MVP
If you use the formula

=SUM(A1*0.01) and A1 contains \$562.50 then the result will be \$5.625

Now, presumably because you have the cell formatted to show two decimal places (i.e. cents), the result displays as \$5.63 BUT the underlying value is still \$5.625 and therefore when you sum these values excel correctly uses the actual underlying value and the result displayed does not appear to tally correctly.

If you use my suggestion and change the formula to ROUND(A1*0.01,2) then the actual value of B1 will be changed to \$5.63 so when you sum column B you'll get the answer you expect.

I'm assuming that you're using a formula like SUM(B1:B10) to give the total at the bottom.....
Book2
ABCD
1\$530.00\$5.30
2\$562.50\$5.63
3\$562.50\$5.63
4\$562.50\$5.63
5\$562.50\$5.63
6\$2,780.00\$27.82
7
Sheet3

Replies
3
Views
345
Replies
1
Views
363
Replies
4
Views
539
Replies
1
Views
407
Replies
6
Views
161

1,171,824
Messages
5,877,760
Members
433,285
Latest member

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

### Which adblocker are you using?

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

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