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

ChrisMusic

Can any of you kind and clever souls out there in tFB land help me with this please ?

I want to create a table in Excel (2008 version) to hold all the permutations for 24 variables.

Any advice on how to do that would be appreciated.

If anyone knows how to automatically populate it, that would be fantastic.

Or even how to ease the burden of doing it manually.

I can't even remember how to calculate how many permutations that is, so a reminder of that would be helpful too.

The idea is to use the table as a source for lookups, either specific or randomised ones.

That may not be the best solution if I can create a fairly simple formula in Excel to do this on an "on demand" basis, so again if anyone has an idea how to do that, or how to approach it that would be great.

And one other Excel thing, how do you switch off the cell borders for screen display, so that all you see is a blank white space with just the numbers and text showing ?

I really don't know how to drive Microsoft software very well, just as well I don't need a license and MOT.  

Axe_meister

VBA is one way of doing it. Otherwise look at array based formula.

p90fool

I can help, it's M.A.T.H.S.

Any time

mike_l

p90fool said:

I can help, it's M.A.T.H.S.

Any time

Not if you're A.M.E.R.I.C.A.N.

 

dafuzz

If I'm reading you right, you need to find 24! (24 factorial) combinations. That's a pretty big spreadsheet.

equalsql

dafuzz said:

If I'm reading you right, you need to find 24! (24 factorial) combinations. That's a pretty big spreadsheet.

  That's an understatement

6.20448402e23  combinations...

ChrisMusic

Thanks @Axe_meister, I am out of my depth with that, could you furnish a little further explanation please  

 

Hey @p90fool, I thought (honest I did) when I wrote the title, what currency do we use for this ?  Is it traditional UK English, or that there American flavour ?  It was always called "maths" when I was growing up, but I am never too sure how far the annexation of our mother tongue has gone.  M.A.T.H.S. ?   I have seen it referred to as "math" so many times in recent years, I was just trying to be trendy !   Well there has to be a first time    >-)

UnclePsychosis

When you say permutations of 24 variables... What permutations are allowed? You're going to have to be more specific.

VimFuego

it's maths. If you want to be really clever, you could mathematics. Would you ever say mathematic?

ChrisMusic

That's what I was thinking @dafuzz, but it is a *very * long time since I have done any of that.  Thanks for reminding me, much appreciated.  I was swaying between n! and n^2-n, but the grey cells just didn't want to cooperate, neither of them.

@equalsql  "6.20448402e23  combinations..."   ~   Oh YES !      \m/

Axe_meister

VBA is visual basic for applications.

Array based formula is best explained here

http://www.cpearson.com/excel/arrayformulas.aspx

Took me ages to get my head around them. You you can create some quite interesting dynamic spreadsheets with them.

I personally think Excel needs an overhaul. 

Would love to have a proper formula editor per cell, with a bit of additional logic (like loops and things), but no have to branch out into VBA

p90fool

ChrisMusic said:

Thanks @Axe_meister, I am out of my depth with that, could you furnish a little further explanation please  

 

Hey @p90fool, I thought (honest I did) when I wrote the title, what currency do we use for this ?  Is it traditional UK English, or that there American flavour ?  It was always called "maths" when I was growing up, but I am never too sure how far the annexation of our mother tongue has gone.  M.A.T.H.S. ?   I have seen it referred to as "math" so many times in recent years, I was just trying to be trendy !   Well there has to be a first time    >-)

Lol I'm just kidding, I spend half my life on US-based fora and have to remind myself how to speak when I'm on here, and anyway I'm not even clever enough to have the vaguest idea what you were talking about!

equalsql

ChrisMusic said:

That's what I was thinking @dafuzz, but it is a *very * long time since I have done any of that.  Thanks for reminding me, much appreciated.  I was swaying between n! and n^2-n, but the grey cells just didn't want to cooperate, neither of them.

@equalsql  "6.20448402e23  combinations..."   ~   Oh YES !      \m/

620448402000000000000000 
X_X

