1

I would like to pass a cell reference into the STDEV.P function in Excel, but when I do this I keep getting a #DIV/0 error.

I have two columns in Excel. Column A contains a list of dates starting Jan-1-2012 and going to the current date. Column B contains a list of integers. I have over 800 rows of data and it's possible that integers in column B are repeated somewhere in the 800 rows of data.

I want to find the STDEV of an array of values in Column B. The array is determined by a begin date and an end date. The end user can decide which begin and end dates are to be used. For example, if the begin date is 1/1/2015, I want to find the corresponding integer in column B for this date and pass the CELL REFERENCE into the STDEV formula. I want to do the same for the end date. The end result is a STDEV calculation that uses the array of integers determined by user supplied begin & end dates.

I've been able to find the cell location (e.g. value .332 is in cell D45) using the MATCH, INDEX and ADDRESS functions, but when I try to pass D45 into the STDEV function, I get the error. Help!

wythes
  • 19
  • 4

2 Answers2

1

Many users believe that an INDEX(MATCH(...)) pair only returns a cell value in a lookup but in fact it can be used to return a cell reference without the INDIRECT function's overhead. Two of them can even be joined with a colon to form a valid cell range to be used in any number of formulas.

        STDEV with INDEX/MATCH range

The formulas in F2:H2 are,

=STDEV(INDEX(B:B, MATCH(D2,A:A, 0)):INDEX(B:B, MATCH(E2,A:A, 0)))    ◄F2
=STDEV.P(INDEX(B:B, MATCH(D2,A:A, 0)):INDEX(B:B, MATCH(E2,A:A, 0)))  ◄G2
=SUM(INDEX(B:B, MATCH(D2,A:A, 0)):INDEX(B:B, MATCH(E2,A:A, 0)))      ◄H2

I've included a simple SUM function so that you can quickly verify that the method used is returning the correct cell range without doing the math on a StDev.

Of the many lookup functions, this is an INDEX function trait. The VLOOKUP function or HLOOKUP function cannot be used in this manner as they are only returning the values.

Community
  • 1
  • 1
0

Assuming your start date is in D1 and your end date in D2 please try: 

=STDEV.P(INDIRECT("B"&MATCH(D1,A:A,0)&":B"&MATCH(D2,A:A,0)))

As long as the dates are unique it should not matter that the other values are not.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • This worked! Although when I try to break it out into its individual components, it doesn't work. For example, I can run the MATCH functions no problem. But when I apply the INDIRECT function to the MATCH function's results, I get a 0 (for both begin & end dates). When I plug these values into the STDEV.P function, I get a different value than if I simply run STDEV.P and manually select the array of values to use. – wythes Apr 16 '15 at 04:27