Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > w/s Formula to Automatically Create Two 2-equi-interval Tables ??
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 215
Default w/s Formula to Automatically Create Two 2-equi-interval Tables ??
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.

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5,441
Default w/s Formula to Automatically Create Two 2-equi-interval Tables ??
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.



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 215
Default w/s Formula to Automatically Create Two 2-equi-interval Tables
Bernie;

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

Regards.

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




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 215
Default w/s Formula to Automatically Create Two 2-equi-interval Tables
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.




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5,441
Default w/s Formula to Automatically Create Two 2-equi-interval Tables
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.





Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 215
Default w/s Formula to Automatically Create Two 2-equi-interval Tables
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.






Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5,441
Default w/s Formula to Automatically Create Two 2-equi-interval Tables
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.








Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 215
Default w/s Formula to Automatically Create Two 2-equi-interval Tables
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 a
(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.

"monir" wrote:


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.






Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
how i create a interval demetrio29 Excel Discussion (Misc queries) 3 September 23rd 08 10:50 PM
How do you create a series based on a specific interval? Steve Hickman Excel Discussion (Misc queries) 4 July 2nd 08 01:48 PM
Possible to create formula columns within Pivot Tables? Pat Dools Excel Discussion (Misc queries) 1 February 28th 08 08:14 PM
need help with setting up a scheduling and allocation of lab equi Gaetre New Users to Excel 2 February 16th 07 03:00 PM
How do i create multiple pivot tables with one field automatically Leonardo Excel Discussion (Misc queries) 1 June 16th 06 09:14 PM


All times are GMT +1. The time now is 05:58 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement