How do I reference a cell as a row number in a formula?

The formula I currently have is: =SQRT(SUM(F5280:F6240)/(N3-M3))
the row numbers 5280 and 6240 are values found in cells M3 and N3, 
respectively.
However I need to copy this formula down a column, and I currently must go 
through and edit each formula to change those two values to the neighboring 
cells.  i.e. that formula is in cell O3, and when copied to O4 the desired 
rows of column F to be referenced will be the numerical values in M4 and N4.  



=SQRT(SUM(F____:F____)/(N3-M3))  please fill in the blanks?
0
Utf
12/13/2009 6:38:01 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
653 Views

Similar Articles

[PageSpeed] 7

=SQRT(SUM(INDIRECT("F"&M3&":F"&N3))/(N3-M3))
--
David Biddulph

"wileye" <wileye@discussions.microsoft.com> wrote in message 
news:42FBCACC-7537-4315-ADB7-1A31AA06025A@microsoft.com...
> The formula I currently have is: =SQRT(SUM(F5280:F6240)/(N3-M3))
> the row numbers 5280 and 6240 are values found in cells M3 and N3,
> respectively.
> However I need to copy this formula down a column, and I currently must go
> through and edit each formula to change those two values to the 
> neighboring
> cells.  i.e. that formula is in cell O3, and when copied to O4 the desired
> rows of column F to be referenced will be the numerical values in M4 and 
> N4.
>
>
>
> =SQRT(SUM(F____:F____)/(N3-M3))  please fill in the blanks? 


0
David
12/13/2009 6:51:48 PM
Try this:

=3DSQRT(SUM(INDIRECT("F"&M3&":F"&N3))/(N3-M3))

then copy it down as required.

Hope this helps.

Pete

On Dec 13, 6:38=A0pm, wileye <wil...@discussions.microsoft.com> wrote:
> The formula I currently have is: =3DSQRT(SUM(F5280:F6240)/(N3-M3))
> the row numbers 5280 and 6240 are values found in cells M3 and N3,
> respectively.
> However I need to copy this formula down a column, and I currently must g=
o
> through and edit each formula to change those two values to the neighbori=
ng
> cells. =A0i.e. that formula is in cell O3, and when copied to O4 the desi=
red
> rows of column F to be referenced will be the numerical values in M4 and =
N4. =A0
>
> =3DSQRT(SUM(F____:F____)/(N3-M3)) =A0please fill in the blanks?

0
Pete_UK
12/13/2009 6:52:34 PM
Hi,

Try this

=SQRT(SUM(INDIRECT("F" &M3&":F" &N3))/(N3-M3))

Mike

"wileye" wrote:

> The formula I currently have is: =SQRT(SUM(F5280:F6240)/(N3-M3))
> the row numbers 5280 and 6240 are values found in cells M3 and N3, 
> respectively.
> However I need to copy this formula down a column, and I currently must go 
> through and edit each formula to change those two values to the neighboring 
> cells.  i.e. that formula is in cell O3, and when copied to O4 the desired 
> rows of column F to be referenced will be the numerical values in M4 and N4.  
> 
> 
> 
> =SQRT(SUM(F____:F____)/(N3-M3))  please fill in the blanks?
0
Utf
12/13/2009 6:57:02 PM
Thank you!  You just saved me several hours of editing each formula!

"Mike H" wrote:

> Hi,
> 
> Try this
> 
> =SQRT(SUM(INDIRECT("F" &M3&":F" &N3))/(N3-M3))
> 
> Mike
> 
> "wileye" wrote:
> 
> > The formula I currently have is: =SQRT(SUM(F5280:F6240)/(N3-M3))
> > the row numbers 5280 and 6240 are values found in cells M3 and N3, 
> > respectively.
> > However I need to copy this formula down a column, and I currently must go 
> > through and edit each formula to change those two values to the neighboring 
> > cells.  i.e. that formula is in cell O3, and when copied to O4 the desired 
> > rows of column F to be referenced will be the numerical values in M4 and N4.  
> > 
> > 
> > 
> > =SQRT(SUM(F____:F____)/(N3-M3))  please fill in the blanks?
0
Utf
12/13/2009 7:18:01 PM
Glad I could help and thanks for the feedback

"wileye" wrote:

