if function with rounding

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Holly - 30 Apr 2007 18:58 GMT

I need to set up a function that rounds numbers up to the nearest whole
number if the decimal is greater than .5 and the number is odd, and rounds
down to the nearest whole number if the decimal is less than .5 with an odd
integer or if it is even no matter what the decimal is. Does anyone know if
there is a way to set this up?? I am trying to help out a co-worker, and I
hit a wall with this spreadsheet.

Thanks

Reply to this Message

Bob Phillips - 30 Apr 2007 19:03 GMT

=ROUND(A1,0)-(INT(A1/2)*2=INT(A1))

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I need to set up a function that rounds numbers up to the nearest whole
> number if the decimal is greater than .5 and the number is odd, and rounds
[quoted text clipped - 6 lines]
>
> Thanks

Reply to this Message

Holly - 30 Apr 2007 19:34 GMT

the only problem is a whole number needs to stay the same

and I typed in 54 and its giving me 53

> =ROUND(A1,0)-(INT(A1/2)*2=INT(A1))
>
[quoted text clipped - 8 lines]
> >
> > Thanks

Reply to this Message

Bob Phillips - 01 May 2007 00:30 GMT

=ROUND(A1,0)-(INT(A1/2)*2=INT(A1))*(MOD(A1,1)>0)

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> the only problem is a whole number needs to stay the same
>
[quoted text clipped - 15 lines]
>> >
>> > Thanks

Reply to this Message

JE McGimpsey - 30 Apr 2007 19:59 GMT

One way:

=INT(A1)+(ROUND(MOD(A1,2),12)>=1.5)

> I need to set up a function that rounds numbers up to the nearest whole
> number if the decimal is greater than .5 and the number is odd, and rounds
[quoted text clipped - 4 lines]
>
> Thanks

Reply to this Message