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

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

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

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

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.

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

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.