Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
getting numbers that are divisible by 4 ramana Excel Worksheet Functions 4 June 19th 07 08:18 AM
Can I create a random order within a series of numbers in Excel? Dimtrax Excel Worksheet Functions 2 November 9th 05 04:40 PM
need a conditional formula to generate numbers divisible by 4 between a given starting no. & end No. ramana Excel Worksheet Functions 5 October 21st 05 07:39 AM
How get true response when numbers divisible by 12? lax_fan Excel Worksheet Functions 2 May 16th 05 11:39 PM
How do I split a column having numbers and text in a random order rana8689 Excel Worksheet Functions 2 December 17th 04 10:14 PM


All times are GMT +1. The time now is 11:32 AM.

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

About Us

"It's about Microsoft Excel"