Disregard the earlier which was wrong. Sorry ..

Formula in A5 should read as:
=INDEX($A$1:$D$1,MATCH(RAND(),$A$3:$D$3,1))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max...is there a way that I could by pass sorting the percentages from low to
high?

Thanks for this, as it getting closer for what I'm looking for...

"Max" wrote:

Disregard the earlier which was wrong. Sorry ..

Formula in A5 should read as:
=INDEX($A$1:$D$1,MATCH(RAND(),$A$3:$D$3,1))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"mslabbe"wrote:
Thanks for this, as it getting closer for what I'm looking for...

Good to hear that ..

is there a way that I could
by pass sorting the percentages from low to high?

Not versed in vba, sorry.
Maybe others will jump in here.

Using formulas, I could try this set up ..

Assuming source data in Sheet1's rows1 and 2, from col A across to col IV,
fruits in A1 across, corresponding percentages in A2 across

In another sheet,

In A1:
=IF(Sheet1!A2="","",Sheet1!A2+COLUMN()/10^10)

In A2:
=IF(COLUMN()COUNT($1:$1),"",INDEX(Sheet1!1:1,SMAL L($1:$1,COLUMN())))

Copy A2 down to A3. Select A1:A3, copy across to IV3. Hide away row1. Rows 2
& 3 returns the required ascending auto-sort (left to right) of Sheet1's
fruits & percents.

Then just set it up as before ..

Enter a zero in A4
Put in B4: =SUM($A$3:A3)
Copy B4 across to IV4

Place in any cell, say in A5:
=INDEX(2:2,MATCH(RAND(),4:4,1))
to generate the "weighted" random draw
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Correction to formula:

In A2:
=IF(COLUMN()COUNT($1:$1),"",INDEX(Sheet1!1:1,SMAL L($1:$1,COLUMN())))

Should be:
=IF(COLUMN()COUNT($1:$1),"",INDEX(X!1:1,MATCH(SMA LL($1:$1,COLUMN()),$1:$1,0)))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Errata (wrong source sheetname used earlier):

In A2 should be:
=IF(COLUMN()COUNT($1:$1),"",INDEX(Sheet1!1:1,MATC H(SMALL($1:$1,COLUMN()),$1:$1,0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

There is no need to sort the percentages before using your approach of
accumulating the percentages as long as they add up to 1.

A play off your formula

=INDEX(A1:D1,1,MATCH(RAND(),CHOOSE(ROW(1:5),0,SUM( $A$2:$A$2),SUM($A$2:$B$2),SUM($A$2:$C$2),SUM($A$2: $D$2))))

array entered, works and the percentages are not sorted.


I had 40%, 10%, 30%, 10% and got consistent results with this test macro:

Sub abcd()
Dim v(1 To 4)
maxVal = 1000
For i = 1 To maxVal
ActiveSheet.Calculate

Select Case Range("F2").Value
Case "Pears" ' 40%
v(1) = v(1) + 1
Case "Apples" ' 10%
v(2) = v(2) + 1
Case "Peaches" ' 30%
v(3) = v(3) + 1
Case "Bananas" ' 20%
v(4) = v(4) + 1
End Select
Next
For i = 1 To 4
vsum = vsum + v(i)
v(i) = v(i) / maxVal
Cells(1, 10 + i) = v(i)
Next
Cells(1, 10 + 6) = vsum
End Sub


--
Regards,
Tom Ogilvy



"Max" wrote in message
...
"mslabbe"wrote:
Thanks for this, as it getting closer for what I'm looking for...

Good to hear that ..

is there a way that I could
by pass sorting the percentages from low to high?

Not versed in vba, sorry.
Maybe others will jump in here.

Using formulas, I could try this set up ..

Assuming source data in Sheet1's rows1 and 2, from col A across to col IV,
fruits in A1 across, corresponding percentages in A2 across

In another sheet,

In A1:
=IF(Sheet1!A2="","",Sheet1!A2+COLUMN()/10^10)

In A2:
=IF(COLUMN()COUNT($1:$1),"",INDEX(Sheet1!1:1,SMAL L($1:$1,COLUMN())))

Copy A2 down to A3. Select A1:A3, copy across to IV3. Hide away row1. Rows
2 & 3 returns the required ascending auto-sort (left to right) of Sheet1's
fruits & percents.

Then just set it up as before ..

Enter a zero in A4
Put in B4: =SUM($A$3:A3)
Copy B4 across to IV4

Place in any cell, say in A5:
=INDEX(2:2,MATCH(RAND(),4:4,1))
to generate the "weighted" random draw
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Tom,
Played with your array formula. Manually pressed F9 repetitiously.
It occasionally returns #REF! ?
I'm not sure whether you got this observation
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tom Ogilvy" wrote in message
...
There is no need to sort the percentages before using your approach of
accumulating the percentages as long as they add up to 1.

A play off your formula

=INDEX(A1:D1,1,MATCH(RAND(),CHOOSE(ROW(1:5),0,SUM( $A$2:$A$2),SUM($A$2:$B$2),SUM($A$2:$C$2),SUM($A$2: $D$2))))

array entered, works and the percentages are not sorted.


I had 40%, 10%, 30%, 10% and got consistent results with this test
macro:

Sub abcd()
Dim v(1 To 4)
maxVal = 1000
For i = 1 To maxVal
ActiveSheet.Calculate

Select Case Range("F2").Value
Case "Pears" ' 40%
v(1) = v(1) + 1
Case "Apples" ' 10%
v(2) = v(2) + 1
Case "Peaches" ' 30%
v(3) = v(3) + 1
Case "Bananas" ' 20%
v(4) = v(4) + 1
End Select
Next
For i = 1 To 4
vsum = vsum + v(i)
v(i) = v(i) / maxVal
Cells(1, 10 + i) = v(i)
Next
Cells(1, 10 + 6) = vsum
End Sub


--
Regards,
Tom Ogilvy