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

monirg

Well-known Member
Joined
Jan 11, 2005
Messages
629
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 Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.
 
Upvote 0

Forum statistics

Threads
1,222,227
Messages
6,164,722
Members
451,912
Latest member
HMF009

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top