0

I am trying to write a function in Excel for Mac 2011 (NOT as a macro; just plain old "=[Function Here]" format) that will take to following form:

=STDEV([X cells above and below this specific cell, in a different column])

NOTE: I am specifying the range "X" by referencing a "Window" cell, $N$3.

In other words: say I'm in cell G53, with $N$3 (range "X") = 50, and looking at a range of values in column F. The function would output STDEV(F3:F103). In cell H39, with $N$3 = 6 and looking at column G, the function would output STDEV(G33:G45).

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
Matt Billman
  • 472
  • 5
  • 19
  • Just to be clear, G3:G103 is 101 cells (inclusive), not 100 and H33:H45 is a total of 13 cells, not 12. Additionally, your biggest problem is omitting the cell that the UDF function is in or you will have a circular reference that will not calculate. –  Jan 05 '16 at 01:41
  • @Jeeped, what do you mean by "UDF?" – Matt Billman Jan 05 '16 at 01:46
  • How would I fix that problem? How do I include "the cell that the UDF function is in?" – Matt Billman Jan 05 '16 at 01:48
  • Your formula, of course, would be a circular reference since you are including the cell itself in trying to calculate the result. Is this what you want? How many iterations? – Ron Rosenfeld Jan 05 '16 at 01:49
  • @RonRosenfeld, I get what you mean. I didn't ask the question correctly; I have updated the question to reflect this and avoid circular reference. – Matt Billman Jan 05 '16 at 01:53
  • a) A UDF is a **User Defined Function**; a VBA custom function. b) if you include the cell in the range to be calculated then it will calculate the result and spit out an answer which in turn changes the values within the range so the function recalculates and spits out an answer which in turn changes the values within the range so the function recalculates and spits out an answer which in turn changes the values within the range so the function recalculates and spits out an answer ... etc, etc... –  Jan 05 '16 at 01:54
  • @MattBillman Where (what cell), would be the formula in column F that is referring to column G? – Ron Rosenfeld Jan 05 '16 at 01:55
  • @RonRosenfeld it would reference F53. Same row, different column. – Matt Billman Jan 05 '16 at 01:56

2 Answers2

1

You can do this using the OFFSET function. something like:

F39: =STDEVP(OFFSET(F39,-$N$3,1,$N$3*2+1))

where 39 is the midpoint, and your data is in column G

If N3 = 6, the above would calculate based on the Data in G33:G45

In the formula:

  • F39: The cell from which we calculate the offset
  • rows offset = -N3 = offset 6 rows down from F39
  • column offset = 1 offset 1 column (eg F-->G)
  • height: 2x N3+1 = 2x6 = 12+1 rows total height
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
0

For thode interested in a non-volitile function:

=STDEV.P(INDEX(G:G,ROW()-$N$3):INDEX(G:G,ROW()+$N$3))
chris neilsen
  • 52,446
  • 10
  • 84
  • 123