Summing Values that are True and Not Rounded
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.
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" <walkerdayle.1x5myc_1129734307.7529@excelforum-nospam.com>
wrote in message
news:walkerdayle.1x5myc_1129734307.7529@excelforum-nospam.com...
>
> 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.
>
>
> --
> walkerdayle
> ------------------------------------------------------------------------
> walkerdayle's Profile:
http://www.excelforum.com/member.php...o&userid=10021
> View this thread: http://www.excelforum.com/showthread...hreadid=477508
>
walkerdayle wrote:
> 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.
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
Originally Posted by 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" <walkerdayle.1x5myc_1129734307.7529@excelforum-nospam.com>
wrote in message
news:walkerdayle.1x5myc_1129734307.7529@excelforum-nospam.com...
>
> 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.
>
>
> --
> walkerdayle
> ------------------------------------------------------------------------
> walkerdayle's Profile:
http://www.excelforum.com/member.php...o&userid=10021
> View this thread: http://www.excelforum.com/showthread...hreadid=477508
>
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" <walkerdayle.1x5sij_1129741515.2075@excelforum-nospam.com>
wrote in message
news:walkerdayle.1x5sij_1129741515.2075@excelforum-nospam.com...
>
> 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
>
> Bob Phillips Wrote:
> > 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"
> > <walkerdayle.1x5myc_1129734307.7529@excelforum-nospam.com>
> > wrote in message
> > news:walkerdayle.1x5myc_1129734307.7529@excelforum-nospam.com...
> > >
> > > 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.
> > >
> > >
> > > --
> > > walkerdayle
> > >
> > ------------------------------------------------------------------------
> > > walkerdayle's Profile:
> > http://www.excelforum.com/member.php...o&userid=10021
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=477508
> > >
>
>
> --
> walkerdayle
> ------------------------------------------------------------------------
> walkerdayle's Profile:
http://www.excelforum.com/member.php...o&userid=10021
> View this thread: http://www.excelforum.com/showthread...hreadid=477508
>
walkerdayle wrote:
> 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).
Thank you for all your help, I was able to fix it using the round function pasting the values in another column to calculate.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks