checking each cell in an array whether it satisfies a condition

vizakenjack · Oct 19, 2014

So, let's say I need to have 24 numbers randomized.
But in a particular way! They all should be chaotically randomized according to these rules:
half of them (12 cells) should be randomized between 10-60
the rest (also 12 cells) between 80-90

so there's a gap between of 61-79 values, they are to be excluded
but(!) you want to have 12 cells that would lie between 10-60, and the rest between 80-90
And it should be done with a single formula, that is, you can't have a formula

Code:

=Randbetween(10,60)

in first 12 cells (which already breaks the rules, because you don't want to have first 12 cells to be randomized between 10-60, but rather the amount of cells containing the values between 10-60, which are spread out randomly in an array), and then

Code:

=Randbetween(80,90)

in the remainder of the cells.
"Search" function returns the position of a character in a text... totally not what I need. I need a formula that would check each cell in an array/selection on whether it satisfies the condition of being more than 60.
Or is there a way to solve it by using simple formulas? Also, randbetween has to be used here, so please don't suggest advices that do not involve using this formula.

Snakehips · Oct 19, 2014

visakenjack,

I see you have had no response as yet so here is an offering.......

I assume that duplicate values are not an issue since to pick 12 numbers from the range 80 to 90 guarantees at least one duplicate?

It does require using a 'helper column' and I am unsure as to whether you will find that acceptable?

The helper can be any column you care to use and can be outside your normal dat set and hidden if required.

So using a helper range A1:A24.

Excel 2007

A

B

C

1

0.706978159

56

2

0.8111149

41

3

0.095217119

86

4

0.268261338

84

5

0.71636209

49

6

0.552367459

86

7

0.922631542

13

8

0.540021839

88

9

0.616209495

43

10

0.691475098

22

11

0.02273162

83

12

0.499136091

88

13

0.066245996

89

14

0.448762051

89

15

0.415616568

86

16

0.201451838

87

17

0.713379055

20

18

0.963713668

27

19

0.60908696

25

20

0.832905927

48

21

0.735025279

38

22

0.282038857

80

23

0.893238908

13

24

0.507136129

88

Sheet1

Cell Formulas
Range	Formula
A1	=RAND()
C1	=IF(RANK(A1,$A$1:$A$24,0)>12,RANDBETWEEN(80,90),RANDBETWEEN(10,60))

Given that the RAND values generated in A are to 9 decimal places I have assumed the chances of duplicates as negligible?
If not the case then the RANK function will need to be modified to provide a tiebreak.

Hope that helps directly or indirectly.

mikerickson · Oct 19, 2014

Leave A1 blank,
put this in A2 and drag down to A25

=IF(RAND()<((12-COUNTIF($A$1:$A1,">70"))/(25-ROW(A1))), RANDBETWEEN(80,90), RANDBETWEEN(10,60))

25-ROW(A1) is 24,23,22,... then number of cells remaining to be chosen
COUNTIF(A$1:A1, ">70") is the number of preceding cells in the high range (between 80 and 90)

12-COUNTIF(..) is the number of remaining cells that have to be filled from the high range

(12-COUNTIF(...))/(25-ROW(A1)) is the percentage of remaining cells that have to be filled from the high range

IF(RAND()<((12-COUNTIF)/(25-ROW)), RAND(high), RAND(low)) makes a weighted choice between returning from the high range or the low range.

vizakenjack · Oct 19, 2014

Snakehips said:
It does require using a 'helper column' and I am unsure as to whether you will find that acceptable?

I'd rather try to have just one column for random numbers. Also, it's fine if some numbers are repeated.

Snakehips said:
The helper can be any column you care to use and can be outside your normal dat set and hidden if required.

Cell Formula
A1 =RAND()
C1 =IF(RANK(A1,$A$1:$A$24,0)>12,RANDBETWEEN(80,90),RANDBETWEEN(10,60))

<tbody>
</tbody>

<tbody>
</tbody>

Hm, if that

Rich (BB code):

RANK(A1,$A$1:$A$24,0)>12

Checks the cells above whether 12 of those cells happen to have values that lie between [10,60] - then it should surely help. I don't quite get what the "rank" function does, though...

mikerickson said:
Leave A1 blank,
put this in A2 and drag down to A25

=IF(RAND()<((12-COUNTIF($A$1:$A1,">70"))/(25-ROW(A1))), RANDBETWEEN(80,90), RANDBETWEEN(10,60))

25-ROW(A1) is 24,23,22,... then number of cells remaining to be chosen
COUNTIF(A$1:A1, ">70") is the number of preceding cells in the high range (between 80 and 90)

12-COUNTIF(..) is the number of remaining cells that have to be filled from the high range

(12-COUNTIF(...))/(25-ROW(A1)) is the percentage of remaining cells that have to be filled from the high range

IF(RAND()<((12-COUNTIF)/(25-ROW)), RAND(high), RAND(low)) makes a weighted choice between returning from the high range or the low range.

Well, if I copy it down in column B, this is what happens. More than 12 cells appear to have values between 80-90...

mikerickson · Oct 19, 2014

If you adjust the references to column B, it should work. The ROW(A1) is not column specific, but the range argument for COUNTIF has to match the column that the formulas are in.

vizakenjack · Oct 20, 2014

mikerickson said:
If you adjust the references to column B, it should work.

That's strange, but it doesn't really work.
Sometimes it will give more values to the range 80-90, sometimes to the range 10-60
sometimes it seems kinda in between.
It's not consistent...
Okay, I see what's wrong.
=COUNTIF($C$1:$C1,">70") returns zero.
My first cell (C1) is the text ("random numbers"), I tried to adjusting the formula to start it from C2, but it gives me an error ("circular reference").

mikerickson · Oct 20, 2014

The formula is correct, $C$1:$C1 should be the reference in C2.

$C$1:$C1 are the cells above the formula, which is what should be counted.

vizakenjack · Oct 20, 2014

ok nvm, figured it out.

checking each cell in an array whether it satisfies a condition

vizakenjack

New Member

Excel Facts

Snakehips

Well-known Member

mikerickson

MrExcel MVP

vizakenjack

New Member

mikerickson

MrExcel MVP

vizakenjack

New Member

mikerickson

MrExcel MVP

vizakenjack

New Member

Similar threads

Forum statistics

Share this page

checking each cell in an array whether it satisfies a condition

New Member

Excel Facts

Well-known Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock