permutation
 

I have a table with year 1, 2, and 3 across in columns and have 13 rows of
data that start at 1 and go to 4 in one-fourth increments. For example, row
1 is 1,1,1; row 2 is 1.25, 1.25, 1.25, row 3 is 1.50, 1.50, 1.50 all the way
down to 4. I know that I have 1706 possible combinations (=permut(13,3). I
need to know if there is a way to have EXCEL actually compute those
combinations and show them on my spreadsheet. I need this for a project at
work. Appreciate any help.
--
Margie

permutation
 

Margie,

The possible combinations is actually 13^3, or 2197. he macro below assumes that you have headings
in row 1 and labels in column A, and your first data point is in cell B2.

That said, it is rarely necessary to actually list out all the combinations. What are you doing?

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim FirstRow As Integer
Dim FirstCol As Integer
Dim LastRow As Integer
Dim RCount As Long

FirstRow = 2
FirstCol = 2
LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row

RCount = LastRow + 3
For i = FirstRow To LastRow
For j = FirstRow To LastRow
For k = FirstRow To LastRow
Cells(RCount, FirstCol).Value = Cells(i, FirstCol).Value
Cells(RCount, FirstCol + 1).Value = Cells(j, FirstCol + 1).Value
Cells(RCount, FirstCol + 2).Value = Cells(k, FirstCol + 2).Value
RCount = RCount + 1
Next k
Next j
Next i

End Sub




"Margie" wrote in message
...
I have a table with year 1, 2, and 3 across in columns and have 13 rows of
data that start at 1 and go to 4 in one-fourth increments. For example, row
1 is 1,1,1; row 2 is 1.25, 1.25, 1.25, row 3 is 1.50, 1.50, 1.50 all the way
down to 4. I know that I have 1706 possible combinations (=permut(13,3). I
need to know if there is a way to have EXCEL actually compute those
combinations and show them on my spreadsheet. I need this for a project at
work. Appreciate any help.
--
Margie

permutation
 

Thanks for the quick response. I will be using the values as percentages and
applying each set of values to a present cost figure to determine cost of
total increase over a three year period. I want to get the total costs and
see where I want to be and then see what sets of values are feasible to
consider. I have never ran a macro before. Do I just put my table in
EXCEL, set up the macro, then run it. I can probably figure out how to set
up the macro. Is this basically the idea.
--
Margie


"Bernie Deitrick" wrote:

Margie,

The possible combinations is actually 13^3, or 2197. he macro below assumes that you have headings
in row 1 and labels in column A, and your first data point is in cell B2.

That said, it is rarely necessary to actually list out all the combinations. What are you doing?

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim FirstRow As Integer
Dim FirstCol As Integer
Dim LastRow As Integer
Dim RCount As Long

FirstRow = 2
FirstCol = 2
LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row

RCount = LastRow + 3
For i = FirstRow To LastRow
For j = FirstRow To LastRow
For k = FirstRow To LastRow
Cells(RCount, FirstCol).Value = Cells(i, FirstCol).Value
Cells(RCount, FirstCol + 1).Value = Cells(j, FirstCol + 1).Value
Cells(RCount, FirstCol + 2).Value = Cells(k, FirstCol + 2).Value
RCount = RCount + 1
Next k
Next j
Next i

End Sub




"Margie" wrote in message
...
I have a table with year 1, 2, and 3 across in columns and have 13 rows of
data that start at 1 and go to 4 in one-fourth increments. For example, row
1 is 1,1,1; row 2 is 1.25, 1.25, 1.25, row 3 is 1.50, 1.50, 1.50 all the way
down to 4. I know that I have 1706 possible combinations (=permut(13,3). I
need to know if there is a way to have EXCEL actually compute those
combinations and show them on my spreadsheet. I need this for a project at
work. Appreciate any help.
--
Margie

permutation
 

Margie,

Visit here for information on getting started with macros:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

For compounded growth over three years, the average rate of growth is the
third root of the ratio of the newest value divided by the oldest value. As
a percentage:

=(A2/B2)^(1/3)-1

format the cell as percent....

HTH,
Bernie
MS Excel MVP

"Margie" wrote in message
...
Thanks for the quick response. I will be using the values as percentages
and
applying each set of values to a present cost figure to determine cost of
total increase over a three year period. I want to get the total costs
and
see where I want to be and then see what sets of values are feasible to
consider. I have never ran a macro before. Do I just put my table in
EXCEL, set up the macro, then run it. I can probably figure out how to
set
up the macro. Is this basically the idea.
--
Margie


"Bernie Deitrick" wrote:

Margie,

The possible combinations is actually 13^3, or 2197. he macro below
assumes that you have headings
in row 1 and labels in column A, and your first data point is in cell B2.

That said, it is rarely necessary to actually list out all the
combinations. What are you doing?

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim FirstRow As Integer
Dim FirstCol As Integer
Dim LastRow As Integer
Dim RCount As Long

FirstRow = 2
FirstCol = 2
LastRow = Cells(Rows.Count, FirstCol).End(xlUp).Row

RCount = LastRow + 3
For i = FirstRow To LastRow
For j = FirstRow To LastRow
For k = FirstRow To LastRow
Cells(RCount, FirstCol).Value = Cells(i, FirstCol).Value
Cells(RCount, FirstCol + 1).Value = Cells(j, FirstCol + 1).Value
Cells(RCount, FirstCol + 2).Value = Cells(k, FirstCol + 2).Value
RCount = RCount + 1
Next k
Next j
Next i

End Sub




"Margie" wrote in message
...
I have a table with year 1, 2, and 3 across in columns and have 13 rows
of
data that start at 1 and go to 4 in one-fourth increments. For
example, row
1 is 1,1,1; row 2 is 1.25, 1.25, 1.25, row 3 is 1.50, 1.50, 1.50 all
the way
down to 4. I know that I have 1706 possible combinations
(=permut(13,3). I
need to know if there is a way to have EXCEL actually compute those
combinations and show them on my spreadsheet. I need this for a
project at
work. Appreciate any help.
--
Margie