Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 293
Default Please explain this formula

Dear David , and Bob

Thank you. You have taught and old dog a new trick. I look forward to
learning More. It made it so much easier when I was able to see where the
Formula took me as a text string.

Thanks Again

Stew

"David Biddulph" wrote:

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Please explain formula to me Paul Excel Worksheet Functions 2 December 10th 06 02:57 AM
Please explain function/formula Tara H Excel Worksheet Functions 6 July 24th 06 07:16 PM
Could any one Explain this Formula John Excel Worksheet Functions 8 April 22nd 06 07:15 PM
Let me see if I can explain this... jsc3489 Excel Worksheet Functions 0 July 22nd 05 05:04 PM
Please Explain Formula Karen Excel Worksheet Functions 4 June 16th 05 11:46 PM


All times are GMT +1. The time now is 10:50 PM.

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

About Us

"It's about Microsoft Excel"