| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
w/s Formula to Automatically Create Two 2-equi-interval Tables
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 "monir" wrote in message ... 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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
w/s Formula to Automatically Create Two 2-equi-interval Tables
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 a .....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. "monir" wrote: 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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
w/s Formula to Automatically Create Two 2-equi-interval Tables
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 "monir" wrote in message ... 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 a ....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. "monir" wrote: 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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
w/s Formula to Automatically Create Two 2-equi-interval Tables
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 a .....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.} "monir" wrote: 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 a ....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. "monir" wrote: 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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
w/s Formula to Automatically Create Two 2-equi-interval Tables
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 "monir" wrote in message ... 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 a ....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.} "monir" wrote: 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 a ....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. "monir" wrote: 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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
w/s Formula to Automatically Create Two 2-equi-interval Tables
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 the 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!). ====================================== "Bernie Deitrick" wrote: 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 "monir" wrote in message ... 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 a ....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.} "monir" wrote: 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 a ....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. "monir" wrote: 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. |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
w/s Formula to Automatically Create Two 2-equi-interval Tables
Monir,
Sure, post away, as long as you can assure me that "MS XL w/s DG" is nothing bad ;-) Bernie "monir" wrote in message ... 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 the 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!). ====================================== "Bernie Deitrick" wrote: 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 "monir" wrote in message ... 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 a ....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.} "monir" wrote: 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 a ....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. "monir" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how i create a interval | Excel Discussion (Misc queries) | |||
| How do you create a series based on a specific interval? | Excel Discussion (Misc queries) | |||
| Possible to create formula columns within Pivot Tables? | Excel Discussion (Misc queries) | |||
| need help with setting up a scheduling and allocation of lab equi | New Users to Excel | |||
| How do i create multiple pivot tables with one field automatically | Excel Discussion (Misc queries) | |||
Linear Mode
