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

[monirg]

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.

 

[monirg]

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::

=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::

=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::

=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:

=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.