| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please explain this formula
Hi All
I am totally out my depth but I need to Learn.In b11 on a worksheet is the following. =INDIRECT("'date details'!"&ADDRESS(3,(INT(ROW()/80)+3))) returns the following results the date that is in cell c3 on worksheet "date details "appears b11 when the above formula is placed in it the date that is in cell d3 on worksheet "date details" appears B91when the above formula is placed in it the date that is in cell E3 on worksheet "date details" appears B171when the above formula is placed in it and on and on whenever you pace that formula How is the 80 row sequence worked out and how is it relative to the intial. cell of B11 Remember I'm new to this Best Stew |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please explain this formula
In B11
ROW() =11 INT(ROW()/80)+3) = 3 ADDRESS(3,INT(ROW()/80)+3)) = C3 In B91 ROW() =91 INT(ROW()/80)+3) = 4 ADDRESS(3,INT(ROW()/80)+3)) = D3 etc. -- __________________________________ HTH Bob "stew" wrote in message ... Hi All I am totally out my depth but I need to Learn.In b11 on a worksheet is the following. =INDIRECT("'date details'!"&ADDRESS(3,(INT(ROW()/80)+3))) returns the following results the date that is in cell c3 on worksheet "date details "appears b11 when the above formula is placed in it the date that is in cell d3 on worksheet "date details" appears B91when the above formula is placed in it the date that is in cell E3 on worksheet "date details" appears B171when the above formula is placed in it and on and on whenever you pace that formula How is the 80 row sequence worked out and how is it relative to the intial. cell of B11 Remember I'm new to this Best Stew |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please explain this formula
So" int "rounds it down to the nearest whole number.
Next question is there a re calculation that allows you to achieve that 1 increase ie if my formula is in Row 475 how can you work out to get int(row()/? to increase on a 49 row increase working on achieving Plus 1 every time Thanks stewart "Bob Phillips" wrote: In B11 ROW() =11 INT(ROW()/80)+3) = 3 ADDRESS(3,INT(ROW()/80)+3)) = C3 In B91 ROW() =91 INT(ROW()/80)+3) = 4 ADDRESS(3,INT(ROW()/80)+3)) = D3 etc. -- __________________________________ HTH Bob "stew" wrote in message ... Hi All I am totally out my depth but I need to Learn.In b11 on a worksheet is the following. =INDIRECT("'date details'!"&ADDRESS(3,(INT(ROW()/80)+3))) returns the following results the date that is in cell c3 on worksheet "date details "appears b11 when the above formula is placed in it the date that is in cell d3 on worksheet "date details" appears B91when the above formula is placed in it the date that is in cell E3 on worksheet "date details" appears B171when the above formula is placed in it and on and on whenever you pace that formula How is the 80 row sequence worked out and how is it relative to the intial. cell of B11 Remember I'm new to this Best Stew |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please explain this formula
You mean that row 475 refers to a row 1, 478 refers to a row 50? Is that
it? What column? -- __________________________________ HTH Bob "stew" wrote in message ... So" int "rounds it down to the nearest whole number. Next question is there a re calculation that allows you to achieve that 1 increase ie if my formula is in Row 475 how can you work out to get int(row()/? to increase on a 49 row increase working on achieving Plus 1 every time Thanks stewart "Bob Phillips" wrote: In B11 ROW() =11 INT(ROW()/80)+3) = 3 ADDRESS(3,INT(ROW()/80)+3)) = C3 In B91 ROW() =91 INT(ROW()/80)+3) = 4 ADDRESS(3,INT(ROW()/80)+3)) = D3 etc. -- __________________________________ HTH Bob "stew" wrote in message ... Hi All I am totally out my depth but I need to Learn.In b11 on a worksheet is the following. =INDIRECT("'date details'!"&ADDRESS(3,(INT(ROW()/80)+3))) returns the following results the date that is in cell c3 on worksheet "date details "appears b11 when the above formula is placed in it the date that is in cell d3 on worksheet "date details" appears B91when the above formula is placed in it the date that is in cell E3 on worksheet "date details" appears B171when the above formula is placed in it and on and on whenever you pace that formula How is the 80 row sequence worked out and how is it relative to the intial. cell of B11 Remember I'm new to this Best Stew |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please explain this formula
Dear Bob
Thanks for your time B475 will contain the formula =INDIRECT("'date details'!"&ADDRESS(3,(INT(ROW()/?)+?))) In B524 THE FORMULA WOULD BE REPEATED TO GIVE THE INCREASE IN C3 TO D3 My question is INT(ROW() in B475 = 475 the next location is B524 INT(ROW() IN b524= 524 is there a set calculation to work out the divisor that would give the +1 to allow me to move along the C3,D3,E3,F3 increase every time when dividing these Numbers. Sorry if I am not expaining this very well "Bob Phillips" wrote: You mean that row 475 refers to a row 1, 478 refers to a row 50? Is that it? What column? -- __________________________________ HTH Bob "stew" wrote in message ... So" int "rounds it down to the nearest whole number. Next question is there a re calculation that allows you to achieve that 1 increase ie if my formula is in Row 475 how can you work out to get int(row()/? to increase on a 49 row increase working on achieving Plus 1 every time Thanks stewart "Bob Phillips" wrote: In B11 ROW() =11 INT(ROW()/80)+3) = 3 ADDRESS(3,INT(ROW()/80)+3)) = C3 In B91 ROW() =91 INT(ROW()/80)+3) = 4 ADDRESS(3,INT(ROW()/80)+3)) = D3 etc. -- __________________________________ HTH Bob "stew" wrote in message ... Hi All I am totally out my depth but I need to Learn.In b11 on a worksheet is the following. =INDIRECT("'date details'!"&ADDRESS(3,(INT(ROW()/80)+3))) returns the following results the date that is in cell c3 on worksheet "date details "appears b11 when the above formula is placed in it the date that is in cell d3 on worksheet "date details" appears B91when the above formula is placed in it the date that is in cell E3 on worksheet "date details" appears B171when the above formula is placed in it and on and on whenever you pace that formula How is the 80 row sequence worked out and how is it relative to the intial. cell of B11 Remember I'm new to this Best Stew |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please explain this formula
You don't have INT(ROW()), you have INT(ROW()/80)
Look at where the closing parenthesis of the INT() function is. If you want to step one column for every 49 rows, rather than one column for every 80 rows, then change INT(ROW()/80) to INT(ROW()/49) As for the second question mark in your formula, if you want the formula in B475 to point to the third column (i.e. column C), then work out what INT(475/49) is, then see what you need to add to it (or in this case subtract) to get the second argument of your ADDRESS function to be 3 in order to point to column C. If you are struggling to work out what your formula is doing, you can always break it into manageable chunks so that you can see what it's doing. If you can't work out where the INDIRECT function is pointing, you can either just use =("'date details'!"&ADDRESS(3,(INT(ROW()/80)+3))) to see the address as a text string, or =INT(ROW()/80)+3 to see the column number. Then if you change your 80 to 49, or change your 3 to -6, you can see what the effect is. -- David Biddulph "stew" wrote in message ... Dear Bob Thanks for your time B475 will contain the formula =INDIRECT("'date details'!"&ADDRESS(3,(INT(ROW()/?)+?))) In B524 THE FORMULA WOULD BE REPEATED TO GIVE THE INCREASE IN C3 TO D3 My question is INT(ROW() in B475 = 475 the next location is B524 INT(ROW() IN b524= 524 is there a set calculation to work out the divisor that would give the +1 to allow me to move along the C3,D3,E3,F3 increase every time when dividing these Numbers. Sorry if I am not expaining this very well "Bob Phillips" wrote: You mean that row 475 refers to a row 1, 478 refers to a row 50? Is that it? What column? -- __________________________________ HTH Bob "stew" wrote in message ... So" int "rounds it down to the nearest whole number. Next question is there a re calculation that allows you to achieve that 1 increase ie if my formula is in Row 475 how can you work out to get int(row()/? to increase on a 49 row increase working on achieving Plus 1 every time Thanks stewart "Bob Phillips" wrote: In B11 ROW() =11 INT(ROW()/80)+3) = 3 ADDRESS(3,INT(ROW()/80)+3)) = C3 In B91 ROW() =91 INT(ROW()/80)+3) = 4 ADDRESS(3,INT(ROW()/80)+3)) = D3 etc. -- __________________________________ HTH Bob "stew" wrote in message ... Hi All I am totally out my depth but I need to Learn.In b11 on a worksheet is the following. =INDIRECT("'date details'!"&ADDRESS(3,(INT(ROW()/80)+3))) returns the following results the date that is in cell c3 on worksheet "date details "appears b11 when the above formula is placed in it the date that is in cell d3 on worksheet "date details" appears B91when the above formula is placed in it the date that is in cell E3 on worksheet "date details" appears B171when the above formula is placed in it and on and on whenever you pace that formula How is the 80 row sequence worked out and how is it relative to the intial. cell of B11 Remember I'm new to this Best Stew |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Please explain formula to me | Excel Worksheet Functions | |||
| Please explain function/formula | Excel Worksheet Functions | |||
| Could any one Explain this Formula | Excel Worksheet Functions | |||
| Let me see if I can explain this... | Excel Worksheet Functions | |||
| Please Explain Formula | Excel Worksheet Functions | |||
Hybrid Mode
