Excel rounding off decimals

[alliswell]

Hi everybody

I want to round off in decimal places
For example :
If its 1.1 then it shud become 1
If its 1.12 then 1.1
If its 1.125 then 1.3 and so on

And also how to do through cell format please show me to automatically take in effect

 

[Joe4]

I don't understand your logic.

Your first example goes from 1 decimal place to 0.
Your second goes from 2 decimal places to 1.
Your third goes from 3 decimal places to 1.
(And I don't see how you would get 1.3 from 1.125. Did you actually mean 1.13?)

Are you just trying to drop one decimal spot (however long the entry is) and round it to that?

 

[Joe4]

And will every entry have at least one decimal place?

Also, are you really still using Excel 2007, or something more recent. There are a bunch of new functions that would make this easier, if you have access to them.

 

[Joe4]

If my assumptions are correct (you are just trying to reduce the number of decimal places by one and round), and as long as you always have at least one decimal place, you could use a formula like this for an entry in cell A1:

=ROUND(A1,LEN(RIGHT(A1,LEN(A1)-FIND(".",A1)))-1)

 

[Scott Huish]

Perhaps:
=ROUND(A1,LEN(MOD(A1,1))-3)

 

[alliswell]

If my assumptions are correct (you are just trying to reduce the number of decimal places by one and round), and as long as you always have at least one decimal place, you could use a formula like this for an entry in cell A1:

=ROUND(A1,LEN(RIGHT(A1,LEN(A1)-FIND(".",A1)))-1)

View attachment 120661

Yes joe its working. Thanks a lot. Just a minor issue. If its 1 in A1, it shows value error #VALUE!, if its 1 then must show 1.

Your assumption was right. Actually i made a mistake in explaining. Sorry for that.

 

[alliswell]

Perhaps:
=ROUND(A1,LEN(MOD(A1,1))-3)

Thanks a lot Scott. Its working. Just a minor issue. If its 1 in A1, it shows value 0, if its 1 then must show 1.

 

[Phuoc]

Try this:

=ROUND(A1,LEN(TEXT(MOD(A1,1),"0.0#####"))-3)

 

[Joe4]

Yes joe its working. Thanks a lot. Just a minor issue. If its 1 in A1, it shows value error #VALUE!, if its 1 then must show 1.

Your assumption was right. Actually i made a mistake in explaining. Sorry for that.

Phuoc's answer should do what you want.