> Thank you!  You just saved me several hours of editing each formula!
> 
> "Mike H" wrote:
> 
> > Hi,
> > 
> > Try this
> > 
> > =SQRT(SUM(INDIRECT("F" &M3&":F" &N3))/(N3-M3))
> > 
> > Mike
> > 
> > "wileye" wrote:
> > 
> > > The formula I currently have is: =SQRT(SUM(F5280:F6240)/(N3-M3))
> > > the row numbers 5280 and 6240 are values found in cells M3 and N3, 
> > > respectively.
> > > However I need to copy this formula down a column, and I currently must go 
> > > through and edit each formula to change those two values to the neighboring 
> > > cells.  i.e. that formula is in cell O3, and when copied to O4 the desired 
> > > rows of column F to be referenced will be the numerical values in M4 and N4.  
> > > 
> > > 
> > > 
> > > =SQRT(SUM(F____:F____)/(N3-M3))  please fill in the blanks?
0
Utf
12/13/2009 7:20:01 PM
Reply:

Similar Artilces:

count a pair of numbers in row in a table
Hello, my question is: we have the following table: 34 29 13 15 7 15 8 40 11 24 13 6 8 21 38 9 17 23 1 4 22 38 42 37 16 1 18 11 37 41 5 42 18 33 45 9 1 21 41 15 41 1 27 23 42 23 29 7 38 18 42 12 26 34 36 and this one in another sheet 1 2 3 1 2 3 I want to fill the second table with the sum of how many times the numbers if each row and column appear in the same row in the first table. for example: how many time the numbers 2 and 3 appear together in the same row on the first table Nik, Assume t...

removing rows.
How to remove rows in one sheet according to values (text) entered in another sheet. If I do not enter text "PTA" (but anything else) in a sheet named "Input Data", on another sheet named "2 Page" these rows (11,12,13,14,15,16,17, 20,21,22,23,24,25) should dissappear. Also If I enter anything else, other than word "Vertical" in cell F3 of "Input Data", rows 101,103,105,107,109,111,113 should dissappear in the sheet named "2 Page" when these conditions not true I want these rows to re-appear. If I need to use ma...

Preserving Cell Formats in Excel Query
I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't carry through to the query results. Is there a way to carry original formatting through to Excel Query results Any insight would be appreciated Karen S No, you can import the data, but not the formats. If you're importing programmatically, you could apply the formatting as part of the import procedure. Karen S wrote: > I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't car...

Formula result shows as zero Excel 2003
Formula as shown =IF(C12<>"",+C11-C12,"") appears in D12. This is the same formula as rows above but rows above show correct result whereas D12 and subsequent rows show only zero. If I do an F2 and F9, the correct result shows in the Formula Editing bar so formula is working correctly. I have tried copying both formula and cell formatting from previous rows which do display their result correctly but still doesn't fix the problem. Any assistance would be much appreciated Hi maybe automatic calculation is disabled. Check 'Tools - Options - Calculate...

Conditional Formula based on previous date + 30
I have a spread sheet that caluclates when proposals expire. Date Sent Follow Up date January 13, 2010 February 12, 2010 What I am looking for is for the Follow up date to turn RED when the date is expired (over the date listed). Just use CF with a formula of =B2>TODAY() -- HTH Bob "Chris" <Chris@discussions.microsoft.com> wrote in message news:66DE65F1-F041-434C-86A7-B13635C6914F@microsoft.com... >I have a spread sheet that calu...

Format cells with dates
Is there a way to format cells so that dates would change when the lead date is changed. for example, when I input monday's date, tue, wed, thur, etc will follow suit. Pat, Assuming the first date is in A1 B1: =A1+1 C1: =B 1+1 etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pat" <anonymous@discussions.microsoft.com> wrote in message news:115681B0-348E-447E-BB1F-F8347CFDB19B@microsoft.com... > Is there a way to format cells so that dates would change when the lead date is...

URGENT!!! Problem with row data being truncated in a copy worksheet sub #2
Dave, Thanks for responding. I tried this but I could not get it t work in conjunction with the entire module. It dies right afte copying and PasteSpecial Values It does not kill the temp file or loa the newly created sheet into an e-mail. Any Ideas -- Doctor ----------------------------------------------------------------------- DoctorV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=514 View this thread: http://www.excelforum.com/showthread.php?threadid=26863 You may want to post your current procedure. DoctorV wrote: > > Dave, Thanks for respon...

