Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
median of combinations from values in a column
Hi,
I posted this already he http://groups.google.com/group/micro...35 7c7d62558d and received some good solutions. However, my original problem has become a bit more complicated. Here's the original problem: I'm trying to figure out how to generate all combinations from a list of N values in a column, let's say column A. In particular, I want to have all combinations of 2 values, 3 values, 4, ... up to 7 values. To give an example: Let's assume I have a list of only 3 values (1,2,3) for which I want to have all combinations of two values. In this case, the result would be 1,2; 1,3; and 2;3. The ordering of the values does not matter, i.e. duplicates should be eliminated. Now, the extension is that I need the median of each combination rather than the combinations themselves. So it would be nice if the code would already calculate the median. Besides the solution in the link, I also found another code that works well - but still does not provide me with the median (see below). An additional problem is the limitation in the number of rows in excel. Thus, it would be great to have the output in a txt file or something similar, which may ideally be imported to SPSS. Any suggestions? Thanks in advance, Andreas Sub test() ListCombos Range("A1:A5"), 3, 7 End Sub Sub ListCombos(r As Range, m As Long, iRow As Long) ' lists the combinations of r choose m starting in row iRow Dim ai() As Long Dim i As Long Dim n As Long Dim vOut As Variant n = r.Rows.Count Redim ai(1 To m) Redim vOut(1 To m) ai(1) = 0 For i = 2 To m ai(i) = i Next i Do For i = 1 To m - 1 If ai(i) + 1 < ai(i + 1) Then ai(i) = ai(i) + 1 Exit For Else ai(i) = i End If Next i If i = m Then If ai(m) < n Then ai(m) = ai(m) + 1 Else Exit Sub End If End If ' put the values in the variant For i = 1 To m vOut(i) = r(ai(i)) Next i ' list it Cells(iRow, 1).Resize(, m).Value = vOut iRow = iRow + 1 Loop End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
median of combinations from values in a column
Replace
Cells(iRow, 1).Resize(, m).Value = vOut iRow = iRow + 1 with Cells(iRow, 1).Resize(, m).Value = vOut Cells(iRow, 1).Offset(0, m).Value = WorksheetFunction.Median(Cells(iRow, 1).Resize(, m)) iRow = iRow + 1 near the end of your code. The middle line is an addition, everything else remains the same. -- HTH, Barb Reinhardt "Andreas" wrote: Hi, I posted this already he http://groups.google.com/group/micro...35 7c7d62558d and received some good solutions. However, my original problem has become a bit more complicated. Here's the original problem: I'm trying to figure out how to generate all combinations from a list of N values in a column, let's say column A. In particular, I want to have all combinations of 2 values, 3 values, 4, ... up to 7 values. To give an example: Let's assume I have a list of only 3 values (1,2,3) for which I want to have all combinations of two values. In this case, the result would be 1,2; 1,3; and 2;3. The ordering of the values does not matter, i.e. duplicates should be eliminated. Now, the extension is that I need the median of each combination rather than the combinations themselves. So it would be nice if the code would already calculate the median. Besides the solution in the link, I also found another code that works well - but still does not provide me with the median (see below). An additional problem is the limitation in the number of rows in excel. Thus, it would be great to have the output in a txt file or something similar, which may ideally be imported to SPSS. Any suggestions? Thanks in advance, Andreas Sub test() ListCombos Range("A1:A5"), 3, 7 End Sub Sub ListCombos(r As Range, m As Long, iRow As Long) ' lists the combinations of r choose m starting in row iRow Dim ai() As Long Dim i As Long Dim n As Long Dim vOut As Variant n = r.Rows.Count Redim ai(1 To m) Redim vOut(1 To m) ai(1) = 0 For i = 2 To m ai(i) = i Next i Do For i = 1 To m - 1 If ai(i) + 1 < ai(i + 1) Then ai(i) = ai(i) + 1 Exit For Else ai(i) = i End If Next i If i = m Then If ai(m) < n Then ai(m) = ai(m) + 1 Else Exit Sub End If End If ' put the values in the variant For i = 1 To m vOut(i) = r(ai(i)) Next i ' list it Cells(iRow, 1).Resize(, m).Value = vOut iRow = iRow + 1 Loop End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
median of combinations from values in a column
Hi Barb,
Thanks for your answer. I did this but I can't see that anything has changed. I still get the combinations (each in one row) but no median. In addition, how can I change the code to get the data in a txt file without row limitations? Andreas |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
median of combinations from values in a column
Thanks Barb, this works perfect.
Now, one more thing. How can I get the data in a txt file so that I can deal with the row limitations of Excel? I have problems with way more than 65,000 rows. I want to import the data in SPSS later. Andreas |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
median of combinations from values in a column
In addition, is it possible to show *only* the median? This would make
it easier in terms of importing to SPSS later. Andreas |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
median of combinations from values in a column
I've written to a text file once and truthfully don't remember how I did it.
I'd do a google search for VBA write to text file. I bet you'll find what you need. -- HTH, Barb Reinhardt " wrote: Hi Barb, Thanks for your answer. I did this but I can't see that anything has changed. I still get the combinations (each in one row) but no median. In addition, how can I change the code to get the data in a txt file without row limitations? Andreas |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
median of combinations from values in a column
Just curious. Can SPSS read in that many records at once?
=COMBIN(66,7) = 778,789,440 Here is just an idea. I don't know if it is of any use though. If you are generating "Subsets" of size 4 in lexicographical order (ie sorted) and use 4 variables a,b,c,&d, then the mean is (a+b+c+d)/4 and the median is (b+c)/2 You execute division 1,557,578,880 times, and store real numbers in your file. Another idea might be to store integer values. ie just store (a+b+c+d) and (b+c) in the text file, and see if SPSS can divide your final answers by 4 and 2. This is not quite the code I use for Subsets, but here is a general idea for outputting to a text file. This is a simple idea that I'm sure you could modify. Given a set 1-6, this generates "Subsets" of size 4. However, this does "NOT" divide the sums by 4 and 2. The "Mean" and "Median" go to different files. Sub Demo() Dim a As Long Dim b As Long Dim c As Long Dim d As Long Dim n As Long Dim z As Long Dim FileMean Dim FileMedian n = 6 z = n + 1 ChDir "C:\Temp" FileMean = FreeFile Open "Mean" For Output As #FileMean FileMedian = FreeFile Open "Median" For Output As #FileMedian For a = 0 + 1 To n - 3 For b = a + 1 To n - 2 For c = b + 1 To n - 1 For d = c + 1 To n Write #FileMean, a + b + c + d '(/4 if you wish) Write #FileMedian, b + c '(/2 if you wish) Next d, c, b, a Close End Sub -- HTH :) Dana DeLouis wrote in message ... In addition, is it possible to show *only* the median? This would make it easier in terms of importing to SPSS later. Andreas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find for non zero values in a column Median,Mode,STDEV | Excel Worksheet Functions | |||
Find the median 3 values | Excel Discussion (Misc queries) | |||
How to display graph if only mean, SD & median values available? | Charts and Charting in Excel | |||
Counting values lower then the median. | Excel Worksheet Functions | |||
Median ignoring Zero Values | Excel Worksheet Functions |