permutation

[Margie]

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.

 

[Bernie Deitrick]

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]

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.

 

[Bernie Deitrick]

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