Sunday, January 17, 2010

How do I compute the total number of configurations of two or more lists in Microsoft Excel 2007?

How do I compute the total number of configurations of two or more lists in Microsoft Excel 2007? The formulas you will need use are the INT, MOD, COUNTA, ROW, and OFFSET functions. Here is an explanation of each:

INT rounds a number down to the nearest integer.
MOD Returns the remainder from division: MOD(number,divisor).
COUNTA function counts the number of cells that are not empty in a range.
ROW(reference) is the cell or range of cells for which you want the row number.
Row ( ) If reference is omitted, it is assumed to be the reference of the cell in which the ROW function appears.
Example: =ROW( ) - 1 inserted into a cell in row 26 will yield the result 25.


Here are the formulas for a two column combination:
=OFFSET($A$1,INT((ROW()-1)/(COUNTA(B:B))),0)
=OFFSET($A$1,INT(MOD((ROW()-1),COUNTA(B:B))),1)

First Column Formula Explanation:

=OFFSET($A$1,INT((ROW()-1)/(COUNTA(B:B))),0)

Takes the row number and subtracts one from it. Then it divides this number by the number of options in the second column. This value is then rounded down to the nearest integer (whole number). This in turn is the number of the row in which it will select the answer, starting with 0.




Later on I will give an example of to use this formula up to ten unique columns.