Author |
Topic: Urgent Excel (Read 4361 times) |
|
fatball
Senior Riddler
Can anyone help me think outside the box please?
Gender: 
Posts: 315
|
 |
Urgent Excel
« on: May 8th, 2017, 10:44am » |
 Quote  Modify
|
Assume I have a set of 10 different numbers and I want to obtain a sum for each combination of 2 to 9 numbers. How can I do it quickly with Excel please?
|
|
 IP Logged |
|
|
|
dudiobugtron
Uberpuzzler
Posts: 735
|
|
Re: Urgent Excel
« Reply #1 on: May 8th, 2017, 12:22pm » |
Quote Modify
|
The set of all the different combinations is called the 'powerset' - you basically need to generate the powerset and then work out the sum of each element of it. There are over 1000 possible combinations you will need to add, so doing it manually would probably be too hard. (The powerset has 1024 elements but you only need 1012 of them.)
Excel doesn't have a powerset function, but if you know how to use Visual Basic for Applications in Excel, then people have written some code to do that:
http://www.tushar-mehta.com/excel/tips/powerset.html
And here is some info about using VBA in Excel:
http://www.excel-easy.com/vba.html
Sorry I couldn't be more help than that! I haven't actually done this myself, so I can't give you specific tips for your situation. You might be able to just write your own code to generate the powerset in a format that is easy to copy and paste into excel.
|
| « Last Edit: May 8th, 2017, 12:29pm by dudiobugtron » |
IP Logged |
|
|
|
fatball
Senior Riddler
Can anyone help me think outside the box please?
Gender:
Posts: 315
|
|
Re: Urgent Excel
« Reply #2 on: May 8th, 2017, 12:29pm » |
Quote Modify
|
No, your advice is very helpful, I can write VBA codes. Let me give it a try and see if it works. Thanks.
|
|
IP Logged |
|
|
|
towr
wu::riddles Moderator
Uberpuzzler
Some people are average, some are just mean.
Gender:
Posts: 13730
|
|
Re: Urgent Excel
« Reply #3 on: May 8th, 2017, 12:49pm » |
Quote Modify
|
You can also do it with copy-pasting ten times
start with two empty rows
put the 10th number in the 10th column of the first row
copy all rows, paste them under existing rows.
put the 9th number in the 9th column of the pasted rows.
copy all rows, paste them under existing rows.
put the 8th number in the 8th column of the pasted rows.
etc
make the 11th column the sum of the previous 10.
eliminate the rows with exactly 1 or ten numbers.
You can probably also make all the numbers variables instead so you can fill in any set of 10 numbers.
|
| « Last Edit: May 8th, 2017, 12:54pm by towr » |
IP Logged |
Wikipedia, Google, Mathworld, Integer sequence DB
|
|
|
fatball
Senior Riddler
Can anyone help me think outside the box please?
Gender:
Posts: 315
|
|
Re: Urgent Excel
« Reply #4 on: May 8th, 2017, 1:40pm » |
Quote Modify
|
towr, I started with this simple approach as well, but there was no way I could address all the possible combinations?
|
|
IP Logged |
|
|
|
rmsgrey
Uberpuzzler
    
