Can you recreate this formula using RAND instead of RANDBETWEEN?

[fishoutofwater]

I have this array formula (thanks to the wonderful mr. excel user Yahya-- thanks!)) and I am sure it is exactly what I want. But the problem is that it uses RANDBETWEEN and because I amusing excel2002 I only have RAND and not RANDBETWEEN.

Is there a way to modify this Array Formula so it uses RAND, and not RANDBETWEEN so I can use it with the excel that I have?

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

(...and here is the orginal thread that describes what I am trying to acomplish: http://www.mrexcel.com/forum/showthread.php?p=2818694#post2818694 )

 

[fishoutofwater]

Wow. Perfect! Thank you so much again markmzz. I wish you lived in Portland, OR. so I could buy you a beer for all of your generous help!

 

[markmzz]

I little modification (the previous formula have a error):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">75</td><td style="text-align: right;;">44</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">79</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">82</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">2</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2</td><td style="text-align: right;;">44</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">2</td><td style="text-align: right;;">75</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Randbetween</td><td style="text-align: center;;">Rand</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet71</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E1</th><td style="text-align:left">{=INDEX(<font color="Blue">B$1:B$9,LARGE(<font color="Red">IF(<font color="Green">(<font color="Purple">A$1:A$9=D$1</font>)*(<font color="Purple">B1:B9<>0</font>),ROW(<font color="Purple">A$1:A$9</font>)-ROW(<font color="Purple">A$1</font>)+1,0</font>),RANDBETWEEN(<font color="Green">1,SUM(<font color="Purple">(<font color="Teal">A1:A9=D1</font>)*(<font color="Teal">B1:B9<>0</font>)</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F1</th><td style="text-align:left">{=INDEX(<font color="Blue">B$1:B$9,LARGE(<font color="Red">IF(<font color="Green">(<font color="Purple">A$1:A$9=D$1</font>)*(<font color="Purple">B1:B9<>0</font>),ROW(<font color="Purple">A$1:A$9</font>)-ROW(<font color="Purple">A$1</font>)+1,0</font>),ROUND(<font color="Green">RAND(<font color="Purple"></font>)*(<font color="Purple">SUM(<font color="Teal">(<font color="#FF00FF">A1:A9=D1</font>)*(<font color="#FF00FF">B1:B9<>0</font>)</font>)-1</font>)+1,0</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[T. Valko]

A quick follow up question to this. If I am usig markmzz's solution (thank you so much markmzz!) and I want to ignore all 0 values in B, how would I modify the array formula? So from below...

I want to modify this array formula:
<TABLE style="BORDER-RIGHT: rgb(166,170,182) 1px solid; BORDER-TOP: rgb(166,170,182) 1px solid; BORDER-LEFT: rgb(166,170,182) 1px solid; BORDER-BOTTOM: rgb(166,170,182) 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: rgb(255,255,255); TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><TBODY><TR><TR><TD style="TEXT-ALIGN: left">{=INDEX($B$1:$B$9,ROUND(RAND()*(MATCH(1,IF($A$1:$A$9=C1,1))-MATCH(C1,$A$1:$A$9,0))+MATCH(C1,$A$1:$A$9,0),0))}</TD></TR></TR></TBODY></TABLE>such that it won't ever return a "0" In other words, since C2 is a "2" I want a random selection from the B column from all the rows that have a "2" in column A (ie, a random pick from A4,A5,A6, A7, A8, A9) but ignoring all 0 value possibilities in column B-- ie, a random pick from only B4, B7, B8, B9.

More simply, this array formula filters the output by column A. I need to add something to it to also filter out 0's from column B.

Try this array formula**:

=INDEX(B3:B11,LARGE(IF((A3:A11=D3)*(B3:B11<>0),ROW(A3:A11)-ROW(A3)+1),INT(1+SUM((A3:A11=D3)*(B3:B11<>0))*RAND())))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[fishoutofwater]

Thanks Biff!

 

[T. Valko]

Thanks Biff!

You're welcome!