wu :: forums « wu :: forums - Urgent Excel » Welcome, Guest. Please Login or Register. May 25th, 2024, 8:20am RIDDLES SITE WRITE MATH! Home Help Search Members Login Register
 wu :: forums    riddles    medium (Moderators: towr, Icarus, ThudnBlunder, Eigenray, Grimbal, william wu, SMQ)    Urgent Excel « Previous topic | Next topic »
 Pages: 1 2 Reply Notify of replies Send Topic Print
 Author Topic: Urgent Excel  (Read 4297 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

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
 Re: Urgent Excel   10combos.xls « Reply #11 on: May 9th, 2017, 10:09am » Quote Modify

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
 Pages: 1 2 Reply Notify of replies Send Topic Print

 Forum Jump: ----------------------------- riddles -----------------------------  - easy => medium   - hard   - what am i   - what happened   - microsoft   - cs   - putnam exam (pure math)   - suggestions, help, and FAQ   - general problem-solving / chatting / whatever ----------------------------- general -----------------------------  - guestbook   - truth   - complex analysis   - wanted   - psychology   - chinese « Previous topic | Next topic »