Goal: Finding the STDEVP of Filtered Data and automatically excluding cells with error messages

[SummerIntern]

Hello there,

I am working with a spreadsheet with about 90 columns and over 14,000 rows of data pertaining to product sales, share of market, and various changes over periods of time (in columns) for specific locations (rows).

My goal: I would like to be able to quantify the average product volume growth between time frames and categorize the locations based on their standard deviation from the mean.

My problem: For whatever reason, the data isn't continuous. Some locations might not have been reporting data before a certain time period, so when I look for the % Volume Change since Year Ago, some of the cells will get error messages because they cannot divide by zero. From what I can tell, if any error messages exist, the subtotal function will not be able to work properly.

Can you think about any ways to get around this problem? Could I be overlooking a simple solution?

Thank you in advance.

 

[T. Valko]

Hello there,

I am working with a spreadsheet with about 90 columns and over 14,000 rows of data pertaining to product sales, share of market, and various changes over periods of time (in columns) for specific locations (rows).

My goal: I would like to be able to quantify the average product volume growth between time frames and categorize the locations based on their standard deviation from the mean.

My problem: For whatever reason, the data isn't continuous. Some locations might not have been reporting data before a certain time period, so when I look for the % Volume Change since Year Ago, some of the cells will get error messages because they cannot divide by zero. From what I can tell, if any error messages exist, the subtotal function will not be able to work properly.

Can you think about any ways to get around this problem? Could I be overlooking a simple solution?

Thank you in advance.

Try something like this...

Book1
	A	B
4	header1	header2
5	X	56
6	X	87
7	Y	_
8	X	#DIV/0!
9	X	0
10	X	97
11	Y	89
12	Y	47
13	X	55
Sheet1

Array entered**:

=STDEVP(IF(ISNUMBER(B5:B13),IF(SUBTOTAL(2,OFFSET(B5,ROW(B5:B13)-ROW(B5),0,1)),B5:B13)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Domenic]

If you're using Excel 2010, have a look at the AGGREGATE function. Off hand, I don't know whether it's available for 2007. Also, I think Biff's formula can be shortened...

=STDEVP(IF(SUBTOTAL(2,OFFSET(B5:B13,ROW(B5:B13)-ROW(B5),0,1)),B5:B13))

...confirmed with CONTROL+SHIFT+ENTER.

 

[SummerIntern]

Dominic and T. Valko,

Thank you both very much! The formula you both gave me worked wonders! So far, I have had success applying it to different areas and with different functions in the spreadsheet!

Thanks so much!

 

[T. Valko]

If you're using Excel 2010, have a look at the AGGREGATE function. Off hand, I don't know whether it's available for 2007.

No, it's not. And I don't have Excel 2010 to be able play with it!

 

[T. Valko]

Dominic and T. Valko,

Thank you both very much! The formula you both gave me worked wonders! So far, I have had success applying it to different areas and with different functions in the spreadsheet!

Thanks so much!

You're welcome. Thanks for the feedback!

 

[T. Valko]

Also, I think Biff's formula can be shortened...

=STDEVP(IF(SUBTOTAL(2,OFFSET(B5:B13,ROW(B5:B13)-ROW(B5),0,1)),B5:B13))

...confirmed with CONTROL+SHIFT+ENTER.

I originally used SUBTOTAL(9 and got the #DIV/0! error so I added the ISNUMBER but then decided to go with SUBTOTAL(2 but left the ISNUMBER in there!

 

[Domenic]

No, it's not. And I don't have Excel 2010 to be able play with it!

Thanks for letting me know, Biiff.

I originally used SUBTOTAL(9 and got the #DIV/0! error so I added the ISNUMBER but then decided to go with SUBTOTAL(2 but left the ISNUMBER in there!

Been there, done that.