adding numbers shown in cells, not what is actually in cell - help!!!

[nelsonsdavis]

This is my first time posting here, so bear with me! I am sure this
question/problem has a very simple answer, but after spending hours
searching for an answer, I gave up.

Here is my problem. An excel spreadsheet is sent to me with columns of
number setup as dollars. An example of the numbers you see when looking
at the cell would be:

$1.98

But, looking IN the cell, it says $1.97765

Now, there are about 20 cells in this column. Obviously, the numbers
are being rounded up, which is fine. BUT, all I want to do is, add the
numbers that are being displayed to me, not the underlying numbers!

So, If I have:

$1.98
$1.82
etc., I just want to add those. I do not want to add (or sum) these:
$1.97765
$1.81832

.....because, if there are 20 cells, when you sum the up, they will
always be slightly higher than if you added up the VISUAL numbers (or
the numbers you actually see when simply looking at the cells). So, how
do I only sum up the numbers I can see in the cell in the spreadsheet,
and NOT the underlying numbers?

Thanks!

 

[Don Guillett]

This maybe?
=SUMPRODUCT(ROUND(E28:E37,2))

 

[RagDyeR]

Your numbers are *formatted* to 2 decimals.

This means that *only* the display is changed, and the underlying values
remain intact.

It's your choice on which way to go.

You can change *all* the values in the sheet to what is displayed by:
<Tools> <Options> <Calculation> tab,
And checking "Precision As Displayed",
OR
You can round the individual cells to display actual values,
Something like this:

From
=SUM(A1:A10)
TO
=ROUND(SUM(A1:A10),2)

I personally prefer the "round" approach.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


This is my first time posting here, so bear with me! I am sure this
question/problem has a very simple answer, but after spending hours
searching for an answer, I gave up.

Here is my problem. An excel spreadsheet is sent to me with columns of
number setup as dollars. An example of the numbers you see when looking
at the cell would be:

$1.98

But, looking IN the cell, it says $1.97765

Now, there are about 20 cells in this column. Obviously, the numbers
are being rounded up, which is fine. BUT, all I want to do is, add the
numbers that are being displayed to me, not the underlying numbers!

So, If I have:

$1.98
$1.82
etc., I just want to add those. I do not want to add (or sum) these:
$1.97765
$1.81832

.....because, if there are 20 cells, when you sum the up, they will
always be slightly higher than if you added up the VISUAL numbers (or
the numbers you actually see when simply looking at the cells). So, how
do I only sum up the numbers I can see in the cell in the spreadsheet,
and NOT the underlying numbers?

Thanks!