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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|