Restricting number of viewings?
Without using macros, is there a way to allow someone to view an application only once or twice, after which point they could no longer view the spreadsheet (for example, if I want to send a worksheet sample that I don't want to be used permenantly). I don't think so, you can restrict access with macros but this can be overcome by opening the workbook with macros disabled. You can also write code to prevent a workbook being opened unless macros are enabled, but this is overcome quite easily by anyone with a little knowledge of Excel and VBA. In short, Excel is not designed for securit...

Tricky Formula.. Please Help
I have created a time roster.. easy in each day I have start,end and break deduction.. still no probs I need to create an output formula for hours worked after a 16:00 from the start and end range on a day. So in the roster it will still display normal hours, however I will create a field for hours after 16:00. this is for calculating a different wage rate. Thanks Aaron See response in .programming -- HTH RP (remove nothere from the email address if mailing direct) "Aaron H" <aaron@istarnetworks.com.au> wrote in message news:uU96kXI$EHA.2076@TK2MSFTNGP15.phx.gbl... ...

Match formula to match values in multiple columns
Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) I am not sure I have understood correectly. Please go through the below example With data as below if you need to retrive the name of the 1st Rank holder from London. D2 = 1 D3 = London In D4 apply the below formula =INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=D2)*($C$2:$C$9=D3),0)) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'...

Page numbering 12-04-09
I have taken the trouble to review all the related previous posts but do not see the answer to meet my needs. I have a 7 page document which consists of a front and back cover 2 pages each and so far 3 pages of report. Page 2 is followed by a section break next page while page 5 is followed by a section break next page. In addition I also put a section break at the end of the document which I should not need. Numbering starts on page 3 as page 1 of Y which seems to work fine. However, page 4 and 5 do not display page numbers while page 6 displays page 1 of 6 and page 7 i...

How to goto cell containing specific date
Thought I asked this before, but can't find the thread w/ my question or any replies... I have a worksheet wih a full year's dates in the cells running down a colum, with other data for each date in the the adjacent columns; Instead of scrolling up & down to a cell with a specific date I'm looking for, is there another way to goto a cell containing a specific date? (e.g., today(), or another specific date) In case this is pertinent: the date series begins with the entry of one date (e.g., 01/01/2010 in cell A1), with the dates in subsequent rows arrived at ...

first row
Hi group Im using excel as a telephone database (cannot get my head around access). Anyway with my first row which has name, contact number etc etc, I would like to always keep visible, so as when I scroll down the list of names I can always see this first row, is this possible? or do I have use access?? TIA -- --------------------------------------------------------------------- "Are you still wasting your time with spam?... There is a solution!" Protected by GIANT Company's Spam Inspector The most powerful anti-spam software available. http://mail.spaminspector.com S...

How can I go to a specific page number in a large document
How do I go to a specific page number in a large word document. Read the replies to your earlier identical post -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "yisaajao" <yisaajao@discussions.microsoft.com> wrote in message news:CFC4289D-FC93-4C1B-951B-DB08CED833...

How do I turn the pivot chart into a list with all cells filled?
I have the pivot chart and would like to copy and paste it so that all fields are filled Select the pivot table. Choose Edit>Copy Select the cell where you'd like to paste the copy Choose Edit>Paste Special Select Values, click OK There are instructions here for filling the blanks: http://www.contextures.com/xlDataEntry02.html gianna wrote: > I have the pivot chart and would like to copy and paste it so that all fields > are filled -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

How do you copy a cell's content verses it's formula?
I have 2 cells and combined them into a third cell with the following formula... =a1&" "&b1. I was combining a person's first name (cell 1) with a person's second name (cell 2) so cell 3 included the first and second name. Now I want to copy and paste cell 3, but it copies the formula... I need to paste in the content (first and second name) not the formula. Hi Tammy, You could use a macro see JOIN macro on it's page http://www.mvps.org/dmcritchie/excel/join.htm not what you actually asked because if would change column A with the concatenated const...

