Summing Values that are True and Not Rounded

W

walkerdayle

HELP! I have a problem at work where I have data in 3 columns that are
derived using the ROUND function to 2 decimal places. In my total
column, I want the values of the data added in it's true form. How do
I do it?

Hopefully I've explained it right. :confused:
 
B

Bob Phillips

If you mean that you want to sum the un-rounded values, I think you have a
problem. If it is values, then how can you possibly know what it was rounded
from? If it is formula relating to other cells, you might be able to concoct
a formula that uses those original cells.

If on the other hand it is just formatted to 2 dec places, no problem. Just
sum them.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"walkerdayle" <[email protected]>
wrote in message
news:[email protected]...
 
J

joeu2004

walkerdayle said:
HELP! I have a problem at work where I have data
in 3 columns that are derived using the ROUND
function to 2 decimal places. In my total column,
I want the values of the data added in it's true form.
How do I do it?

You cannot "unround" a formula. So you must
duplicate the data without using the ROUND function
and SUM the unrounded data.

Do you really need to use ROUND, in the first place?
Would it suit your purposes to simply format the
original data cells as a Number with 2 decimal places?

Thus, the original data cells will appear to be
rounded, but they will retain their true value, which
you can sum and do other operations with.
 
W

walkerdayle

Thank you for your suggestion. As an alternative, how can I sum up the
rounded values but not the values in the cell? For example: B3 = 5.23
(but it's really 5.2345) and B4=3.23 (3.23111)

How can I add the two and get the true answer even though B3:B4 are
answers using the ROUND function. Note: I didn't create this
spreadsheet.

Anita :confused:
 
B

Bob Phillips

Anita, Hi,

If you know it is 5.2345 not 5.23 can I assume that the real value is
somewhere else.

Can you tell me what the formula is in B3, and also B4 to see the pattern?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"walkerdayle" <[email protected]>
wrote in message
 
J

joeu2004

walkerdayle said:
As an alternative, how can I sum up the rounded values
but not the values in the cell? For example: B3 = 5.23
(but it's really 5.2345) and B4=3.23 (3.23111)

How can I add the two and get the true answer even though
B3:B4 are answers using the ROUND function.

Your first and second questions are contradictory.
What are you calling the "true answer"?

I presume you would like the sum to be 8.465561 or rounded
to 8.47 instead of 8.46, which is the sum of the rounded
values. Please confirm.

(Note that that is __not__ "summing up the rounded values
but not the values in the cell".)

Simply put: you cannot do that, at least not directly.

How big is the spreadsheet -- at least the cells that
have rounded values?

If the cells have "=ROUND(...,2)", can you simply do
one of two things, depending on which best fits your
overall needs (which are not clear):

a. Simply edit the cells and remove "=ROUND(" and ",2)".
Ideally, you only need to edit one cell, then copy
the modified formula into all similar cells. If you
want any cells to appear to be rounded to 2 decimal
places, simply change the cell format (Format > Cells
Number, and select Number and 2 Decimal Places).

b. Copy the cells with "=ROUND(...,2)", then paste them
somewhere else. Edit the copied cells as described
in #a.

Of course, we are all shooting blindly because we
cannot see your spreadsheet, and your description begs
for clarification.

A much simpler solution might be possible if, for
example, the rounded formula is simply "=ROUND(A3,2)".
In that case, you might not need to do the edits
described in #a or #b at all. All you need to do is
SUM(A3:A4) instead of SUM(B3:B4).
 
W

walkerdayle

Thank you for all your help, I was able to fix it using the roun
function pasting the values in another column to calculate
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top