rounding

[tankgirl215]

Hi,

I'm having trouble with excel. I am taking information from one sheet
and using formulas to have it appear on another sheet in the format I
need. Right now I'm needing to take a number in this format:

12.25 12.25 2.75
9 6 6
8.75 8.75 .25

and have it appear in the other sheet in this format:

12.3 12.3 2.75
9.00 6.00 6.00
8.75 8.75 0.25

The final cells are to be right-justified, zero-filled, and kept to 4
characters including the decimal (hence the need for rounding for the
12.25 to get to 12.3)

How would I do this?

 

[Ron Rosenfeld]

Hi,

I'm having trouble with excel. I am taking information from one sheet
and using formulas to have it appear on another sheet in the format I
need. Right now I'm needing to take a number in this format:

12.25 12.25 2.75
9 6 6
8.75 8.75 .25

and have it appear in the other sheet in this format:

12.3 12.3 2.75
9.00 6.00 6.00
8.75 8.75 0.25

The final cells are to be right-justified, zero-filled, and kept to 4
characters including the decimal (hence the need for rounding for the
12.25 to get to 12.3)

How would I do this?

When you write "have it appear" I assume you are looking for appearance and not
content. In other words, if the value in the cell is still 12.25, but it
appears like 12.3, this is the desired solution.

What is the possible range of data?

If the range is 0-99.9, then you can use the custom format:
Format/Cells/Number/Custom Type: [<10]0.00;[>=10]00.0;General


--ron

 

[tankgirl215]

Hi Ron,

No, I'm able to just use formatting to get it to "appear" in the cells
that way, but then the formula on the next sheet still pulls the
original information. (It still takes the 12.75 instead of the 12.3)
I need the formula in the new sheet to be able to pull the rounded
numbers, not the originals. So having it just "appear" doesn't do the
trick...I actually need to change the data.

Thanks!

Hi,

I'm having trouble with excel. I am taking information from one sheet
and using formulas to have it appear on another sheet in the format I
need. Right now I'm needing to take a number in this format:

12.25 12.25 2.75
9 6 6
8.75 8.75 .25

and have it appear in the other sheet in this format:

12.3 12.3 2.75
9.00 6.00 6.00
8.75 8.75 0.25

The final cells are to be right-justified, zero-filled, and kept to 4
characters including the decimal (hence the need for rounding for the
12.25 to get to 12.3)

How would I do this?

When you write "have it appear" I assume you are looking for appearance and not
content. In other words, if the value in the cell is still 12.25, but it
appears like 12.3, this is the desired solution.

What is the possible range of data?

If the range is 0-99.9, then you can use the custom format:
Format/Cells/Number/Custom Type: [<10]0.00;[>=10]00.0;General


--ron

 

[Ron Rosenfeld]

Hi Ron,

No, I'm able to just use formatting to get it to "appear" in the cells
that way, but then the formula on the next sheet still pulls the
original information. (It still takes the 12.75 instead of the 12.3)
I need the formula in the new sheet to be able to pull the rounded
numbers, not the originals. So having it just "appear" doesn't do the
trick...I actually need to change the data.

Thanks!

Well, you wrote that you wanted it to "appear". So that is what I responded
to.

If you need to change the data, that's simple also. But we need to have some
idea of the potential data range in order to do this easily.

As I wrote before, "What is the possible range of data?"

In other words, what is the smallest value that might be in the cell, and what
is the largest value?






--ron

 

[tankgirl215]

The smallest value will be .25 and the largest would be 99.9

 

[Ron Rosenfeld]

The smallest value will be .25 and the largest would be 99.9

Formula:
=IF(A1<10,ROUND(A1,2),ROUND(A1,1))

Then custom format the cell--
Format/Cells/Number Custom Type:

[<10]0.00;[>=10]00.0;General
--ron

 

[Ron Rosenfeld]

The smallest value will be .25 and the largest would be 99.9

You could also use this formula:

=ROUND(A1,1+(A1<10))

and the same formatting


--ron