ChrisMusic

If it is a lookup table it needs to contain *all* the permutations @UnclePsychosis.

So that every line contains the full set of 24, but is in a different order to every other line.

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)

I am not sure whether I have described the problem properly, with most things in life, the answers are comparatively simple, the difficult part is framing the right question*.  ( *See Douglas Adams and Deep Thought )

@equalsql - The Excel workbook so far has a dedicated page which has 7.94968E+25 potential combinations - that's 80 Octillion.

Luckily I only need to use a randomised sample of those   

Axe_meister

If you only need a randomised sample, why not just create a routine that generates a random permutation.

I think VBA will be your savour.

http://www.get-digital-help.com/2011/02/03/excel-udf-list-permutations-without-repetition/

UnclePsychosis

ChrisMusic said:

If it is a lookup table it needs to contain *all* the permutations @UnclePsychosis.

So that every line contains the full set of 24, but is in a different order to every other line.

Right, so what you want is to generate all of the permutations of choosing exactly 24 objects, but in any order? Thats 24! which is a huge number. 

If you only need a subset of the permutations then better just to generate a random selection each time you need one. 

ChrisMusic

@Axe_meister said:

VBA is visual basic for applications.

Array based formula is best explained here

http://www.cpearson.com/excel/arrayformulas.aspx

Took me ages to get my head around them. You you can create some quite interesting dynamic spreadsheets with them.

I personally think Excel needs an overhaul. 

Would love to have a proper formula editor per cell, with a bit of additional logic (like loops and things), but no have to branch out into VBA

Thanks for that  Axe_meister  .  I thought you meant Visual Basic, I had a brief flirtation with it back in the nineties, but any recollection is pretty much lost in the mists of time.  I don't think it is even available for the Mac, it wasn't back then, so I never followed up on learning it.

As for Excel, well my controversial view is that it should probably have been shot at birth, along with the ensuing development teams.  Back in 1991 I bought a new Apple Mac Quadra, and at the Mac Expo Lotus were showing 123 elegantly ported to Mac OS.  I had used Lotus 123 and jumped at the chance of having a good spreadsheet for the Mac, Microsoft were steering clear of the whole platform back then IIRC.

I used Lotus right up into the early noughties, yes that well out of date version as they never had the uptake to warrant upgrading it.  I was gutted at the downgrade to Excel, which I had to do when OSX finally stopped supporting 68xxx code.

Excel + Love + Lost = NO !

I will check out the link you posted, thanks very much for that, it should keep me out of mischief for the afternoon.

Axe_meister

I actually think the concept of a spreadsheet full stop is flawed.

Create a proper database and proper programmatic logic. I've learnt this the hard way.

ChrisMusic

@UnclePsychosis said:

ChrisMusic said:

If it is a lookup table it needs to contain *all* the permutations @UnclePsychosis.

So that every line contains the full set of 24, but is in a different order to every other line.

Right, so what you want is to generate all of the permutations of choosing exactly 24 objects, but in any order? Thats 24! which is a huge number. 

If you only need a subset of the permutations then better just to generate a random selection each time you need one. 

That was the idea of using a table look-up, to randomise the results on look-up, with the table holding the permutations.  Probably not a very elegant solution I know.

"If you only need a subset of the permutations then better just to generate a random selection each time you need one."

Do you have any idea on how create a formula to do that, or how to approach it ?

UnclePsychosis

I don't know how to do it in excel, I'd use a real programming language or at the very least matlab.

As a general approach what you want to do is implement a fisher-Yates (knuth) shuffle. Google will get you loads of help on this.

If you need each time you do it to be unique then you probably need to keep a list of previously used permutations.

UnclePsychosis

Axe_meister;375746" said:

I actually think the concept of a spreadsheet full stop is flawed.Create a proper database and proper programmatic logic. I've learnt this the hard way.

I disagree. Spreadsheets are invaluable for basic tasks. People try and do too much with Excel but that doesn't mean spreadsheets themselves are flawed.