Format and what the total is coming out to

fatbob

New Member
Joined
Feb 3, 2003
Messages
23
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Joined
Jul 30, 2006
Messages
3,656
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
Joined
Feb 3, 2003
Messages
23
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
Joined
Mar 23, 2005
Messages
20,825
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,648
Messages
5,660,100
Members
418,549
Latest member
malmattos

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