Excel fu?

[CJones]

Anyone here with mad Excel fu willing to help me out on this?

I need to create a table of all possible combinations of two digits: ex: 4, 5

So for a 4x2 table it would be:
4 4
4 5
5 4
5 5

For a 8x3 it would be:
4 4 4
4 4 5
4 5 4
4 5 5
5 4 4
5 4 5
5 5 4
5 5 5

16x4
4 4 4 4
4 4 4 5
4 4 5 4
4 4 5 5
4 5 4 4
4 5 4 5
4 5 5 4
4 5 5 5
5 4 4 4
5 4 4 5
5 4 5 4
5 4 5 5
5 5 4 4
5 5 4 5
5 5 5 4
5 5 5 5

Etc. etc....

I need to be able to repeat this for thousands of possibilities, so I'm trying to find a way for Excel to do the math for me rather than me having to type it in manually. I will also have to change the digits for separate tables (3,6), (2,7), etc.

Any ideas?

 

[jason]

That makes my head hurt.

 

[ScottM]

Any ideas?

You are counting in a binary system. THAT IT IS A HINT ON THE SOLUTION

2x will tell you your matrix size

 

[gkainz]

1. open a bottle of good sippin' whiskey
2. sip until this crazy urge passes

 

[CJones]

You are counting in a binary system. THAT IT IS A HINT ON THE SOLUTION

2x will tell you your matrix size

Right. I can understand the binary stuff from my Computer Engineering days, but how the heck do I get Excel to do the busy work for me? Oh and by the way, each 'digit' needs to be in an individual column.

 

[CJones]

1. open a bottle of good sippin' whiskey
2. sip until this crazy urge passes

