how can I drag formulas with other cell references

example 
cell A1 contains formula "=stdev(a2:a7)
cell a2 contains formula "=stdev(a8:a13)
Is it possible to drag the formula down so that cell a3 contains 
"=stdev(a14:19) and cell a4 contains "=stdev(a20:a25) and so on for cells a5, 
a6, a7 etc. etc.
0
Bram1 (8)
10/10/2005 10:16:02 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
787 Views

Similar Articles

[PageSpeed] 52

Hi Bram,
See   http://www.mvps.org/dmcritchie/excel/snakecol.htm#snkAddr

B1:   =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0))
B2:   =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0))

It was easier to test and verify using SUM instead of  stdev, but the formula is same
NOTE  the formulas are in a different column than the data.
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Bram" <Bram@discussions.microsoft.com> wrote in message news:0AD65710-7678-4856-9FF5-2C75F951E378@microsoft.com...
> example
> cell A1 contains formula "=stdev(a2:a7)
> cell a2 contains formula "=stdev(a8:a13)
> Is it possible to drag the formula down so that cell a3 contains
> "=stdev(a14:19) and cell a4 contains "=stdev(a20:a25) and so on for cells a5,
> a6, a7 etc. etc.


0
10/10/2005 11:29:31 AM
Reply:

Similar Artilces:

Viewing error: can only see in Full Screen
Hi all, TIA for the help. I've had 2 users in the last month or so say that when they open their sheet, it only displays a grey screen. Once i flip it to View > Full Screen, the sheet comes up. Any solutions? Thanks, Kevin M ...

can i delete the last 3 characters in each cell in a column in 1 .
I have a column of names followed by a number. Obviously the names are of different lengths but the numbers are consistent, so i need to remove say 8 chars from the right of each filed in the column. I'm sure I have done this before but can't remember how! D'oh! In an empty column, enter this formula (assuming your names are in column A and all the numbers are 8 characters long) =LEFT(A1,LEN(A1) - 8) - works if there are no spaces between the name & numbers =LEFT(A1,LEN(A1) - 9) - works if there is one space between the name & numbers "homer" wrote:...

Combining Cells #2
I need the easiest way to do this: I have a column with values. Sometimes there are 2 rows, sometimes 20. For another program, I need all the values in that column in one long string separated by commas. So if my orginal data is: 8766 7788 9987 What I need excel to spit out is: 8766,7788,9987 How do I do this given that my number of rows can vary. I don't mind at all copying and pasting a function in that handles this, but just not sure how to go about it. Any help is appreciated. Thanks! Let's say the numbers start in A1 In B1 enter = A1 In B2 enter =B1&","&...

