Monir,

Oops, I had left in a reference to ROW($B$4), which also needed to be changed.

Just to be safe, and to make the formulas completely independent, use this is 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),""),"")

and this in 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),""),"")

and in E7
=IF(INT((ROW()-ROW($E$7)+1)/($C$2+1))< (((ROW()-ROW($E$7)+1)/($C$2+1))),
IF($C$4+INT((ROW()-ROW($E$7)+1)/($C$2+1))*($C$3-$C$4)/($C$2+1)
<=$C$3,$C$4+MOD((ROW()-ROW($E$7)),($C$2+1))*($C$3-$C$4)/($C$2-1),""),"")

and in G7
=IF(INT((ROW()-ROW($F$7)+1)/($C$2+1))< (((ROW()-ROW($F$7)+1)/($C$2+1))),
IF($C$4+INT((ROW()-ROW($F$7)+1)/($C$2+1))*($C$3-$C$4)/($C$2+1)
<=$C$3,$G$4+INT((ROW()-ROW($F$7)+1)/($C$2+1))*($G$3-$G$4)/($G$2-1),""),"")

HTH,
Bernie
MS Excel MVP


"monir" wrote in message
...
Hi Bernie;

I'm sorry, but there's still a problem!

1) no. of X values::C2 =8 no. of Y values::G2 =10
max. X value::C3 =1.00 max. Y value::G3 =0.00
min. X value::C4 =0.930 min. Y value ::G4 =-13.50

2) Table 1 starts at A7:B7 and ALWAYS works fine.
Table 2 starts at E7:F7 and produces wrong Y values at the end of each
segment.
For example, the top segment shows (wrong values bracketed for clarity):
....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 (-12.000)
0.930 -3.000 1.000 (-12.000)
0.930 -1.500 blank....blank
0.930 0.000 0.930 -12.000
blank....blank 0.940 -12.000
...........................................
3) The formulas entered at :F7 is:
F7:: = IF(INT((ROW()-ROW($C$7)+1)/($C$2+1))<
(((ROW()-ROW($C$7)+1)/($C$2+1))),
IF($C$4+INT((ROW()-ROW($C$7)+1)/($C$2+1))*($C$3-$C$4)/($C$2+1)<=$C$3,
$G$4+INT((ROW()-ROW($G$4))/($C$2+1))*($G$3-$G$4)/($G$2-1),""),"")

and the formula in E7 is:
=IF(INT((ROW()-ROW($C$7)+1)/($C$2+1))< (((ROW()-ROW($C$7)+1)/($C$2+1))),
IF($C$4+INT((ROW()-ROW($C$7)+1)/($C$2+1))*($C$3-$C$4)/($C$2+1)<=$C$3,$C$4+
MOD((ROW()-ROW($A$7)), ($C$2+1))*($C$3-$C$4)/($C$2-1),""),"")

4) Furthermore, if you enter the numbers: C2:: 10 and G2:: 15
Table 1. produces again the correct values X1, Y1, while Table 2's LAST
displayed segment is:
....TABLE 1.........TABLE 2
...X1.......Y1........X2........Y2
.....................blank ...blank
1.00 -13.500 0.930 -2.893
1.00 -12.536 0.938 -2.893
1.00 -11.571 0.946 -2.893
1.00 -10.607 0.953 -2.893
1.00 -9.643 0.961 -2.893
1.00 -8.679 0.969 -2.893
1.00 -7.714 0.977 -2.893
1.00 -6.750 0.984 -2.893
1.00 -5.786 0.992 (-1.929)
1.00 -4.821 1.000 (-1.929)
1.00 -3.857 blank ...blank
1.00 -2.893 blank ...blank
1.00 -1.929 blank ...blank
1.00 -0.964 blank ...blank
1.00 0.000 blank ...blank

which not only mixes the values of Y2 at the end of the segment, but also
misses the entire segments for Y2 (constant value) of -1.929, -0.964 and
0.000.

Will check again your replies in case I missed something.

Kind regards.

"Bernie Deitrick" wrote:

Monir,

Change all instances of
-ROW($A$4)
to
-ROW($A$XXX)+1

where XXX is the row where the formula is entered - like so if you were
entering it into cell A7:

=IF(INT((ROW()-ROW($A$7)+1)/($B$2+1))<
(((ROW()-ROW($A$7)+1)/($B$2+1))),IF($A$4+INT((ROW()-ROW($A$7)+1)/($B$2+1))*($A$3-$A$4)/($A$2-1)<=$A$3,$A$4+INT((ROW()-ROW($A$7)+1)/($B$2+1))*($A$3-$A$4)/($A$2-1),""),"")

Change all four in a similar manner, and then the formulas can be dragged
around and will respond to row insertions properly.

I guess I took your desire to have the table start in row 5 too seriously
;-)

Bernie


"monir" wrote in message
...
Bernie;

Absolutely perfect! Your formulas work fine and as desired. Excellent!

A very tiny problem however.
I'm able to move any or all of the 6 input cells around to suit with no
problem, but can't drag down or insert rows at the top of the two
generated
tables. I've tried to change row ref. $A$4 and/or row ref. $B$5 with no
success.

If it's not too much trouble, please let me know which row ref. cells in
your formulas I should change.

Thanks again for your tremendous help.

"Bernie Deitrick" wrote:

Monir,

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

I'm assuming you meant the Y values are in B2,B3,B4

In A5
=IF(INT((ROW()-ROW($A$4))/($B$2+1))< (((ROW()-ROW($A$4))/($B$2+1))),
IF($A$4+INT((ROW()-ROW($A$4))/($B$2+1))*($A$3-$A$4)/($A$2-1)
<=$A$3,$A$4+INT((ROW()-ROW($A$4))/($B$2+1))*($A$3-$A$4)/($A$2-1),""),"")

In B5
=IF(INT((ROW()-ROW($A$4))/($B$2+1))< (((ROW()-ROW($A$4))/($B$2+1))),
IF($A$4+INT((ROW()-ROW($A$4))/($B$2+1))*($A$3-$A$4)/($A$2-1)
<=$A$3,$B$4+MOD((ROW()-ROW($B$5)),($B$2+1))*($B$3-$B$4)/($B$2-1),""),"")

In C5
=IF(INT((ROW()-ROW($A$4))/($A$2+1))< (((ROW()-ROW($A$4))/($A$2+1))),
IF($A$4+INT((ROW()-ROW($A$4))/($A$2+1))*($A$3-$A$4)/($A$2+1)
<=$A$3,$A$4+MOD((ROW()-ROW($A$5)),($A$2+1))*($A$3-$A$4)/($A$2-1),""),"")

In D5
=IF(INT((ROW()-ROW($A$4))/($A$2+1))< (((ROW()-ROW($A$4))/($A$2+1))),
IF($A$4+INT((ROW()-ROW($A$4))/($A$2+1))*($A$3-$A$4)/($A$2+1)
<=$A$3,$B$4+INT((ROW()-ROW($B$4))/($A$2+1))*($B$3-$B$4)/($B$2-1),""),"")

Take out the extra line breaks, then copy down 1500 rows.

HTH,
Bernie
MS Excel MVP


"monir" wrote in message
...
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 (or start) of
each
segment in each Table (as shown above)}.

Thank you kindly.