Round up in Excel

Srecak · #1 Mar 29, 2006, 12:40 AM

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

Many thanks, You're all the best!

Srecak

RickJ · Mar 29, 2006, 01:50 AM

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 · Mar 29, 2006, 02:02 AM

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 · Mar 29, 2006, 02:50 AM

Install the Analysis Pak and use this function:

=CEILING(A1,0.5)

The value would be in A1

Casey

cajalat · Mar 29, 2006, 02:57 AM

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 · Mar 29, 2006, 03:37 AM

Colbtech,

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

Code:

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

Casey

cajalat · Mar 29, 2006, 03:40 AM

Srecak,

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

Casey

colbtech · Mar 29, 2006, 04:58 AM

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 · Feb 26, 2007, 11:52 AM

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 · Jun 21, 2007, 01:59 AM

Quote:

Originally Posted by 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!!!!

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

eg.

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



At Ask Me Help Desk you can ask questions in any topic and have them answered for free by our experts. To ask questions or participate in answering them you must register for a free account. By registering you will be able to: Get free answers from experts in any of our 300+ topics. Accept money for answers that you provide. Communicate privately with other members (PM). See fewer ads. Sign Up!