Change a number to round up without a function
 

Is there a way to round numbers up so that if I enter 45.999 in cell A1, it
will display at 46 in the cell and in the formula bar?


I do not want the originally entered number to show any decimals.

Use a format of #,##0

--
HTH

Bob Phillips

"teebee0831" wrote in message
...
Is there a way to round numbers up so that if I enter 45.999 in cell A1,
it
will display at 46 in the cell and in the formula bar?


I do not want the originally entered number to show any decimals.

That only changes how the number displays in the cell. I want to change the
number even in the formula bar so the original number with decimals does not
display anywhere.

"Bob Phillips" wrote:

Use a format of #,##0

--
HTH

Bob Phillips

"teebee0831" wrote in message
...
Is there a way to round numbers up so that if I enter 45.999 in cell A1,
it
will display at 46 in the cell and in the formula bar?


I do not want the originally entered number to show any decimals.

The way you are describing it is this. You enter 45.9 in cell A1. You want
to see 46 in the output of the cell and in the formula bar. If you type
"=A1" in cell A2 you get a result of 45.9. I don't see any reason for this,
other than to confuse yourself and others. I don't have an answer to the
problem described this way. Here are two solutions that might help you, of
you are trying something a little different.

1. Use the "Decrease Decimal" button. This is most commonly used to change
the output of the cell but have other cells still treat it as a decimal.
This doesn't change the number in the formula bar however.

2. Use the "Round" function. "=Round(45.6,0)" will yield an output of 46.
There are also Roundup and Rounddown functions. This is used to modify a
result and have other cells recognize it as a rounded number. Something like
this would be usefull when adding dollar amounts and tax. You want to round
to the nearest cent.

Sorry if these don't help, but I can't think of a single situation in which
this would be usefull. Why can't you just type 46?

"teebee0831" wrote:

Is there a way to round numbers up so that if I enter 45.999 in cell A1, it
will display at 46 in the cell and in the formula bar?


I do not want the originally entered number to show any decimals.

The numbers are being copied and pasted from another worksheet.

"Sloth" wrote:

The way you are describing it is this. You enter 45.9 in cell A1. You want
to see 46 in the output of the cell and in the formula bar. If you type
"=A1" in cell A2 you get a result of 45.9. I don't see any reason for this,
other than to confuse yourself and others. I don't have an answer to the
problem described this way. Here are two solutions that might help you, of
you are trying something a little different.

1. Use the "Decrease Decimal" button. This is most commonly used to change
the output of the cell but have other cells still treat it as a decimal.
This doesn't change the number in the formula bar however.

2. Use the "Round" function. "=Round(45.6,0)" will yield an output of 46.
There are also Roundup and Rounddown functions. This is used to modify a
result and have other cells recognize it as a rounded number. Something like
this would be usefull when adding dollar amounts and tax. You want to round
to the nearest cent.

Sorry if these don't help, but I can't think of a single situation in which
this would be usefull. Why can't you just type 46?

"teebee0831" wrote:

Is there a way to round numbers up so that if I enter 45.999 in cell A1, it
will display at 46 in the cell and in the formula bar?


I do not want the originally entered number to show any decimals.

teebee0831 wrote:
Is there a way to round numbers up so that if I enter 45.999 in cell A1, it
will display at 46 in the cell and in the formula bar?

Under Tools / Options / Calculations, select "Precision as displayed".
Then under Format / Cells / Number, select your precision.

Be forewarned: "precision as displayed" affects the entire
spreadsheet. It can lead to unexpected results, if you are
not careful.

AFAIK, there is nothing you can do will change the 'actual' value listed
on the formula bar of that cell without using VBA code.

Except to use Paste Special... Add (or subtract, multiply, divide) In
another cell, enter 0.1 and Copy. Go to your cell with 45.9 and Paste
Special...Add and your cell will now show 46 both in the cell and on
the formula bar.

Not pretty, but it does work in this one instance.


Good Luck

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=383525

Thanks. That is just what I wanted.

" wrote:

teebee0831 wrote:
Is there a way to round numbers up so that if I enter 45.999 in cell A1, it
will display at 46 in the cell and in the formula bar?

Under Tools / Options / Calculations, select "Precision as displayed".
Then under Format / Cells / Number, select your precision.

Be forewarned: "precision as displayed" affects the entire
spreadsheet. It can lead to unexpected results, if you are
not careful.