checking each cell in an array whether it satisfies a condition

[vizakenjack]

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

=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

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

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]

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]

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.

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

[TABLE="width: 85%"]
<tbody>[TR]
[TD][TABLE="width: 100%"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]A1[/TH]
[TD="align: left"]=RAND()[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C1[/TH]
[TD="align: left"]=IF(RANK(A1,$A$1:$A$24,0)>12,RANDBETWEEN(80,90),RANDBETWEEN(10,60))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hm, if that

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

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]

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]

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]

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]

ok nvm, figured it out.