Home |
Search |
Today's Posts |
#1
|
|||
|
|||
reference is wrong
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
|
|||
|
|||
You got a couple of replies to your post in .programming.
Bram wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
how do I format a cell reference to move as source changes | Excel Worksheet Functions | |||
a cell reference in a 3d reference | Excel Worksheet Functions | |||
Flexible Cell Reference | Excel Discussion (Misc queries) | |||
Absolute Worksheet reference number | Excel Discussion (Misc queries) |