Alternate Rounding Up & Down of .50 in a column

[HunterX]

Hello,

I haven't had any success in trying to do the following:

I have a column of hours - all with .50 (such as 8.50, 7.50, 1.50, etc.)

I am trying to program a routine to go down the column cell by cell and
alternately round the first cell up to the nearest whole number, round the
second cell down to the nearest whole number, round the third one up to the
nearest whole number, etc.

I appreciate any help with this. Thank you !!

 

[Roger Govier]

Hi

Try
=IF(MOD(ROW(),2)<>0,ROUNDUP(A1,0),ROUNDDOWN(A1,0))

Depending upon whether the first row you are trying to Roundup is Odd (use
formula as is) or if even the change <> to =

Copy down as required
--

Regards
Roger Govier

Hello,

I haven't had any success in trying to do the following:

I have a column of hours - all with .50 (such as 8.50, 7.50, 1.50, etc.)

I am trying to program a routine to go down the column cell by cell and
alternately round the first cell up to the nearest whole number, round the
second cell down to the nearest whole number, round the third one up to
the
nearest whole number, etc.

I appreciate any help with this. Thank you !!

__________ Information from ESET Smart Security, version of virus
signature database 5166 (20100602) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5166 (20100602) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Jim Cone]

Another way (requires the Analysis ToolPak)...
=MROUND(A1,2)
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware




"HunterX" <[email protected]>
wrote in message
Hello,
I haven't had any success in trying to do the following:

I have a column of hours - all with .50 (such as 8.50, 7.50, 1.50, etc.)

I am trying to program a routine to go down the column cell by cell and
alternately round the first cell up to the nearest whole number, round the
second cell down to the nearest whole number, round the third one up to the
nearest whole number, etc.
I appreciate any help with this. Thank you !!

 

[Tom Hutchins]

If the first number is in an odd-numbered row (cell A1 in my example), try
this formula:
=IF(MOD(ROW(),2)=1,CEILING(A1,1),FLOOR(A1,1))
and copy down.

If the first number is in an even-numbered row (cell A2 in my example), try
this version:
=IF(MOD(ROW(),2)=0,CEILING(A2,1),FLOOR(A2,1))
and copy down.

CEILING rounds up, FLOOR rounds down.

Hope this helps,

Hutch

 

[T. Valko]

round the first cell up to the nearest whole number

round the second cell down to the nearest whole number

Assuming the numbers start in cell A2, enter this formula in B2 and copy
down as needed:

=IF(MOD(ROWS(A$2:A2),2),A2+0.5,A2-0.5)

 

[Gord Dibben]

With Analysis Toolpak add-in loaded.

In an adjacent column enter.

=IF(ISEVEN(ROW()),ROUNDDOWN(A1,0),ROUNDUP(A1,0))

Copy down.

If not the pattern you need, swap the ROUNDDOWN and ROUNDUP or rplace ISEVEN
with ISODD


Gord Dibben MS Excel MVP