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

M

monir

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

Bernie Deitrick

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
Click to expand...

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
 
M

monir

Bernie;

Thank you kindly for your time and prompt reply.
Please allow me time to try your "complex" formulas.

Regards.
 
M

monir

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

Bernie Deitrick

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
 
M

monir

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

Bernie Deitrick

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
 
M

monir

Bernie;

Sorry to bother you again!

1) The problem of mixing up the Y2 values at each segment's end appears to
be solved by replacing ALL ROW references in your formulas by the ref to the
starting row in its column, and then copy down.

2) no. of X values::C2 =10 no. of Y values::G2 =15
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.
The LAST displayed segments in both Tables are:
(notice there's no more mix-up now in the Y2 values)

.....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 -2.893
1.00 -4.821 1.000 -2.893
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 (no more segments displayed in Table 2. Strange!!)

3) The formulas entered at :F7 is:
=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))/($C$2+1))*($G$3-$G$4)/($G$2-1),""),"")

and the formula in E7 is:
=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),""),"")

4) As you could see, the other problem remains however.
Table 2 DOES NOT display X2 & Y2 for the last 3 segments (in this example)
corresponding to the constant Y2 values of:
-1.929, -0.964 and 0.000.

Will keep trying.

Regards.

:
 
B

Bernie Deitrick

Alright, one more time ;-)

(Sorry...)

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),""),"")

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),""),"")


F7
=IF(INT((ROW()-ROW($F$7)+1)/($C$2+1))<> (((ROW()-ROW($F$7)+1)/($C$2+1))),
IF($C$4+MOD((ROW()-ROW($F$7)),($C$2+1))*($C$3-$C$4)/($C$2-1)
<=$C$3,$C$4+MOD((ROW()-ROW($F$7)),($C$2+1))*($C$3-$C$4)/($C$2-1),""),"")

G7
=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),""),"")


HTH,
Bernie
MS Excel MVP
 
M

monir

Bernie;

I posted my latest reply this morning before checking for new messages. My
apologies!

1) I'm glad that your recent suggestion concurs with mine.

2) In addition to my earlier observation regarding the formulas (entered in
E7 and F7 and copied down) missing few segments in Table 2:
if one reverses the number of X values and Y values:
no. of X values::C2 =15 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

now the LAST displayed segments are:

.....TABLE 1.......TABLE 2
....X1.......Y1......X2........Y2
......................blank ...blank
1.00 -13.500 0.930 +10.500
1.00 -12.000 0.935 +10.500
1.00 -10.500 0.940 +10.500
1.00 -9.000 0.945 +10.500
1.00 -6.000 0.950 +10.500
1.00 -4.500 0.955 +10.500
1.00 -3.000 0.977 +10.500
1.00 -1.500 0.960 +10.500
1.00 0.000 0.965 +10.500
blank ...blank..0.970 +10.500
.......................0.975 +10.500
.......................0.980 +10.500
.......................0.985 +10.500
.......................0.990 +10.500
.......................0.995 +10.500
.......................1.000 +10.500
........................blank.....blank
You notice that Table 2. produces now (for this example) 7 ADDITIONAL
segments for Y constant: +1.50, +3.00, +4.50, +6.00, +7.50, +9.00 and +10.50:
a) ALL these additional segments are for Y values EXCEEDING the specified
"max. Y value = 0.00";
b) The number of segments generated in Table 2. is now 17 despite the
specified "no. of Y values =10"; and
c) Table 1 appears to be working perfectly all the time!

Regards.
 
B

Bernie Deitrick

Monir,

Sorry, I copied the wrong conditional. In F7, use

=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,$C$4+MOD((ROW()-ROW($F$7)),($C$2+1))*($C$3-$C$4)/($C$2-1),""),"")

HTH,
Bernie
MS Excel MVP
 
M

monir

Bernie;

OK. You're getting very close to perfect the formulas which's very
encouraging!

1) Table 1: X1 and Y1 tabulation is correct, with both columns stopping at
the end of the last segment.

2) Table 2: X2 and Y2 tabulation is perfect with the following caveat:
Y2 stops correctly after the last segment, but X2 continues on displaying
the correct segment values over and over again with nothing in the Y2 column.

3) So for the same example:
no. of X values::C2 =15 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
The LAST displayed segments are:
.....TABLE 1.......TABLE 2
....A7.......B7.....E7........F7
....X1.......Y1.....X2........Y2
.........................................
1.00 -13.500 0.930 0.000
1.00 -12.000 0.935 0.000
1.00 -10.500 0.940 0.000
1.00 -9.000 0.945 0.000
1.00 -7.500 0.950 0.000
1.00 -6.000 0.955 0.000
1.00 -4.500 0.960 0.000
1.00 -3.000 0.965 0.000
1.00 -1.500 0.970 0.000
1.00 0.000 0.975 0.000
blank ...blank..0.980 0.000
.......................0.985 0.000
.......................0.990 0.000
.......................0.995 0.000
.......................1.000 0.000
.................... ..blank..blank (should stop here!)
.......................0.930 blank
.......................0.935 blank
.......................0.940 blank
.......................0.945 blank
.......................0.950 blank
.......................0.955 blank
.......................0.960 blank
.......................0.965 blank
.......................0.970 blank
.......................0.975 blank
.......................0.980 blank
.......................0.985 blank
.......................0.990 blank
.......................0.995 blank
.......................1.000 blank

.................... ..blank..blank
.......................0.930 blank
.......................0.935 blank
.......................0.940 blank
.......................and on and on....

4) The (latest) formula for X2 entered in E7 (and copied down) is:
=IF(INT((ROW()-ROW($E$7)+1)/($C$2+1))<> (((ROW()-ROW($E$7)+1)/($C$2+1))),
IF($C$4+MOD((ROW()-ROW($E$7)),($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),""),"")

Regards.
{Just retrieved your latest reply (8:51 AM PST) and will try your corrected
conditional right now and report back.}
 
B

Bernie Deitrick

Monir,

This formula for X2 fixed it... did you miss my last post?

=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),""),"")

HTH,
Bernie
MS Excel MVP
 
M

monir

Bernie;

Now it really wotks perfectly!
Thanks again for your tremendous help in resolving the issue. Greatly
appreciated!

With your permission, I'd like to post your formulas in the other Excel
Forum with the proper acknowledgement. (Please see below)
I had earlier posted my question there but none of their experts have
responded. Someone might learn something or even save some time as a result.

Please let me know if you agree.

Kind regards.
Monir

======================================
Here's what I'd post there:

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 X & Y 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
....X1.......Y1........X2........Y2
....A7.......B7........E7........F7
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
0.930 0.000 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.000 0.980 -12.000
0.940 -7.500 0.990 -12.000
0.940 -6.000 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 proved 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!).
======================================
 
B

Bernie Deitrick

Monir,

Sure, post away, as long as you can assure me that "MS XL w/s DG" is nothing
bad ;-)

Bernie
 
Top