Search code examples
excelexcel-formulaworksheet-functionmodular-arithmetic

Populate cells based on x by y cell value

I'm trying to populate cells based on values from two different cells. Values in the cell needs to be (n-1) where n is the input and then repeated based on the amount of the other cell.

For example, I have input:

x     y  
2     5

Output should be:

  • x should have 0 and 1; each repeated five times
  • y should have 0, 1, 2, 3, 4; each repeated twice 
x1   y1  
0    0  
0    1  
0    2  
0    3  
0    4  
1    0  
1    1  
1    2  
1    3  
1    4

I used:

=IF(ROW()<=C2+1,K2-1,"")

and 

=IF(ROW()<=d2+1,K2-1,"")

but it is not repeating and I only see:

  
x   y  
0   0  
1   1  
__  2  
__  3  
__  4

(C2 and D2 are where values for x and y are, K is the number of items.)

Are there any suggestions on how I can do this?

Solution

  • In Row2 and copied down to suit:

    =IF(ROW()<=1+C$2*D$2,INT((ROW()-2)/D$2),"")

    and 

    =IF(ROW()<=1+C$2*D$2,MOD(ROW()-2,D$2),"")