Question about formula or marco
Hi all, Here is what I need to do: Column B will have a list of first names (John), Column C will have last names (Smith). What kind of formula or macro do I need so that I can have Column A read "jsmith" (all lower case)? Any ideas? Thanks very much. Try this formula in Cell A1:- =LOWER(CONCATENATE(LEFT(B1),C1)) "Gus Jae" <gusjae@verizon.net> wrote in message news:dn8Ue.186$sk2.153@trndny03... > Hi all, > Here is what I need to do: > Column B will have a list of first names (John), Column C will have last > names (Smith). > What kind of formu...

Moving a row from one sheet to another wrongly leaves blank-row artifact.
Moving an entire row within a sheet in Excel XP is implemented correctly: Select the entire row, cut it, go to the target location, and Insert Copied Cells. The target row is inserted (pushing all rows below it down), and the source row is deleted (bringing all rows below it up, as it were). NOT so when the target is in another sheet in the same workbook. Excel inserts the target row correctly, but fails to delete the source row. The row still exists (only it's blanked out). You have to remeber to, tediously, return to the source and delete the blank row. Which means that if you intende...

Select non-consecutive rows
Hi, Is there a way to select non-consecutive rows besides Ctrl + click. If I want to insert a blank row above about 40 rows, this can become quite tedious. -- Thanks! Dee Hi only possible with VBA in this case -- Regards Frank Kabel Frankfurt, Germany "dee" <dee@discussions.microsoft.com> schrieb im Newsbeitrag news:23B3F46E-C956-4FCC-9880-08B9A200B3A2@microsoft.com... > Hi, > > Is there a way to select non-consecutive rows besides Ctrl + click. If I > want to insert a blank row above about 40 rows, this can become quite tedious. > > > -- > Tha...

Conditional Cell Fill?
Hello, Is there a way to use fill colors based on formulas? Thanks, Rusty Look at conditional formatting in help -- Regards, Peo Sjoblom "Rusty Williamson" <rusty@uno.sd.znet.com> wrote in message news:RUGlb.45764$Z86.33887@twister.socal.rr.com... > Hello, > > Is there a way to use fill colors based on formulas? > > Thanks, > Rusty > > ...

Import plain text with formulas into Excel
I'm having some trouble in Excel. If I create a plain text file like the following: 1,2,3 4,5,6 =sum(a1:a2), =sum(b1:b2) Nothing can be done as if in spanish is (SI), iserror is (ESERROR), and so on. This really sucks as I can't just install other software on the server nor make other modifications. I don't know what Microsoft was thinking when they translated the versions; any Excel version should accept it's language commands and English commands, but well, that's too much to ask for... Any good sugestions? Regards -- schmiedel ---------------------------------...

C++ References
Hey, I've planed to write a Class with full access to special variables, given in the Constructor. I know this way: Class-Element: int* test char* test2 Constructor: CClass(int* ptest, char* ptest2){ test=ptest; test2=ptest2; } Access in Class: *test *test2 I've want to realice this with references, because I want to access the data with test and test2. But I don't know how! Could anyone help me? Thanks Peter Meier peter meier wrote: >Hey, > >I've planed to write a Class with full access to special variables, given >in the Constructor....

Formatting hyperlinks in an Excel cell 02-16-10
Two of the columns in a spreadsheet (Excel 2003) that I use record email and web addresses. All of them appear as hyperlinks i.e. blue and underlined but some occasionally seem to lose their hyperlink properties. This means that when one hovers over them, the cursor stays as the usual Excel cross rather than changing to the hand/finger symbol. Also, clicking on the former does not launch the browser. Is there any way to ensure they are formatted, and work, as hyperlinks please? TIA V ...

Formula auditing on protected sheets
Simple question: is there a easy (or difficult?) way to track precedent and dependent cells on sheets which are protected? Thanks, Andrew ...

Counting number of rows based on mutiple criteria
Does anyone know how to count the number of rows based on mutiple criteria on other columns? For example I want to count the number row that meet the criteria of "Yes" in Col 2 and "Yes" in Col 3. The result would be 2 in the example below. I tried different combinations of Vlookup, Countif and Sumif and could not come up with anything that worked. Col 1 Col2 Col3 A Yes No B Yes Yes C No No D No Yes E Yes Yes Your help is greatly appreciated! Hi, =SUMPRODUCT((B1:B10=&qu...