[Srecak]

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: :)

[RickJ]

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.

[colbtech]

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

[cajalat]

Install the Analysis Pak and use this function:

=CEILING(A1,0.5)

The value would be in A1

Casey

[cajalat]

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

[cajalat]

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

[cajalat]

Srecak,

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

Casey

[colbtech]

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.

[gr8one]

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!!

[ian_6800]

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

[dilpesh1]

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

[m_ropar]

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

[JBeaucaire]

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