Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mally
 
Posts: n/a
Default Displaying all combinations of a range of numbers

How do I display all the combinations of a group of numbers

Example
Cell A1='3'
Cells B1='1', B2=3, B3=9, B4=20, B5=25

This will display all the combinations of 3(A1) numbers from the numbers
1,3,9,20,25

1 3 9
1 3 20
1 3 25 etc

The amount in cell A1 will change as well as the total of numbers in the
range.
  #2   Report Post  
Mally
 
Posts: n/a
Default

If i didn't make it clear enough. Hope this helps.

Simply, in my example above, display all the combinations of the number in
cell A1 in a range. So the above would be

A1='2' A1='3' A1='4'
1 3 1 3 9 1 3 9 20
1 9 1 3 20 1 3 9 25
1 20 1 3 25 3 9 20 25
1 25 1 9 20 9 20 25 1
3 9 1 9 25 9 20 25 3
3 20 3 9 20 20 25 1 3
3 25 3 9 25 etc
9 20 9 20 25
9 25 20 25 1
20 25 20 25 3
etc etc

Again, the numbers in cells be can be different and there is no maximum.
Also the combinations shouldnt repeat the same combinations i.e. 1 3 and 3 1.

I hope this I hope this is clearer.

Thanks

"Mally" wrote:

How do I display all the combinations of a group of numbers

Example
Cell A1='3'
Cells B1='1', B2=3, B3=9, B4=20, B5=25

This will display all the combinations of 3(A1) numbers from the numbers
1,3,9,20,25

1 3 9
1 3 20
1 3 25 etc

The amount in cell A1 will change as well as the total of numbers in the
range.

  #3   Report Post  
Myrna Larson
 
Posts: n/a
Default

You need a macro for this. I've written the code. You can find it on Google if
you search for

combinations permutations

posted by Myrna Larson in groups microsoft.public.excel.*

But it will not work with your layout. It requires a column of data, with the
top cell containing "C" or "P" (for combinations or permutations), the number
in a set in the cell below that (what you say you have in A1), and the members
of the population in the cells below that (what you say you have in B1:B5).
i.e. you would need "C" in A1, the number 3 in A2, and the numbers 1, 3, 9,
20, and 25 in A3:A7

On Sun, 6 Mar 2005 03:07:03 -0800, "Mally"
wrote:

How do I display all the combinations of a group of numbers

Example
Cell A1='3'
Cells B1='1', B2=3, B3=9, B4=20, B5=25

This will display all the combinations of 3(A1) numbers from the numbers
1,3,9,20,25

1 3 9
1 3 20
1 3 25 etc

The amount in cell A1 will change as well as the total of numbers in the
range.


  #4   Report Post  
Myrna Larson
 
Posts: n/a
Default

PS: The code is not locked, so you could perhaps modify it to fit your layout
if you can't modify the layout.

On Sun, 6 Mar 2005 03:07:03 -0800, "Mally"
wrote:

How do I display all the combinations of a group of numbers

Example
Cell A1='3'
Cells B1='1', B2=3, B3=9, B4=20, B5=25

This will display all the combinations of 3(A1) numbers from the numbers
1,3,9,20,25

1 3 9
1 3 20
1 3 25 etc

The amount in cell A1 will change as well as the total of numbers in the
range.


  #5   Report Post  
Max
 
Posts: n/a
Default

Think Biff posted this link previously: http://tinyurl.com/6okbp
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----




  #6   Report Post  
Junior Member
 
Posts: 1
Default

i am wondering if you know how to put the results of the code on different cells and not on one cell
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to add one number to a range of numbers BatonRougeguy Excel Worksheet Functions 1 February 16th 05 07:47 AM
How do I add a range of numbers to sum a specific total? SJoshi Excel Worksheet Functions 3 February 15th 05 02:16 PM
Converting negative numbers in a range of cells to zero Dede Excel Discussion (Misc queries) 3 January 14th 05 07:23 PM
find numbers in a range that add to a specific value Brett Excel Discussion (Misc queries) 1 December 20th 04 02:55 PM
How do I reference and sort a range of numbers in Excel 97? Old Northern Excel Worksheet Functions 3 December 14th 04 04:15 PM


All times are GMT +1. The time now is 11:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"