wu :: forums
« wu :: forums - Urgent Excel »

Welcome, Guest. Please Login or Register.
Mar 28th, 2024, 5:30pm

RIDDLES SITE WRITE MATH! Home Home Help Help Search Search Members Members Login Login Register Register
   wu :: forums
   riddles
   medium
(Moderators: Eigenray, Icarus, SMQ, Grimbal, ThudnBlunder, william wu, towr)
   Urgent Excel
« Previous topic | Next topic »
Pages: 1 2  Reply Reply Notify of replies Notify of replies Send Topic Send Topic Print Print
   Author  Topic: Urgent Excel  (Read 4253 times)
fatball
Senior Riddler
****



Can anyone help me think outside the box please?

   


Gender: male
Posts: 315
Urgent Excel  
« on: May 8th, 2017, 10:44am »
Quote Quote Modify 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 Quote Modify 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: male
Posts: 315
Re: Urgent Excel  
« Reply #2 on: May 8th, 2017, 12:29pm »
Quote Quote Modify 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: male
Posts: 13730
Re: Urgent Excel  
« Reply #3 on: May 8th, 2017, 12:49pm »
Quote Quote Modify 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: male
Posts: 315
Re: Urgent Excel  
« Reply #4 on: May 8th, 2017, 1:40pm »
Quote Quote Modify 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
*****





134688278 134688278   rmsgrey   rmsgrey


Gender: male
Posts: 2872
Re: Urgent Excel  
« Reply #5 on: May 8th, 2017, 3:49pm »
Quote Quote Modify 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 Smiley
IP Logged
towr
wu::riddles Moderator
Uberpuzzler
*****



Some people are average, some are just mean.

   


Gender: male
Posts: 13730
Re: Urgent Excel  
« Reply #6 on: May 8th, 2017, 10:12pm »
Quote Quote Modify 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: male
Posts: 315
Re: Urgent Excel  
« Reply #7 on: May 9th, 2017, 8:32am »
Quote Quote Modify 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: male
Posts: 315
Re: Urgent Excel  
« Reply #8 on: May 9th, 2017, 8:37am »
Quote Quote Modify 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: male
Posts: 13730
Re: Urgent Excel  
« Reply #9 on: May 9th, 2017, 8:49am »
Quote Quote Modify 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: male
Posts: 315
Re: Urgent Excel  
« Reply #10 on: May 9th, 2017, 9:19am »
Quote Quote Modify 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: male
Posts: 13730
Re: Urgent Excel   10combos.xls
« Reply #11 on: May 9th, 2017, 10:09am »
Quote Quote Modify 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: male
Posts: 315
Re: Urgent Excel  
« Reply #12 on: May 9th, 2017, 10:21am »
Quote Quote Modify 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: male
Posts: 13730
Re: Urgent Excel  
« Reply #13 on: May 9th, 2017, 10:47am »
Quote Quote Modify 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: male
Posts: 315
Re: Urgent Excel  
« Reply #14 on: May 9th, 2017, 10:52am »
Quote Quote Modify 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: male
Posts: 13730
Re: Urgent Excel  
« Reply #15 on: May 9th, 2017, 10:56am »
Quote Quote Modify 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: male
Posts: 315
Re: Urgent Excel  
« Reply #16 on: May 9th, 2017, 11:07am »
Quote Quote Modify 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: male
Posts: 13730
Re: Urgent Excel  
« Reply #17 on: May 9th, 2017, 11:13am »
Quote Quote Modify 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 Tongue
« 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: male
Posts: 315
Re: Urgent Excel  
« Reply #18 on: May 9th, 2017, 11:19am »
Quote Quote Modify 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... Smiley
IP Logged
dudiobugtron
Uberpuzzler
*****





   


Posts: 735
Re: Urgent Excel  
« Reply #19 on: May 9th, 2017, 12:41pm »
Quote Quote Modify 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: male
Posts: 315
Re: Urgent Excel  
« Reply #20 on: May 9th, 2017, 1:36pm »
Quote Quote Modify 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 Quote Modify 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: male
Posts: 315
Re: Urgent Excel  
« Reply #22 on: May 9th, 2017, 8:17pm »
Quote Quote Modify 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: male
Posts: 13730
Re: Urgent Excel  
« Reply #23 on: May 9th, 2017, 11:05pm »
Quote Quote Modify 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: male
Posts: 315
Re: Urgent Excel  
« Reply #24 on: May 10th, 2017, 6:19am »
Quote Quote Modify 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 Reply Notify of replies Notify of replies Send Topic Send Topic Print Print

« Previous topic | Next topic »

Powered by YaBB 1 Gold - SP 1.4!
Forum software copyright © 2000-2004 Yet another Bulletin Board