Standard Deviation over set points

In one place you use the term standard deviation and in another the average deviation. Which do you want? The average deviation is the average of the difference between the result and the target. The standard deviation is a measure of the distribution of values around the mean value and is calculated from ONE dataset.

Also, do you want the same value - the average/standard deviation of rows 2-61 in the first 60 rows and then the average/standard deviation of rows 62-121 in the next 60 rows and so on?

I have just noticed that the dates in column C jump around all over the place. Do the data not need to be in date order for this to be meaningful? What is on the intended x-axis: date or simply row number? If it is date, it would be a lot easier if the data were in date order: then it would be each 60 rows.

Also, your sample is too small, can you include about 200 rows so that there are a few "cycles" of 60 rows to play with.

OK. So, IF I have picked you up correclty, this array formula will do what you want:

=AVERAGE(OFFSET($I$2:$I$61,60*INT(ROWS(($2:2)-1)/60),,,)-100*OFFSET($H$2:$H$61,60*INT(ROWS(($2:2)-1)/60),,,))

It might take a few seconds to process the data.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed IN THE FIRST CELL by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. This cell can then be dragged down normally.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Formula:

=AVERAGE(OFFSET($I$2:$I$61,60*INT(ROWS(($2:2)-1)/60),,,)-100*OFFSET($H$2:$H$61,60*INT(ROWS(($2:2)-1)/60),,,))
Basically, of course; it's looking at the difference between column I and column H. The OFFSET function works like this:

=OFFSET(cell reference or range,how many rows to move, how many columns to move, width,height)

So... OFFSET($I$2:$I$61 look at this range of cells and return the values: 60*INT(ROWS(($2:2)-1)/60) rows away.

If you paste =60*INT(ROWS(($2:2)-1)/60) into a cell and copy down it'll return 0 for the fist 60 rows, then 60 for the next 60, then 120 for the next 60, etc. So for for the first 60 rows it returns the values in I2 to I61. After that it returns I62-I121 for the next 60 rows and so on...

Clear as mud??



You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.