getting numbers divisible by 4 from random numbers in ascending order

[ramana]

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

 

[Guest]

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.

 

[ramana]

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








- 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

 

[Guest]

Typo:

say down to A200?

should read as:

say down to C200?

---

 

[Max]

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()-1>COUNT(B:B),"",INDEX(A:A,MATCH(SMALL(B:B,ROW()-1),B:B,0)))

 

[Ron Rosenfeld]

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

 

[T. Valko]

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