Hello,

I am using the MROUND function to display a series of number like 1622
as 1500. The problem I am having is that I am summing all of these
cells and it sums the newly rounded number. What I would like to
happen is the individual cells to display the rounded number but have
the cell with the SUM in it display the sum of the actual, unrounded
number. Is there a way to do this? Any help is much appreciated.

On 5 Jan 2007 08:13:44 -0800, "cheeser83" wrote:

Hello,

I am using the MROUND function to display a series of number like 1622
as 1500. The problem I am having is that I am summing all of these
cells and it sums the newly rounded number. What I would like to
happen is the individual cells to display the rounded number but have
the cell with the SUM in it display the sum of the actual, unrounded
number. Is there a way to do this? Any help is much appreciated.

You will need to edit your SUM formula to refer to cells with unrounded
information.

Whether it is simpler to keep these values in a separate area on your
worksheet, or recompute from the raw data, is one of the decisions to make.
--ron

=MROUND is performing a calculation (i.e., rounding) so what you see in the
cell is what you get. Therefore when you sum these rounded numbers you are
summing rounded numbers, not their original, unrounded values.

A possible solution is for you to have a helper column of unrounded values
and sum off that column.

Dave
--
Brevity is the soul of wit.


"cheeser83" wrote:

Hello,

I am using the MROUND function to display a series of number like 1622
as 1500. The problem I am having is that I am summing all of these
cells and it sums the newly rounded number. What I would like to
happen is the individual cells to display the rounded number but have
the cell with the SUM in it display the sum of the actual, unrounded
number. Is there a way to do this? Any help is much appreciated.

This (and the other answer) makes since. However, the sheet I
currently have is already maxed out on size for printing ease. If I
have another group of cells with the real numbers in them, I would be
adding another sheet when it is printed out which just can't happen. I
guess what I need is a way of displaying a rounded number but not
actually using it in any functions. Sort of like when you format a
cell to display $23.00 instead of $23.21. But I need it to show 1500
instead of 1622.

Dave F wrote:
=MROUND is performing a calculation (i.e., rounding) so what you see in the
cell is what you get. Therefore when you sum these rounded numbers you are
summing rounded numbers, not their original, unrounded values.

A possible solution is for you to have a helper column of unrounded values
and sum off that column.

Dave
--
Brevity is the soul of wit.


"cheeser83" wrote:

Hello,

I am using the MROUND function to display a series of number like 1622
as 1500. The problem I am having is that I am summing all of these
cells and it sums the newly rounded number. What I would like to
happen is the individual cells to display the rounded number but have
the cell with the SUM in it display the sum of the actual, unrounded
number. Is there a way to do this? Any help is much appreciated.

So hide the column of unrounded values off of which you sum.

No need to print them out.
--
Brevity is the soul of wit.


"cheeser83" wrote:

This (and the other answer) makes since. However, the sheet I
currently have is already maxed out on size for printing ease. If I
have another group of cells with the real numbers in them, I would be
adding another sheet when it is printed out which just can't happen. I
guess what I need is a way of displaying a rounded number but not
actually using it in any functions. Sort of like when you format a
cell to display $23.00 instead of $23.21. But I need it to show 1500
instead of 1622.

Dave F wrote:
=MROUND is performing a calculation (i.e., rounding) so what you see in the
cell is what you get. Therefore when you sum these rounded numbers you are
summing rounded numbers, not their original, unrounded values.

A possible solution is for you to have a helper column of unrounded values
and sum off that column.

Dave
--
Brevity is the soul of wit.


"cheeser83" wrote:

Hello,

I am using the MROUND function to display a series of number like 1622
as 1500. The problem I am having is that I am summing all of these
cells and it sums the newly rounded number. What I would like to
happen is the individual cells to display the rounded number but have
the cell with the SUM in it display the sum of the actual, unrounded
number. Is there a way to do this? Any help is much appreciated.

On 5 Jan 2007 08:25:02 -0800, "cheeser83" wrote:

This (and the other answer) makes since. However, the sheet I
currently have is already maxed out on size for printing ease. If I
have another group of cells with the real numbers in them, I would be
adding another sheet when it is printed out which just can't happen. I
guess what I need is a way of displaying a rounded number but not
actually using it in any functions. Sort of like when you format a
cell to display $23.00 instead of $23.21. But I need it to show 1500
instead of 1622.

Your argument against Dave's solution does not make sense. There is no
requirement that cells added on a different or the same sheet be printed.

But if size really is an issue (and it does not sound as if it is from what you
write), you could implement my other suggestion which would be to have the SUM
formula act on the original data. This might entail using an array formula, or
the SUMPRODUCT worksheet function.
--ron

Thatnks for the tips! After reviewing the answers, I got what you
meant. I hid the rows and it worked fine. Thanks again!!

Ron Rosenfeld wrote:
On 5 Jan 2007 08:25:02 -0800, "cheeser83" wrote:

This (and the other answer) makes since. However, the sheet I
currently have is already maxed out on size for printing ease. If I
have another group of cells with the real numbers in them, I would be
adding another sheet when it is printed out which just can't happen. I
guess what I need is a way of displaying a rounded number but not
actually using it in any functions. Sort of like when you format a
cell to display $23.00 instead of $23.21. But I need it to show 1500
instead of 1622.

Your argument against Dave's solution does not make sense. There is no
requirement that cells added on a different or the same sheet be printed.

But if size really is an issue (and it does not sound as if it is from what you
write), you could implement my other suggestion which would be to have the SUM
formula act on the original data. This might entail using an array formula, or
the SUMPRODUCT worksheet function.
--ron

On 5 Jan 2007 10:25:54 -0800, "cheeser83" wrote:

Thatnks for the tips! After reviewing the answers, I got what you
meant. I hid the rows and it worked fine. Thanks again!!

You're welcome. Thanks for the feedback.
--ron