Excel 2007 : A Geometric Mean function that ignores negative numbers

Hi Folks,

I have a series of numbers, some of them including negatives. I want to get the geometric mean of them, but ignore all the negatives in the series. How would I go about making this?

I have attached some sample data.

Also, some further clarification:
n= amount of terms in a series

a geometric mean is the product of all the numbers in the series to the (1/n) power.

I want to make sure that the formula can adjust to varying amounts of terms, as it will be used on multiple series...

How do you want to "ignore" the negative numbers? I am unable to read the sample file, so I can't see exactly how you want to handle the negative numbers. Neither of my solutions is a single cell formula, but I expect someone who is good at nesting formulas like this could make a single cell formula out of them.

My first attempt was to use the ABS() function to make them all positive in an adjacent column. Then you can use the built in GEOMEAN() function. But that's not really "ignoring" the negative numbers.

The 2nd option would be to replace values <=0 with 1 [B1=IF(A1<=0,1,A1)], then use something like =PRODUCT(B1:B10)^(1/COUNTIF(A1:A10,">0")).

I also tried the ABS method, but as you stated, it would not ignore the negatives... i was thinking a conditional statement that would read through the series, recognize any negatives as 1 (this way when it is multiplied in the series, it would not affect the geometric mean of the series), then takes that product to the 1/n power...