Calculating Geometric Standard Deviation with Range Including Blanks

[briggss1]

Running GSD on a series of cells that holds up to 20 values maximum, of which I want to calculate the GSD and exclude the cells in the range that are empty. Straight forward GSD formula fails if no blank value is detected.

In cells A3: T3 is a table of sample values. Not all are filled. Example, A3:C3 are: .1, 3, and 4 respectively. Running a GSD formula =EXP(STDEV(LN(A3:C3))) yields a correct GSD value of 3.82. However for the life of me I cannot remember how to run the GSD on the entire range whereby it ignores the blanks and only produces a GSD based on nonblank values.

How do I exclude the blank cells in my formula?

 

[AhoyNC]

When I use your example I get 2.078425, not 3.82
See if the formula in B6 helps. Depending on your version of Excel you may need to enter the formula as an array with CTRL-SHIFT-ENTER.

Book1
	A	B	C
1
2
3	1	3	4
4
5		2.078425
6		2.078425
Sheet1

Cell Formulas
Range	Formula
B5	B5	=EXP(STDEV(LN(A3:C3)))
B6	B6	=EXP(STDEV((IF(A3:T3<>"",LN(A3:T3)))))

 

[briggss1]

That worked, thanks! Now I can do away with a whole set of hidden columns that I was using to place the LN value or text (the GSD disregards text).

 

[AhoyNC]

You're welcome. Thanks for the feedback.