Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
getting numbers divisible by 4 from random numbers in ascending order
Hi Everybody,
one more problem, I have random numbers in column A, now in column B I need the numbers that are divisible by 4 in ascending order in consequtive rows. could any body help me to solve this. Thanks and Regards Ramana |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
getting numbers divisible by 4 from random numbers in ascending or
One way using non-array formulas
Assuming random numbers running in A1 down In B1: =IF(OR(A1="",A1=0),"",IF(MOD(A1,4)=0,A1+ROW()/10^10,"")) In C1: =IF(ROW()COUNT(B:B),"",INDEX(A:A,MATCH(SMALL(B:B, ROW()),B:B,0))) Select B1:C1, copy down to cover the max expected extent of data in col A, say down to A200? Hide away col B. Col C will return the results that you seek, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ramana" wrote: Hi Everybody, one more problem, I have random numbers in column A, now in column B I need the numbers that are divisible by 4 in ascending order in consequtive rows. could any body help me to solve this. Thanks and Regards Ramana |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
getting numbers divisible by 4 from random numbers in ascending or
On Jun 19, 12:08 pm, Max wrote:
One way using non-array formulas Assuming random numbers running in A1 down In B1: =IF(OR(A1="",A1=0),"",IF(MOD(A1,4)=0,A1+ROW()/10^10,"")) In C1: =IF(ROW()COUNT(B:B),"",INDEX(A:A,MATCH(SMALL(B:B, ROW()),B:B,0))) Select B1:C1, copy down to cover the max expected extent of data in col A, say down to A200? Hide away col B. Col C will return the results that you seek, all neatly bunched at the top. -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- "ramana" wrote: Hi Everybody, one more problem, I have random numbers in column A, now in column B I need the numbers that are divisible by 4 in ascending order in consequtive rows. could any body help me to solve this. Thanks and Regards Ramana- Hide quoted text - - Show quoted text - Thank you Max, its working perfect, but the problem is when I enter the formula in row 2 its giving me the second number. Thanks and Regards Ramana |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
getting numbers divisible by 4 from random numbers in ascendin
Typo:
say down to A200? should read as: say down to C200? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
getting numbers divisible by 4 from random numbers in ascending or
If it starts in row2, use this set instead
In B2: =IF(OR(A2="",A2=0),"",IF(MOD(A2,4)=0,A2+ROW()/10^10,"")) In C2: =IF(ROW(A1)COUNT(B:B),"",INDEX(A:A,MATCH(SMALL(B: B,ROW(A1)),B:B,0))) The key adjustment is more in col C's formula which uses the row sensitive: ROW() -- just use ROW(A1) instead of ROW() in the top cell wherever this cell may be (in this case in C2) An alternative to use in C2 here would be simply to adjust it arithmetically, ie change ROW() to ROW()-1, viz in C2: =IF(ROW()-1COUNT(B:B),"",INDEX(A:A,MATCH(SMALL(B:B,ROW()-1),B:B,0))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ramana" wrote Thank you Max, its working perfect, but the problem is when I enter the formula in row 2 its giving me the second number. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
getting numbers divisible by 4 from random numbers in ascending order
On Tue, 19 Jun 2007 06:49:09 -0000, ramana wrote:
Hi Everybody, one more problem, I have random numbers in column A, now in column B I need the numbers that are divisible by 4 in ascending order in consequtive rows. could any body help me to solve this. Thanks and Regards Ramana Where rng is a defined name for the values in column A, and does not include the entire column, then the following **array** formula should do the trick. To enter an **array** formula, after typing/pasting the formula into the formula bar, you must hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. =IF((SUM(--(MOD(rng,4)<0))+ROWS($1:1))COUNT(rng),"", SMALL((MOD(rng,4)=0)*rng,SUM(--(MOD(rng,4)<0))+ROWS($1:1))) Fill down as far as necessary (until the formula starts returning blanks). Note that empty cells and zeros will be evaluated as divisible by four and will be returned by this formula. --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
getting numbers divisible by 4 from random numbers in ascending or
Or, you can use:
ROWS($1:1) Which doesn't matter what row the formula is on *and* is robust against row insertions. I cringe when I see formulas that use ROW() or ROW(A1). <VBG Biff "Max" wrote in message ... If it starts in row2, use this set instead In B2: =IF(OR(A2="",A2=0),"",IF(MOD(A2,4)=0,A2+ROW()/10^10,"")) In C2: =IF(ROW(A1)COUNT(B:B),"",INDEX(A:A,MATCH(SMALL(B: B,ROW(A1)),B:B,0))) The key adjustment is more in col C's formula which uses the row sensitive: ROW() -- just use ROW(A1) instead of ROW() in the top cell wherever this cell may be (in this case in C2) An alternative to use in C2 here would be simply to adjust it arithmetically, ie change ROW() to ROW()-1, viz in C2: =IF(ROW()-1COUNT(B:B),"",INDEX(A:A,MATCH(SMALL(B:B,ROW()-1),B:B,0))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ramana" wrote Thank you Max, its working perfect, but the problem is when I enter the formula in row 2 its giving me the second number. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
getting numbers that are divisible by 4 | Excel Worksheet Functions | |||
Can I create a random order within a series of numbers in Excel? | Excel Worksheet Functions | |||
need a conditional formula to generate numbers divisible by 4 between a given starting no. & end No. | Excel Worksheet Functions | |||
How get true response when numbers divisible by 12? | Excel Worksheet Functions | |||
How do I split a column having numbers and text in a random order | Excel Worksheet Functions |