Gender:
Posts: 2873
|
|
Re: Urgent Excel
« Reply #5 on: May 8th, 2017, 3:49pm » |
Quote Modify
|
Or you can set up a binary array:
A1:J1 = 0
A2 = 1 - A1
B2 = if (A1==1 and A2 == 0); 1-B1; B1
Copy A2 into A2:A1024
Copy B2 into B2:J1024
Give or take my not remembering Excel syntax properly, that will give you every binary number from 0 to 1023. You can then get all 1024 sums by copying a suitable formula down another column. Eliminating the 12 sums you don't want and then making use of the remaining 1012 sums is up to you 
|
|
IP Logged |
|
|
|
towr
wu::riddles Moderator
Uberpuzzler
Some people are average, some are just mean.
Gender:
Posts: 13730
|
|
Re: Urgent Excel
« Reply #6 on: May 8th, 2017, 10:12pm » |
Quote Modify
|
on May 8th, 2017, 1:40pm, fatball wrote:| towr, I started with this simple approach as well, but there was no way I could address all the possible combinations? |
|
What do you mean by "address"?
|
|
IP Logged |
Wikipedia, Google, Mathworld, Integer sequence DB
|
|
|
fatball
Senior Riddler
Can anyone help me think outside the box please?
Gender:
Posts: 315
|
|
Re: Urgent Excel
« Reply #7 on: May 9th, 2017, 8:32am » |
Quote Modify
|
on May 8th, 2017, 10:12pm, towr wrote:
What do you mean by "address"?
|
|
I need the sum of each and every combination.
|
|
IP Logged |
|
|
|
fatball
Senior Riddler
Can anyone help me think outside the box please?
Gender:
Posts: 315
|
|
Re: Urgent Excel
« Reply #8 on: May 9th, 2017, 8:37am » |
Quote Modify
|
rmsgery, I will give it a try and let you know. Thanks.
|
|
IP Logged |
|
|
|
towr
wu::riddles Moderator
Uberpuzzler
Some people are average, some are just mean.
Gender:
Posts: 13730
|
|
Re: Urgent Excel
« Reply #9 on: May 9th, 2017, 8:49am » |
Quote Modify
|
on May 9th, 2017, 8:32am, fatball wrote:
I need the sum of each and every combination. |
|
I don't see how that's a problem; you just paste "sum of previous ten columns" in the 11th, and then the 11th column has all the sums.
|
|
IP Logged |
Wikipedia, Google, Mathworld, Integer sequence DB
|
|
|
fatball
Senior Riddler
Can anyone help me think outside the box please?
Gender:
Posts: 315
|
|
Re: Urgent Excel
« Reply #10 on: May 9th, 2017, 9:19am » |
Quote Modify
|
uhmmm, but all I will be getting is 11 results bot 1000?
|
|
IP Logged |
|
|
|
towr
wu::riddles Moderator
Uberpuzzler
Some people are average, some are just mean.
Gender:
Posts: 13730
|
see attachment
You can fill in whatever ten numbers you want in the first row, and it'll fill out the rest of the sheet. Sums of combos are in the L column.
|
| « Last Edit: May 9th, 2017, 10:09am by towr » |
IP Logged |
Wikipedia, Google, Mathworld, Integer sequence DB
|
|
|
fatball
Senior Riddler
Can anyone help me think outside the box please?
Gender:
Posts: 315
|
|
Re: Urgent Excel
« Reply #12 on: May 9th, 2017, 10:21am » |
Quote Modify
|
on May 9th, 2017, 10:09am, towr wrote:see attachment
You can fill in whatever ten numbers you want in the first row, and it'll fill out the rest of the sheet. Sums of combos are in the L column. |
|
How do you populate Cols A to J starting row 3?
|
|
IP Logged |
|
|
|
towr
wu::riddles Moderator
Uberpuzzler
Some people are average, some are just mean.
Gender:
Posts: 13730
|
|
Re: Urgent Excel
« Reply #13 on: May 9th, 2017, 10:47am » |
Quote Modify
|
The cells in the rows from 3 down have an absolute reference to cells in the top row, i.e. the cells contain =$A$1 up to =$J$1 (or nothing if the number is left out of that combo)
So if you change the values in the top row, the rest copy that one.
By using absolute references, you can just copy the row, and they'll reference the same cells as the original row, instead of other cells that have the same relative position to the new row.
i.e. if you copy a cell with =$A$1 from A3 to A4, it'll still reference A1 i.s.o. A2 as it would if A3 contained =A1.
And I just realized I could have made things slightly easier for myself if I had only made the row-position absolute (i.e. using =A$1), then I could have just copied that to the other columns to refer to the top cell.
|
| « Last Edit: May 9th, 2017, 10:54am by towr » |
IP Logged |
Wikipedia, Google, Mathworld, Integer sequence DB
|
|
|
fatball
Senior Riddler
Can anyone help me think outside the box please?
Gender:
Posts: 315
|
|
Re: Urgent Excel
« Reply #14 on: May 9th, 2017, 10:52am » |
Quote Modify
|
I know but you were not copying and pasting the entire row as there were blank cells with no formulae. How did you set up the criteria for each row? Using some sort of binary algorithm like what rmsgrey suggested?
|
|
IP Logged |
|
|
|
towr
wu::riddles Moderator
Uberpuzzler
Some people are average, some are just mean.
Gender:
Posts: 13730
|
|
Re: Urgent Excel
« Reply #15 on: May 9th, 2017, 10:56am » |
Quote Modify
|
I started with a full row, then copied it, then emptied the tenth column from the rows I copied (i.e. the top half).
Then copied all the rows again, and emptied the 9th column of the rows I copied.
and so on.
Then I removed rows with just one value in them. (Which conveniently is always the first row in which each number occurs, tenth in row 1(+2), 9th in row 2(+2), 8th in row 4(+2) etc)
1 2 3 4
=> duplicate rows + clear half of 4th column
1 2 3 _
1 2 3 4
=> duplicate rows + clear half of 3th column
1 2 _ _
1 2 _ 4
1 2 3 _
1 2 3 4
=> duplicate rows + clear half of 2th column
1 _ _ _
1 _ _ 4
1 _ 3 _
1 _ 3 4
1 2 _ _
1 2 _ 4
1 2 3 _
1 2 3 4
=> duplicate rows + clear half of 1th column
_ _ _ _
_ _ _ 4
_ _ 3 _
_ _ 3 4
_ 2 _ _
_ 2 _ 4
_ 2 3 _
_ 2 3 4
1 _ _ _
1 _ _ 4
1 _ 3 _
1 _ 3 4
1 2 _ _
1 2 _ 4
1 2 3 _
1 2 3 4
=> remove rows with 0 or 1 value
_ _ 3 4
_ 2 _ 4
_ 2 3 _
_ 2 3 4
1 _ _ 4
1 _ 3 _
1 _ 3 4
1 2 _ _
1 2 _ 4
1 2 3 _
1 2 3 4
I suppose it doesn't really matter at what end you start, or whether you empty the bottom or top half of the column each step.
|
| « Last Edit: May 9th, 2017, 11:08am by towr » |
IP Logged |
Wikipedia, Google, Mathworld, Integer sequence DB
|
|
|
fatball
Senior Riddler
Can anyone help me think outside the box please?
Gender:
Posts: 315
|
|
Re: Urgent Excel
« Reply #16 on: May 9th, 2017, 11:07am » |
Quote Modify
|
Oh so you were indeed taking a manual approach...is there a way to generalize the model with sth like rmsgrey suggested? I was attempting it but found it quite tedious.
On a side note, why couldn't I see your illustration in your main message but only under the topic summary when I replied?
|
|
IP Logged |
|
|
|
towr
wu::riddles Moderator
Uberpuzzler
Some people are average, some are just mean.
Gender:
Posts: 13730
|
|
Re: Urgent Excel
« Reply #17 on: May 9th, 2017, 11:13am » |
Quote Modify
|
on May 9th, 2017, 11:07am, fatball wrote:| Oh so you were indeed taking a manual approach...is there a way to generalize the model with sth like rmsgrey suggested? I was attempting it but found it quite tedious. |
|
Well, it didn't take me more than 7 minutes.
I'm really not an excel-wizard, I didn't even know how to use absolute cell-reference until yesterday. I could probably write something in python that would generate an excel sheet for a given number of values, but you'll have to wait for one of the other puzzler to get an answer how to do it purely in excel (and/or VBA).
Quote:| On a side note, why couldn't I see your illustration in your main message but only under the topic summary when I replied? |
|
Because I was still editing it in 
|
| « Last Edit: May 9th, 2017, 11:14am by towr » |
IP Logged |
Wikipedia, Google, Mathworld, Integer sequence DB
|
|
|
fatball
Senior Riddler
Can anyone help me think outside the box please?
Gender:
Posts: 315
|
|
Re: Urgent Excel
« Reply #18 on: May 9th, 2017, 11:19am » |
Quote Modify
|
In that case, I may as well do it the same way as yours as all I need is a quick solution. Building a model in excel takes time and writing the VBA codes takes longer...
|
|
IP Logged |
|
|
|
dudiobugtron
Uberpuzzler
Posts: 735
|
|
Re: Urgent Excel
« Reply #19 on: May 9th, 2017, 12:41pm » |
Quote Modify
|
Thanks for the tip about absolute references btw towr! Very useful, but I didn't even think that they might be a thing until I read your post today.
|
|
IP Logged |
|
|
|
fatball
Senior Riddler
Can anyone help me think outside the box please?
Gender:
Posts: 315
|
|
Re: Urgent Excel
« Reply #20 on: May 9th, 2017, 1:36pm » |
Quote Modify
|
It did not really take that long to do it the towr way. I needed to do it for 18 numbers and it just took me less than 15 min to populate over 260K rows. Thanks!
|
|
IP Logged |
|
|
|
dudiobugtron
Uberpuzzler
Posts: 735
|
|
Re: Urgent Excel
« Reply #21 on: May 9th, 2017, 6:03pm » |
Quote Modify
|
If I'm understanding it correctly, then a slight speed-up for towr's method would be to paste *after* deleting the column. That way you can quickly select all the things you want to copy (ctrl+a, ctrl+c) and delete (just click on the column header). The only time you'd need to find the right row would be when pasting.
|
|
IP Logged |
|
|
|
fatball
Senior Riddler
Can anyone help me think outside the box please?
Gender:
Posts: 315
|
|
Re: Urgent Excel
« Reply #22 on: May 9th, 2017, 8:17pm » |
Quote Modify
|
but at which point do I have to delete any column? I just create the columns I need to hold my numbers...
|
|
IP Logged |
|
|
|
towr
wu::riddles Moderator
Uberpuzzler
Some people are average, some are just mean.
Gender:
Posts: 13730
|
|
Re: Urgent Excel
« Reply #23 on: May 9th, 2017, 11:05pm » |
Quote Modify
|
You don't delete the column, but the contents.
like
1 2 3 4
1 2 3 4
=> copy (no paste)
1 2 3 4
1 2 3 4
=> delete column-content
1 _ 3 4
1 _ 3 4
=> paste
1 _ 3 4
1 _ 3 4
1 2 3 4
1 2 3 4
So that's a bit faster since you don't need to select half a column.
|
| « Last Edit: May 9th, 2017, 11:07pm by towr » |
IP Logged |
Wikipedia, Google, Mathworld, Integer sequence DB
|
|
|
fatball
Senior Riddler
Can anyone help me think outside the box please?
Gender:
Posts: 315
|
|
Re: Urgent Excel
« Reply #24 on: May 10th, 2017, 6:19am » |
Quote Modify
|
It sounds like deleting the entire column content is technically better than deleting half-column, but practically there will be no gain in time as, with the revised method, you have to place your cursor precisely for the copy-delete-paste steps; but by pasting first, the cursor will end up sitting at the same spot, i.e., mid-way of the entire data set, thus you can easily select the half column to delete with these buttons [shift] [end] [up arrow]...
|
|
IP Logged |
|
|
|
|
RIDDLES SITE
WRITE MATH!
Home 
Help 
Search 
Members 
Login 
Register
Reply
Notify of replies
Send Topic
Print
10combos.xls