MS have been playing with a new “dynamic array” feature in Excel for some time.
It looks like it is now on general issue for Office 365 subscribers.
Anyone else got in in their Excel?
(If you type =Sort and get Sort and Sortby as available functions, you have dynamic arrays as well).
Not that I use excel for anything but basic features, but having a look I don’t even have the “=Sort” option available.
The Rev Dodgson said:
MS have been playing with a new “dynamic array” feature in Excel for some time.It looks like it is now on general issue for Office 365 subscribers.
Anyone else got in in their Excel?
(If you type =Sort and get Sort and Sortby as available functions, you have dynamic arrays as well).
Yes Blue leader I get Sort and Sortby.
What next? cut the red wire?
Over.
sibeen said:
Not that I use excel for anything but basic features, but having a look I don’t even have the “=Sort” option available.
That means you don’t have the dynamic arrays yet.
The Rev Dodgson said:
sibeen said:
Not that I use excel for anything but basic features, but having a look I don’t even have the “=Sort” option available.
That means you don’t have the dynamic arrays yet.
Peak Warming Man said:
The Rev Dodgson said:
MS have been playing with a new “dynamic array” feature in Excel for some time.It looks like it is now on general issue for Office 365 subscribers.
Anyone else got in in their Excel?
(If you type =Sort and get Sort and Sortby as available functions, you have dynamic arrays as well).
Yes Blue leader I get Sort and Sortby.
What next? cut the red wire?
Over.
OK, cut the red wire, whatever that does.
You could also try entering a list of random numbers in say A1:A10, then enter = sort(A1:A10)
The Rev Dodgson said:
sibeen said:
Not that I use excel for anything but basic features, but having a look I don’t even have the “=Sort” option available.
That means you don’t have the dynamic arrays yet.
Damn, that means I won’t be able to use them
kicks dirt
Tamb said:
The Rev Dodgson said:
sibeen said:
Not that I use excel for anything but basic features, but having a look I don’t even have the “=Sort” option available.
That means you don’t have the dynamic arrays yet.
My Excel 2016 seems to not have it.
No, I’m pretty sure it’s only Office365, which updates itself every month without asking.
The Rev Dodgson said:
Tamb said:
The Rev Dodgson said:That means you don’t have the dynamic arrays yet.
My Excel 2016 seems to not have it.No, I’m pretty sure it’s only Office365, which updates itself every month without asking.
While on the topic of Excel.
The Rev Dodgson said:
mollwollfumble said:Now shuffling in Fortran – still a couple of bugs in there.
Enjoying this video – five false card shuffles. Five ways where it looks like a card shuffle but isn’t.
https://www.youtube.com/watch?v=PfbOELSTyP0
HTF can it be easier to code shuffling in Fortran than doing it in Excel?
I’d like you to answer that.
Here is the first part of the Fortran code. What would this code in Fortran look like in Excel? I tried to do this in Excel and failed.
ncard=52
do nc=1,ncard
ltaken(nc)=.false. ! value has not already been used
end do
do nc=1,ncard ! generate permutation in reduced form
ireduced(nc)=ceiling((ncard+1-nc)*rand()) ! generates in turn a random integer over ncard, ncard-1, ..., 1
end do
do nc=1,ncard ! expand reduced form to full permutation
irandom(nc)=0
do ir=1,ireduced(nc)
irandom(nc)=irandom(nc)+1
5 continue
if(ltaken(irandom(nc))) then
irandom(nc)=irandom(nc)+1
goto 5
end if
end do
ltaken(irandom(nc))=.true.
end do
?
OK, to do it on the worksheet without VBA:
Enter 1 and 2 in row 1 and 2 of any column.
Drag down to get a list of integers 1 to 52
In Row 1 of the adjacent column enter =Rand()
Double click on the bottom right hand corner to fill rows 1 to 52 with random numbers.
Sort both columns using the random number column as the key.
To do that from VBA:
Set up a spreadsheet as above.
Name the two column range “ShuffleRange” and the second column “RandRange”
Enter VBA code:
Sub Shuffle()
Dim ShuffleRange As Variant, Cards(1 To 52) As Long, i As Long
Save the worksheet as xlsm or xlsb.
Its probably even easier with the new sort function, but that won’t yet work with most versions of Excel.
You could also do it entirely from VBA using a VBA sort function. There isn’t a built in one, but you can download a free one from my blog if you are interested.
I don’t know how to get the line breaks to work.
Let me know if you’d like a link to the spreadsheet.
The Rev Dodgson said:
OK, to do it on the worksheet without VBA:Enter 1 and 2 in row 1 and 2 of any column.
Drag down to get a list of integers 1 to 52
In Row 1 of the adjacent column enter =Rand()
Double click on the bottom right hand corner to fill rows 1 to 52 with random numbers.
Sort both columns using the random number column as the key.To do that from VBA:
Set up a spreadsheet as above.
Name the two column range “ShuffleRange” and the second column “RandRange”Enter VBA code:
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Clear ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add2 Key:=Range(“RandRange”) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets(“Sheet1”).Sort .SetRange Range(“ShuffleRange”) .Apply End With ShuffleRange = Range(“ShuffleRange”).Value2 For i = 1 To 52 Cards(i) = ShuffleRange(i, 1) Next i End Sub
Sub Shuffle()
Dim ShuffleRange As Variant, Cards(1 To 52) As Long, i As LongSave the worksheet as xlsm or xlsb.
Its probably even easier with the new sort function, but that won’t yet work with most versions of Excel.
You could also do it entirely from VBA using a VBA sort function. There isn’t a built in one, but you can download a free one from my blog if you are interested.
Oh, I see what you mean, sort by random number, That’s quite clever, I hadn’t thought of that.
mollwollfumble said:
The Rev Dodgson said:
OK, to do it on the worksheet without VBA:Enter 1 and 2 in row 1 and 2 of any column.
Drag down to get a list of integers 1 to 52
In Row 1 of the adjacent column enter =Rand()
Double click on the bottom right hand corner to fill rows 1 to 52 with random numbers.
Sort both columns using the random number column as the key.To do that from VBA:
Set up a spreadsheet as above.
Name the two column range “ShuffleRange” and the second column “RandRange”Enter VBA code:
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Clear ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add2 Key:=Range(“RandRange”) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets(“Sheet1”).Sort .SetRange Range(“ShuffleRange”) .Apply End With ShuffleRange = Range(“ShuffleRange”).Value2 For i = 1 To 52 Cards(i) = ShuffleRange(i, 1) Next i End Sub
Sub Shuffle()
Dim ShuffleRange As Variant, Cards(1 To 52) As Long, i As LongSave the worksheet as xlsm or xlsb.
Its probably even easier with the new sort function, but that won’t yet work with most versions of Excel.
You could also do it entirely from VBA using a VBA sort function. There isn’t a built in one, but you can download a free one from my blog if you are interested.
Oh, I see what you mean, sort by random number, That’s quite clever, I hadn’t thought of that.
Next challenge – unshuffle.
Given a random permutation, find the minimum number of overhand shuffles needed to put it back into order. Each overhand shuffle is a collection of non-overlapping cuts.
Could you do an approximate random overhand shuffle in excel? Use a random number to get the number of cards to the next cut?
Hmm, a sort of approximate cheat-way to do that would be to start by doing random shuffles in turn until the second order statistics (autocorrelation function in technical terms) match that of a random permutation. But that’s not really good enough because it’s a waste to have one shuffle partially undo previous shuffles.
I’ll have to think about the unshuffled.
Working on my bi-axial bending spreadsheet at the moment.
mollwollfumble said:
Next challenge – unshuffle.
Given a random permutation, find the minimum number of overhand shuffles needed to put it back into order. Each overhand shuffle is a collection of non-overlapping cuts.
Could you do an approximate random overhand shuffle in excel? Use a random number to get the number of cards to the next cut?
Hmm, a sort of approximate cheat-way to do that would be to start by doing random shuffles in turn until the second order statistics (autocorrelation function in technical terms) match that of a random permutation. But that’s not really good enough because it’s a waste to have one shuffle partially undo previous shuffles.
I think I must be missing something here. Surely any form of random shuffling will need so many iterations to get back to starting order that effectively it would never happen?
But I think the shuffling process would be fairly easy. Use Randbetween to generate a number between 1 and 51 (or whatever limits you want), and reorder the pack with a for loop in VBA or using Index on the spreadsheet.
The Rev Dodgson said:
mollwollfumble said:Next challenge – unshuffle.
Given a random permutation, find the minimum number of overhand shuffles needed to put it back into order. Each overhand shuffle is a collection of non-overlapping cuts.
Could you do an approximate random overhand shuffle in excel? Use a random number to get the number of cards to the next cut?
Hmm, a sort of approximate cheat-way to do that would be to start by doing random shuffles in turn until the second order statistics (autocorrelation function in technical terms) match that of a random permutation. But that’s not really good enough because it’s a waste to have one shuffle partially undo previous shuffles.
I think I must be missing something here. Surely any form of random shuffling will need so many iterations to get back to starting order that effectively it would never happen?
But I think the shuffling process would be fairly easy. Use Randbetween to generate a number between 1 and 51 (or whatever limits you want), and reorder the pack with a for loop in VBA or using Index on the spreadsheet.
Ta. I hadn’t heard of Randbetween.