# w/s Formula to Automatically Create Two 2-equi-interval Tables ??

#### monirg

##### Well-known Member
Hello;

Your help would be greatly appreciated.

1) Given:
no. of X values::A2 = 8 (<= 30)
max. X value::A3 = 1.00
min. X value ::A4 = 0.93

no. of Y values::A2 = 10 (<= 50)
max. Y value::A3 = 0.00
min. Y value ::A4 = -13.50

2) I need to automatically create two 2-equi-interval X-Y tables by changing any of the above 6 values, and:
a. X and Y are in ascending order in each table;
b. Table 1 (starts at A5:B5): Y changes faster for each value of X; and
c. Table 2 (starts at C5:D5): X changes faster for each value of Y.

3) For the above example, the TWO tables should look like:

....TABLE 1..........TABLE 2
...X1.......Y1........X2........Y2
0.930 -13.500 0.930 -13.500
0.930 -12.000 0.940 -13.500
0.930 -10.500 0.950 -13.500
0.930 -9.000 0.960 -13.500
0.930 -7.500 0.970 -13.500
0.930 -6.000 0.980 -13.500
0.930 -4.500 0.990 -13.500
0.930 -3.000 1.000 -13.500
0.930 -1.500 blank....blank
0.930 0.000 0.930 -12.00
blank....blank 0.940 -12.00
0.940 -13.500 0.950 -12.00
0.940 -12.000 0.960 -12.00
0.940 -10.500 0.970 -12.00
0.940 -9.000 0.980 -12.00
0.940 -7.500 0.990 -12.00
......................1.000 -12.00
.......................blank....blank
...........................................
.last segmt........last segmt....
1.000 -13.500 0.930 0.000
1.000 -12.000 0.940 0.000
1.000 -10.500 0.950 0.000
1.000 -9.000 0.960 0.000
1.000 -7.500 0.970 0.000
1.000 -6.000 0.980 0.000
1.000 -4.500 0.990 0.000
1.000 -3.000 1.000 0.000
1.000 -1.500 blank....blank
1.000 0.000

What are the formulas to enter at the start of Tables (cells A5, B5, C5, D5) ?? ...to be copied down to the max anticipated 30*50 rows ??
{It would be a bonus to have 2 blank cells at the end of each segment in each Table (as shown above).

Thank you kindly.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### monirg

##### Well-known Member
Hello;

Here's a solution which works fine for me.

1) Given:
no. of X values::C2 = 8 (<= 30)
max. X value::C3 = 1.00
min. X value ::C4 = 0.93

no. of Y values::G2 = 10 (<= 50)
max. Y value::G3 = 0.00
min. Y value ::G4 = -13.50

2) The question is (applying w/s functions):
How can one automatically create two 2-equi-interval X-Y tables by changing any of the above 6 values, and:
a. X and Y are in ascending order in each table;
b. Table 1 (starts at A7:B7): Y changes faster for each value of X;
c. Table 2 (starts at E7:F7): X changes faster for each value of Y; and
d. It would be nice to have 2 blank cells at the end (or start) of each segment in each Table (as shown below).

3) The two Tables would look like:
....TABLE 1.........TABLE 2
...A7.......B7.....E7.......F7
...X1.......Y1......X2.......Y2
0.930 -13.500 0.930 -13.500
0.930 -12.000 0.940 -13.500
0.930 -10.500 0.950 -13.500
0.930 -9.0000 0.960 -13.500
0.930 -7.5000 0.970 -13.500
0.930 -6.0000 0.980 -13.500
0.930 -4.5000 0.990 -13.500
0.930 -3.0000 1.000 -13.500
0.930 -1.5000 .....................
0.930 00.0000 0.930 -12.000
................... 0.940 -12.000
0.940 -13.500 0.950 -12.000
0.940 -12.000 0.960 -12.000
0.940 -10.500 0.970 -12.000
0.940 -9.0000 0.980 -12.000
0.940 -7.5000 0.990 -12.000
0.940 -6.0000 1.000 -12.000
..........................................

4) Bernie Deitrick (MS Excel MVP), MS XL w/s DG, kindly provided the following formulas which have been tested and proven to be working as desired:
Cell A7::
Code:
``````=IF(INT((ROW()-ROW(\$A\$7)+1)/(\$G\$2+1))<> (((ROW()-ROW(\$A\$7)+1)/(\$G\$2+1))),
IF(\$C\$4+INT((ROW()-ROW(\$A\$7)+1)/(\$G\$2+1))*(\$C\$3-\$C\$4)/(\$C\$2-1)<=\$C\$3,
\$C\$4+INT((ROW()-ROW(\$A\$7)+1)/(\$G\$2+1))*(\$C\$3-\$C\$4)/(\$C\$2-1),""),"")``````
Cell B7::
Code:
``````=IF(INT((ROW()-ROW(\$B\$7)+1)/(\$G\$2+1))<> (((ROW()-ROW(\$B\$7)+1)/(\$G\$2+1))),
IF(\$C\$4+INT((ROW()-ROW(\$B\$7)+1)/(\$G\$2+1))*(\$C\$3-\$C\$4)/(\$C\$2-1)<=\$C\$3,
\$G\$4+MOD((ROW()-ROW(\$B\$7)),(\$G\$2+1))*(\$G\$3-\$G\$4)/(\$G\$2-1),""),"")``````
Cell E7::
Code:
``````=IF(INT((ROW()-ROW(\$E\$7)+1)/(\$C\$2+1))<> (((ROW()-ROW(\$E\$7)+1)/(\$C\$2+1))),
IF(\$G\$4+INT((ROW()-ROW(\$E\$7)+1)/(\$C\$2+1))*(\$G\$3-\$G\$4)/(\$G\$2-1)<=\$G\$3,
\$C\$4+MOD((ROW()-ROW(\$E\$7)),(\$C\$2+1))*(\$C\$3-\$C\$4)/(\$C\$2-1),""),"")``````
Cell F7:
Code:
``````=IF(INT((ROW()-ROW(\$F\$7)+1)/(\$C\$2+1))<> (((ROW()-ROW(\$F\$7)+1)/(\$C\$2+1))),
IF(\$G\$4+INT((ROW()-ROW(\$F\$7)+1)/(\$C\$2+1))*(\$G\$3-\$G\$4)/(\$G\$2-1)<=\$G\$3,
\$G\$4+INT((ROW()-ROW(\$F\$7)+1)/(\$C\$2+1))*(\$G\$3-\$G\$4)/(\$G\$2-1),""),"")``````
5) Copy down 30*50 rows (max. anticipated plus blanks!).

6) The above formulas and the input cells (Item 1 above) can be dragged around and will respond to row insertions properly.

Regards.

Replies
0
Views
207

1,191,686
Messages
5,988,071
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back