Printing cells that are blank, because there is a formula
I'm using Excel 03 and I have a column of cells that have a formula =if(e10="","",e10+90) Is there a way Excel will recognize these cells to print when there's an actual value but not when the value is "" Thanks Left to its own devices, excel will include those cells in the print range--they contain something (that formula), so those cells will be included. But you can do something to tell excel what you want... Saved from a previous post (so you'll have to adjust the sheet names and column letters and even the columns to print (A:X...

If formula?
I am not sure if I need an IF formula but I am preparing a spreadsheet for work and basically it's a sponsorship form and if the person gets sponsorships of different levels they get a different prize i.e. Up to �10 - Wallet �10.01-�20 Bootbag �20.01-�30 T-shirt etc up to �60 and I would like to enter the amount in column B and then then let the computer to enter either the prize in column C. Can anyone tell me how to do this. I am totally new to excel. Thank you Wendy Hi One way is to use VLOOKUP. First create a table of information: 10 Wallet 20 Bootbag 30 T-Shirt 40 ....

calculating results in formulas
I am trying to do some calculate differences between results in columns containing a mix of negative or postive numbers. I can't seem to write the formula to display the results that are expected. So far....I get the correct answer but all are postive results, and I can add the minus sign, but then get only negative results. When summing the answer columns results...then the difference is inflated. For example: (0.33)-(0.37)=0.04 and 12.28-9.72=(2.56) I would like the formula to take a negative number subtract another negative number and display the result as a positive AND the ...

track changes window is too big...can't select "okay" or "apply"
I can't scroll down and window is already moved to the top of the screen? Does anyone know what I can do? I can't think what you mean by "track changes window"? Do you maybe have your changes in a panel instead of underlines & strikeouts within the text? Version of Word? On Jan 20, 12:07=A0pm, gg <g...@discussions.microsoft.com> wrote: > I can't scroll down and window is already moved to the top of the screen?= =A0 > Does anyone know what I can do? ...

Excel; How can I use Query to create lists from separate workbooks
Hello, I have 5 worksheets in 1 workbook with purchasing data in them. The sheets have some different fields, but all contain a Filing No, Date, Vendor Name, Description, Department No, Account No, and Amount. I would like to pull the above information and place it into a master list based on Department No. and Account No. criteria (Ex; If (DepartmentNo=431110 and AccountNo=560420) = True, then insert into list) I thought that creating a database query was a promising solution. Unfortunately I cannot get it to work. I can get query to properly pull entries from a single sheet. W...

sorting merged cells
my worksheet cannot be sorted because it is saying that it cannot sort merged cells, how do i find the merged cells bucause i see none Select the whole area then Format, Cells, Alignment and make sure th Merge celss box is clea -- Alex Delamai ----------------------------------------------------------------------- Alex Delamain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1127 View this thread: http://www.excelforum.com/showthread.php?threadid=26644 try this or just select the cells button (top left of row/columns) and right click>format>alingment&g...

How not to draw cells with formula but no value in Excel charts?
Can anyone please help? I have a chart referring to a table of cells with formulae pointing to other worksheets. Those worksheets are captured and created periodically (every 30 min). When the sheets are not created, the formulae will check their absence and display "" (empty value). Once the sheets are created, the wanted value would be extracted and displayed. However, with empty value, the chart will treat it as zero and draw it. How may I skip those cells and not draw them? I now have to delete the formula and copy back the formula when the referenced sheets are...

How do I set up formula to copy format from one worksheet to anoth
How do I set up a formula to copy both value and format from one worksheet to another? When I use the = sign, it copy the value only. How do I get the format be copied also? you can't "link" the formatting in the same way as you link the contents what you need to do is to copy all the cells in your sheet X A B C ... 1 2 click on the grey square which I have illustrated with the X this selects all the cells in the sheet press control-C (copy) go to your new sheet and select the same X square now do edit > paste special > format / column width. if you are doin...

Trying to delete multiple cells that contain a specific word (not equation)
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I've had to export a form from QuickBooks Online to Excel so I could manipulate it. Within a column there are 115+ cells that contain a specific word that I need to delete all those cells. Is there something as easy as find and delete, rather than find and replace? I can't believe that I have to delete each cell individually. On 3/9/10 10:52 AM, JeanM@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I've had to > export a form from QuickBooks Online to Excel so I could manipulate ...

How can I create global hyperlinks?
Hello all, I have been working on a network diagram for my company. I want to be able to drill down from our overall server floor image to individual racks, by inserting hyperlinks into my drawing. However, the hyperlinks seemingly only work on my local computer, meaning that others would have to insert the hyperlinks themselves. Is there any way I can place a hyperlink on an object that can be seen universally? The drill down function would seem handicapped if there was no way to do such. Note: I think I've developed a workaround. I'm going to insert pages, copy and paste dr...

Rank function
You have a list of values in column A and you want their ranks (1 against the highest value and so on) in column B. The Rank function does this. However with tied values, the function gives such values the same rank - the lowest of the ranks they would have got. It would be much more useful to give tied values the mean of the ranks they would have got. (For example you'd have a check on the sum of the ranks.) Is it possible to do this? I'm reasonably sure this has been answered before. Check the google.com archives of the XL NGs. -- Regards, Tushar Mehta, MS MVP -- Excel ww...

Can we change cell color programatically?
Hi all I'm using Excel 2003. Does Excel allow me to change the fill-color of a cell based upon the contents of that cell? For example: If the formula of a cell calculates its value to be less then, say, 100, I'd like to fill-color that cell to Green. Otherwise I'd like to color that cell with no color. Is this possible? Thanks -Mike Take a look at Format|Conditional formatting. You can specify up to 3 rules (and formats) as well as the "normal" format. Mike wrote: > > Hi all > > I'm using Excel 2003. > Does Excel allow me to change the fill...

Code to an in cell drop down list
I would like to assign code to an in cell drop down list..... so that once an item is chosen it would run the code. Is this possible? I know you can attach code to a control list box, but I am having trouble creating a dependant list like I can with the in cell drop down. Thanks for any help. You can use a Worksheet_Change event macro like: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(0, 0) <> A1 Then Exit Sub If Target = "" Then Exit Sub Call YourMacro Application.EnableEvents = False Target.ClearContents Application.Enab...

create list to enter data in cell
What I want to do is: when I select a cell I want a list of choices to come up to choose from. I then pick the item and it is entered into the cell. I saw in one of excel websites and I cannot figure out how to do it. Would appreciate any help. Harvey Try this: Select the cell where you want this to happen Goto the menu Data>Validation Allow: List Source: the source can be a range of cells like =A1:A5 or you can type them in separated by a comma like Joe, Sue,Bob,Tom,Lisa Make sure In-cell drop down is checked OK out -- Biff Microsoft Excel MVP "Harvey" <hsm162@cs....

Can I install vs2010 and vs2008 at the same pc?
I used VS2008 with frameword3.5. I want to install vs2010 beta2. Can I install vs2010 and vs2008 at the same pc? -- Message posted via http://www.dotnetmonster.com Hello, According to http://weblogs.asp.net/scottgu/archive/2009/10/19/vs-2010-and-net-4-0-beta-2.aspx it is supported... -- Patrice "aspfun via DotNetMonster.com" <u53138@uwe> a �crit dans le message de groupe de discussion : a0ae4fb15d388@uwe... > I used VS2008 with frameword3.5. > > I want to install vs2010 beta2. > > Can I install vs2010 and vs2008 at the same p...

Can be done?
Hello all, ok i am a newbie yadda yadda and I have 4 tables that I want to be loaded into one datagrid depending on the users selection in a combobox. I have set AutoGenerateColumns="True" so that the datagrid can format the return data however it wants to. I have created a stored procedure that I want to take the value of the the combobox so that it knows the right data to return to the datagrid. Can all of this be done? I have am stopped at the point of when I click on the combobox i want it to call the SP and fill datagrid with dynamcic data. Every example i se...

Can't import JPG in Word or Powerpoint, windows 7
Just downloaded windows 7 and now microsoft word and powerpoint won't import any of my jpgs. Any ideas??? Cortneyc If it was me, I'd re-install ms word and ms powerpoint (ms office?) cheers TAJ Simmons Powerpoint Wizard awesome graphics for powerpoint http://www.PresentationPictures.com powerpoint pictures, tutorials, free samples... "cortneyc" <cortneyc@discussions.microsoft.com> wrote in message news:743A157B-29FA-4AD9-BB0B-6A2CEE78B7B8@microsoft.com... > Just downloaded windows 7 and now microsoft word and powerpoint won't > impor...

cell in cell
Okay, I got the money cell in cell formula to work, but now I want to do one with a date, and can't seem to get the date to show up correctly. If I just enter the formula in the text string as &(A1) & where A1 is the date January 7, 2005, I get the numeric value of the date 38359. How do I switch the format in the calculation? Is there a way to bold is within the rest of the sentence? (This is Excel 97...I'll be getting an upgrade to Xp and 2003 in a week...) This will convert the 38359 to a date format in your formula: =TEXT(A1,"mm/dd/yy")&" your c...

Can not compose or respond a message in OWA 2003
We are using Exch. 2k3 SP1 in a W2k3 Server. Our clients are XP SP2 and some of them can not compose a new message or respond a message because their IE got with a X in the field one is sopouse to write. I compared one "good" PC with one "bad" and all the IE settings are the same. We are using IE 6 SP1. PLease help me because I have used more than a day trying to get an answer but cnow am frozen. MR Marcos, I have the same problem but no solution yet. I saw your post after I posted my question. Here's the link to my message if you wanna keep an eye on it if someo...

Can I delete an email file from M:?
Is it possible to delete a file/folder from the M: drive directly? Will exchange be able to track the changes? Thanks for the help ahead of time. That is not a good idea�I Strong recomend you do not directly access Exchagne IFS(M:) -- Jammy "ctrlgrid" <jkit001@yahoo.com> ���g��l��s�D :4f9d64d8.0410252347.662bbc48@posting.google.com... > Is it possible to delete a file/folder from the M: drive directly? > Will exchange be able to track the changes? > > Thanks for the help ahead of time. It is possible to do so. Not sure what you mean by will Exchange track ...

Outlook 2007 Can't receive email messages
I am not able to receive emails through my pop3 account, I can only send. I keep getting the enter your password pop up window. Please help, thank you. Julio wrote: > I am not able to receive emails through my pop3 account, I can only send. I > keep getting the enter your password pop up window. Please help, thank you. Have you tried using the same login credentials recorded or used in Outlook when using the webmail interface to your e-mail account? Using the webmail client will let you know if the login credentials are correct and haven't been changed. If the logi...

Can we use group policy templates for Outlook client options
Hi there, We are looking to automate the CRM outlook client settings so we don't have to manually change them for each user. Is there any way we can do this through group policy or another way? It would be handy if we had a list of registry settings that we could use? Thanks ...