Hi!
I am wondering how to solve this:
Numbers in one column need to be rounded to the nearest half decimal, for example: 0,4 - 0,5 0,7 - 1,0 1,4 - 1,5 1,1 - 1,5
Is there an easy way around this? Could You please help!

Many thanks, You're all the best!

Srecak:) :confused: :)

Searching Excel Help I do not find rounding to the nearest half decimal as an option. (I'm using Excel 2002)

I only find rounding options for
Up,
Down,
Nearest number, or
Number of decimal places.

Can't figure out how to do this in the entry cell but if you enter the following formula:

=IF(SUM(F8)-INT(F8)<0.5,INT(F8)+0.5,INT(F8))

in another cell, this does the job.

Note: F8 is your entry cell

Install the Analysis Pak and use this function:

=CEILING(A1,0.5)

The value would be in A1

Casey

Actually I just realized that you don't need the Analysis Pak for the CEILING function but if you do want to use that pak go to TOOLS -> Add-Ins and then check the Analysis Pak. It has functions such as MROUND which you might find handy.

Casey

Colbtech,

Your formula wasn't too far off and you were on the right track. I think this is what you were gunning for:


=IF(F8-INT(F8)<=0.5,IF(MOD(F8,1)=0,F8,INT(F8)+0.5),ROUNDUP(F8,0))
Casey

Srecak,

Glad to help and thanks for letting us know that it worked for you :)

Casey

Thanks caj,

I looked at my formula... must have been a bad day. Should have taken a little more time to check! That'll teach me.

ceiling will not always give the rounded value:
ceiling(22.1,0.5)=22.5
but rounding it to nearest val should be 22.0

try this: =ROUND(A1/0.5,0) * 0.5
which would return : 22 for 22.1 , 22.5 for 22.3
ceiling and floor in one function!!

ceiling will not always give the rounded value:
ceiling(22.1,0.5)=22.5
but rounding it to nearest val should be 22.0

try this: =ROUND(A1/0.5,0) * 0.5
which would return : 22 for 22.1 , 22.5 for 22.3
ceiling and floor in one function!!!!

This is great... but.. is it possible to round to to 2 decimal places

e.g..

10.00 - 10.24 = rounded down to 10

10.25 - 10.49 = rounded up to 10.5

10.5 - 10.74 = rounded down to 10.5

10.75 - 10.9 = rounded up to 11

Hi you could try the following formula

=IF(SUM(F24)-INT(F24)=0.25,SUM(F24)-INT(F24)=0.75,INT(F24)+1,F24)))


Assuming that Cell F24 is holding the value that you want to round up or down.

Hope it helps

Dil

I Understand Your Problem. So
Try Out This Formula Its Really Works
=round(a1/1,0)*1

Note: Enter Value In A1
And Formula In Different Place

Mohan Chauhan,ropar

This thread is two years old... probably don't need any more responses.