A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Alternate Rounding Up & Down of .50 in a column



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 03:46 PM posted to microsoft.public.excel.worksheet.functions
HunterX
external usenet poster
 
Posts: 1
Default Alternate Rounding Up & Down of .50 in a column

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 !!
  #2  
Old June 2nd, 2010, 04:15 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default Alternate Rounding Up & Down of .50 in a column

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

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

__________ 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



  #3  
Old June 2nd, 2010, 04:23 PM posted to microsoft.public.excel.worksheet.functions
Jim Cone[_2_]
external usenet poster
 
Posts: 434
Default Alternate Rounding Up & Down of .50 in a column


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




"HunterX"
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 !!
  #4  
Old June 2nd, 2010, 04:28 PM posted to microsoft.public.excel.worksheet.functions
Tom Hutchins
external usenet poster
 
Posts: 722
Default Alternate Rounding Up & Down of .50 in a column

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

"HunterX" wrote:

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

  #5  
Old June 2nd, 2010, 04:34 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Alternate Rounding Up & Down of .50 in a column

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)

--
Biff
Microsoft Excel MVP


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



  #6  
Old June 2nd, 2010, 04:55 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Alternate Rounding Up & Down of .50 in a column

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

On Wed, 2 Jun 2010 07:46:01 -0700, HunterX
wrote:

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.