Unfortunately, no amount of ANYTHING will prevent the assignment from being due Thursday at 12:10pm. And no, the assignment is not to find a way to get Excel to do this (I'm not so lame as to ask someone to do my homework for me.... at least not this early in the semester) - the assignment is to do some different statistics on the numbers once they are entered. I'm guessing there is an 'easy' way to make Excel do this for me without having to manually do the binary breakdown myself.

 

[jesse]

I'm no excel badass--so I'd probably just generate a .CSV from php and load that into excel. Let me think about this for a few minutes.

 

[Capt. Geoffrey Thorpe]

Like this?

 

[CJones]

Like this?

That's getting close, but I'm still under the impression that there is a way to make Excel do it all from the start.

Here is the template for the project. I have everything filled out except the 'formula' areas, which should be used to fill their fields themselves. If push comes to shove, I can do this one by hand, but the prof has said that we will have a similar spreadsheet with a thousand or more fields later this semester, so I would like to find a shortcut now.

 

[jesse]

Okay. I got something that works. This is amazingly more hard to program then you'd expect. I set the max column width at 13--which will take the script several minutes to respond. 10 columns take 'bout a second.

It is amazing how fast it becomes slow. A change from 10 to 11 is a huge difference. If you really need to go wider then 13 let me know and I can do it more efficiently.

http://www.gastonsflyin.com/chris_fun/logic.php?width=5

Change the width number to increase the number of columns. Script should return a .csv you can save. If you need to go higher then 13 let me know--its just the way I'm doing it right now kills the servers CPU at high numbers. A width of 10 will be instant. A width of 13 will take several minutes.

I can change it so that you can change the numbers if that helps. Sorry I can't come up with an Excel way for you--I barely know how to use Excel.

 

[mantakos]

Sounds like fancy Excel, but grade school VBA. Do you know how to do VBA macros?

I'm not sure how you're intending to layout these tables in a spreadsheet. Just lay them across one after the other? Or one table per worksheet? Or one table only at a time? Or what?
-harry

 

[CJones]

Okay. I got something that works. How wide do you need to go in the excel file? My logic isn't that efficient and I don't feel like trying to rewrite it now. I set the max column width at 13--which will take the script several minutes to respond. 10 columns take 'bout a second.

It is amazing how fast it becomes slow. A change from 10 to 11 is a huge difference. If you really need to go wider then 13 let me know and I can do it more efficiently.

http://www.gastonsflyin.com/chris_fun/logic.php?width=10

Change the width number to increase the number of columns. Script should return a .csv you can save. If you need to go higher then 13 let me know--its just the way I'm doing it right now kills the servers CPU at high numbers. A width of 10 will be instant. A width of 13 will take several minutes.

Wow. Impressive! Thanks for the help! Unfortunately, it's not finding the arrangement that's the problem, it's getting Excel to put them in the correct columns easily. I have a feeling I'm making this more complicated than necessary. My work office is in the same building as the prof's, so I'll stick my head in tomorrow and see if I can get some hints on how to handle this. I'm not sure if I need to create a macro or if there is a way to manipulate a math formula to make it work.

 

[jesse]

Unfortunately, it's not finding the arrangement that's the problem, it's getting Excel to put them in the correct columns easily.

Hmm, what do you mean? If you open the csv in excel it will put each digit in a separate column.

 

[CJones]

Sounds like fancy Excel, but grade school VBA. Do you know how to do VBA macros?

I'm not sure how you're intending to layout these tables in a spreadsheet. Just lay them across one after the other? Or one table per worksheet? Or one table only at a time? Or what?
-harry

I attached the actual spreadsheet to another post, but here is a screenshot of what the template looks like.

FYI:
"Cycle Time" = max(info to left of that column)
"Station 4" column in the 'Four Station Line' (U8) table is: =IF(U7=4, 5, 4)

 

[CJones]

Hmm, what do you mean? If you open the csv in excel it will put each digit in a separate column.

See attached screenshot. I need to fill in the values for the 'Formula' cells and I need to show the formula that does it.

 

[Cap'n Jack]

The enclosed Zip file has an XL file with macros that I hope will do as you want.

To use-
Make sure the active sheet is clear

Use Menu item Tools, select Macros >, select Macros..., select Create Array and click the Run button

A dialog will appear. Enter the number of columns, and the two digits you want to alternate. Note- it you select more than 10 or 11 columns, this WILL take some time to run. The power of exponents, you know.

After the array is created, copy it wherever you need to. The array will start in cell A1 and go from there.

I apologize- I borrowed a long to binary convertor but I gave credit in the comments. I didn't have the time to hack my own this evening

Enjoy!

PS- After you understand the macro, give that to the prof for your formula.

 

[CJones]

The enclosed Zip file has an XL file with macros that I hope will do as you want.

To use-
Make sure the active sheet is clear

Use Menu item Tools, select Macros >, select Macros..., select Create Array and click the Run button

A dialog will appear. Enter the number of columns, and the two digits you want to alternate. Note- it you select more than 10 or 11 columns, this WILL take some time to run. The power of exponents, you know.

After the array is created, copy it wherever you need to. The array will start in cell A1 and go from there.

I apologize- I borrowed a long to binary convertor but I gave credit in the comments. I didn't have the time to hack my own this evening

Enjoy!

PS- After you understand the macro, give that to the prof for your formula.

Nice!

 

[mantakos]

See attached screenshot. I need to fill in the values for the 'Formula' cells and I need to show the formula that does it.

This sounds, to me, like you're not supposed to use VBA macros, but just Excel formulas.

The part I was puzzling over was how to put something in a cell that evaluates to a row or column index offset from the top of the table, but in your picture, I see a "Combination" column that provides this for rows, at least.

So it seems to me that you're supposed to come up with a formula for each column, and not necessarily a single formula for the whole table. If you put numerical column headings like 1, 2, 3, 4, then you could use a single formula for the whole thing.

In any case, given that you've already been given the row index in a nearby cell, then you can customize the formula for each column, and then it's a just a matter of doing some bitwise arithmetic.

For instance, if you look at the 3-column table, the formula for the leftmost column (i.e. column L in your picture) would be:

=(IF((MOD((INT(K8/(2^3))),2)),5, 4))
​

The "3" is the column number. It's 0 for the rightmost column, and increments by one for each column as you go to the left, so this is what varies from one column to the next. The 5 and 4 are the two digits that get put in the cells. The "K8" is the value from the "Combination" column, if you paste the formula into the rest of the column, this will be converted to L8, M8, N8, as appropriate.

If you break this down from inside-out, you have:

K8/(2^3)
​

This takes the "row offset" in the "Combination" column and divides by 8. For the next column, you'll change this to "2^2" to divide by 4, etc. From a bitwise arithmetic perspective, you've taken the bit you want to look at and put it into the least significant bit, the "ones" bit. In other words, we've shifted the number 3 bits to the right (if Excel supports bitwise AND, then there are easier ways to do this).

We then do "INT" on that, because otherwise Excel tries to do floating point arithmetic.

We then do MOD(..., 2). This just divides by two, then takes the remainder. You can also think of this as an "even or odd" test. This is a test of our least significant bit, our "ones" bit, effectively discarding all the more significant bits. We now have a value that's either 1 or 0.

We then do an IF(..., 5, 4) . If the results of our prior calculation are non-zero, then we spit out a 5. If they result in a zero, we spit out a 4.
-harry

 

[CJones]

This sounds, to me, like you're not supposed to use VBA macros, but just Excel formulas.

The part I was puzzling over was how to put something in a cell that evaluates to a row or column index offset from the top of the table, but in your picture, I see a "Combination" column that provides this for rows, at least.

So it seems to me that you're supposed to come up with a formula for each column, and not necessarily a single formula for the whole table. If you put numerical column headings like 1, 2, 3, 4, then you could use a single formula for the whole thing.

In any case, given that you've already been given the row index in a nearby cell, then you can customize the formula for each column, and then it's a just a matter of doing some bitwise arithmetic.

For instance, if you look at the 3-column table, the formula for the leftmost column (i.e. column L in your picture) would be:

=(IF((MOD((INT(K8/(2^3))),2)),5, 4))
​

The "3" is the column number. It's 0 for the rightmost column, and increments by one for each column as you go to the left, so this is what varies from one column to the next. The 5 and 4 are the two digits that get put in the cells. The "K8" is the value from the "Combination" column, if you paste the formula into the rest of the column, this will be converted to L8, M8, N8, as appropriate.

If you break this down from inside-out, you have:

K8/(2^3)
​

This takes the "row offset" in the "Combination" column and divides by 8. For the next column, you'll change this to "2^2" to divide by 4, etc. From a bitwise arithmetic perspective, you've taken the bit you want to look at and put it into the least significant bit, the "ones" bit. In other words, we've shifted the number 3 bits to the right (if Excel supports bitwise AND, then there are easier ways to do this).

We then do "INT" on that, because otherwise Excel tries to do floating point arithmetic.

We then do MOD(..., 2). This just divides by two, then takes the remainder. You can also think of this as an "even or odd" test. This is a test of our least significant bit, our "ones" bit, effectively discarding all the more significant bits. We now have a value that's either 1 or 0.

We then do an IF(..., 5, 4) . If the results of our prior calculation are non-zero, then we spit out a 5. If they result in a zero, we spit out a 4.
-harry

I think you nailed it. I haven't tried to implement it yet, but the logic seems sound after a quick read-through. I knew there had to be some 'simple' way to have Excel do this, but being new to Excel formulation, I haven't been able to get all the possibilities straightened out.

Thanks!

 

[Cap'n Jack]

Harry-

Looks very elegant. After getting more than 11 or 12 columns wide, it is likely to be faster than the program too.