Math problem ~ ~ ~ a little help ~ please . . .

mart

ChrisMusic said:

...

If it is a formula it needs to return a line with one instance of each variable in a sequence containing the full set.

(that is the purpose of the lookup, which is just my iterative approach to solving this problem)

...

If I understand this right, you want a function that takes a number (eg the row number) in the range of 1 to 24! and you want a formula that takes that number and produces a permutation of 24 variables, in such a way that each number gives a different permutation.

If that's right, then it can just about be done in Excel. Take the remainder after dividing your number by 24 (the function MOD(-,24)); this gives the first variable in the list. Next subtract that remainder, divide the result by 24, and take the remainder after dividing that by 23. This gives the second variable, unless this number is greater than or equal to the first number you found, in which case you need to increase the index by 1. Carry on!

So then you can pick a random number and get a random permutation. I seriously wouldn't try to generate a lookup table with 24! rows.

mart

Scrub that. Fisher-Yates is probably much more useful!

scrumhalf

Flawed?

You may be looking at it from too few angles.

Excel is a perfect tool for creating financial models, and for financial and operational reporting.

crunchman

You don't get locked out of Excel by bureaucratic corporate IT policies.  I've done stuff in Excel that should never have been done in Excel.  I've put in a month's work to build some VBA monstrosity that I could have done in a week with much better functionality in a database.

The other alternative is to get IT involved, and end up with a project costing £30k or more by the time they write all the specs, pay someone to code it, pay to test that it doesn't break the network etc. You've also got to get a senior manager to sign off on a £30k outlay that they don't have the budget for, so you end up bodging it in Excel VBA.

Axe_meister

But if you are bodging in excel with VBA why not download visual studio express for free (comes with a cutdown version of sqlserver) and do it properly. Either in VB or .net. If it's .net it can even we portable to a server/web environment.

Even Access is better than bending excel to be a database

ChrisMusic

An interesting little website, and one I used previously to generate partial tables for a similar exercise.  The chances of duplicate entries over a few thousand data sets are pretty minimal with such astronomical permutations available.  But I am now looking for more robust and automatic ways of achieving the results I am after.

http://www.random.org

crunchman

Axe_meister said:

But if you are bodging in excel with VBA why not download visual studio express for free (comes with a cutdown version of sqlserver) and do it properly. Either in VB or .net. If it's .net it can even we portable to a server/web environment.

Even Access is better than bending excel to be a database

Can't even use Access at our place.  I can personally use Access for historic reasons, but IT point blank refuse to give Access to anyone new.  I've had to convert something from an Access database into Excel. Access isn't ideal but was a lot better than Excel for what it was doing.

As for downloading Visual Studio Express, IT would have kittens.  Actually I'd be blocked from downloading it in the first place.

There are backdoor ways to run Powershell scripts which helps sometimes as a way around VBA.  There was something my boss wrote in Excel VBA that used to take over an hour to run that I got down to 5 minutes by using Powershell.

The problem is that I am trying to process and manipulate very large quantities of engineering data from an automated recording system, and I'm in engineering, not sitting in IT, so we are blocked from using a lot of useful stuff.  It's probably partly the public sector bureaucracy monster.  I can't imagine that a private sector enterprise with a grotesquely inefficient IT bureaucracy like ours would last all that long.

The main database and viewing software that we use is very specialist and was written by an engineering company, not a software house, and it has it's flaws.  It doesn't handle bad data very well.  We are needing to write code to validate data files, and investigate problems with the data.  We've got scripts we run every day on the data files to validate them before we upload them into the main database.

To be honest, I quite like my job.  I like writing code, but I think I'd get bored writing it full time.

ChrisMusic

Well coffee break over, so it's been down to work with this.

Firstly thanks for pointing out some ideas for getting rid of grid lines, @viz's idea of using cell backgrounds gives me the opportunity to mix clear visual areas for presentation and grid lined areas for workings, so that's a flexible solution for me.

I have also stumbled across the tabbed pallets while working with formulas, well hidden under the toolbox icon, which is not a Mac intuitive way to do things, but thanks for pointing it out @mart and viz, or I would never have gone on the hunt, so I can now switch them off at will.

The best I could have hoped for was a partial table, which isn't really the best approach, but I have successfully used it before in a similar situation.

Good input from every one so far, with some great links too, thanks @Axe_meister

Spot on with the suggestion of looking at Fisher-Yates @UnclePsychosis , a good approach.  I have cobbled a solution together using a two step iterative process based on this which is pretty inelegant but seems to work.  It is largely based on logic arguments to do the spatial manipulations with a bit of maths thrown in too.  So individual tools created for 4, 5, 6, 7, 8, 12 and 24 variable sets.  Excel can't deal with it in the files original format, too many nested arguments I guess, so that's opened another can of worms.

Does anyone have a better idea of how I could approach it in Excel ?  It would be nice to resolve it a bit more elegantly.

I based my current approach on Durstenfeld's modern version.

Can anyone advise me what the impact will be of not using a swap, and just reducing the data set instead, would be.  If you are starting each iteration with a random number I can't see that there would be any issue, but maybe I have missed something more fundamental ?

Wikipedia.org ~ Fisher–Yates shuffle

A million thanks, (1E+06 or ≈3*9! thanks)  ~  I could never have done this without your help  

viz

ChrisMusic

Oh, I should have mentioned it before

Baby's first word:

5 7 20 6 2 11 22 9 12 21 13 16 1 19 15 3 24 4 17 10 14 8 23 18

She's working fine I reckon, cheers all    

ChrisMusic

I really do find it both extraordinary and counter intuitive that the number sequence above, with just 24 instances can give rise to such astronomical permutations.

Baby's first word above is one of 600,000,000,000,000,000,000,000 possible unique combinations, that is 6 followed by 23 zeros ~ wow !

That is 24! (factorial) or using scientific notation 6.20448E+23 and can also be written as 6x10^23,

which is 600 sextillion, or 6 hundred thousand billion billion.  I am glad I dropped the table idea !

For the curious: see here for the names of large numbers  http://en.wikipedia.org/wiki/Names_of_large_numbers

A couple of other weird maths-ish type thingies to get you thinking, in previous discussions on here ~ if you are interested

Fifty seven octaves below middle C

http://thefretboard.co.uk/discussion/15672/fifty-seven-octaves-below-middle-c#latest

A curio patterns, maths, music

http://thefretboard.co.uk/discussion/15358/a-curio-patterns-maths-music#latest