Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
wrong reference after dragging down a formula
I have a large dataset (over 20000 rows). column A is the date, column b is
the company name, column c is the monthly performance per company. So the cells A1:A12, represents January till December for company x the cells A13:A26 represents January till december for company y The cells B1:B12, company name x the cells B13:b26 company name y The cells C1:C12 is the monthly performance for company x The cells C13:C24 is the monthly performance for company y and so on I want to compare the standard deviation of every company of the first six months of the year and the last six months of the year. So I make a new worksheet with in column A the company name, Column B the standard deviation of the first 6 months and column C the standard deviation of the last 6 months. In this new worksheet cell B1 contains the formula =stdev('sheet1'!A2:A7) C1 contains the formula =stdev('sheet1'!A8:A13) B2 contain the formula =stdev('sheet1'!A14:A19) C2 contains the formula =stdev('sheet1'!A20:A25) If I drag down these formula from cell A2 to A3 and so on, the result is =stdev('sheet1'!A4:A9) instead of =stdev('sheet1!A26:A31). I hope that you understand what I mean and that you can help me solve this 'problem'. Thanks and regards, Bram |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
wrong reference after dragging down a formula
in A2 (for company name)
=Offset(Sheet1!$A$1,(ROW(C1)-1)*12+1,0,1,1) in B2 =stdev(offset(sheet1!$A$1,(ROW(A1)-1)*12+2,0,6,1)) In C2 =stdev(offset(sheet1!$A$1,(ROW(A1)-1)*12+8,0,6,1)) then select A2:C2 and drag fill down the column -- Regards, Tom Ogilvy "Bram" wrote in message ... I have a large dataset (over 20000 rows). column A is the date, column b is the company name, column c is the monthly performance per company. So the cells A1:A12, represents January till December for company x the cells A13:A26 represents January till december for company y The cells B1:B12, company name x the cells B13:b26 company name y The cells C1:C12 is the monthly performance for company x The cells C13:C24 is the monthly performance for company y and so on I want to compare the standard deviation of every company of the first six months of the year and the last six months of the year. So I make a new worksheet with in column A the company name, Column B the standard deviation of the first 6 months and column C the standard deviation of the last 6 months. In this new worksheet cell B1 contains the formula =stdev('sheet1'!A2:A7) C1 contains the formula =stdev('sheet1'!A8:A13) B2 contain the formula =stdev('sheet1'!A14:A19) C2 contains the formula =stdev('sheet1'!A20:A25) If I drag down these formula from cell A2 to A3 and so on, the result is =stdev('sheet1'!A4:A9) instead of =stdev('sheet1!A26:A31). I hope that you understand what I mean and that you can help me solve this 'problem'. Thanks and regards, Bram |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you have fixed cell reference when dragging/copying formulae? | Excel Discussion (Misc queries) | |||
reference is wrong | Excel Discussion (Misc queries) | |||
How to set up reference to allow dragging | New Users to Excel | |||
Wrong reference showing up as missing | Excel Programming | |||
finding a cell has a wrong reference. | Excel Programming |