Geomean range

[Stephen]

Hi,

I am using this formula to calculate a geomean, but would like to omit line
13 and 17 and 19 in the o924 range.

{=EXP(AVERAGE(LN(1+O9:O24)))-1}

 

[N Harkawat]

Stephen
Will this work
=geomean(O9:O12,O14:O16,O18,O20:O24)

Or is there a criterion to exclude those(like exclude all negatives etc..)

 

[Stephen]

I have tried that, but I do have negatives and therefore need the original
formula

 

[Stephen]

I don't understand why the formula won't allow me to add several ranges after
each other?

 

[N Harkawat]

To exclude the negatives from your formula try this :

{=PRODUCT(1+IF(O9:O24>0,O9:O24))^(1/COUNTIF(O9:O24,">0"))-1}
array entered

 

[Harlan Grove]

Stephen wrote...

I am using this formula to calculate a geomean, but would like to omit line
13 and 17 and 19 in the o924 range.

{=EXP(AVERAGE(LN(1+O9:O24)))-1}

=EXP(AVERAGE(LN(1+O9:012),LN(1+O14:O16),LN(1+O18),LN(1+O20:O24)))-1
or

=EXP(SUM(LN(1+O9:O24)*{1;1;1;1;0;1;1;1;0;1;0;1;1;1;1;1})/13)-1