Geomean range

Hi,

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

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

--
Stephen

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" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I am using this formula to calculate a geomean, but would like to omit
> line
> 13 and 17 and 19 in the o9:o24 range.
>
> {=EXP(AVERAGE(LN(1+O9:O24)))-1}
>
> --
> Stephen

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

"N Harkawat" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I am using this formula to calculate a geomean, but would like to omit
> > line
> > 13 and 17 and 19 in the o9:o24 range.
> >
> > {=EXP(AVERAGE(LN(1+O9:O24)))-1}
> >
> > --
> > Stephen
>
>
>

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

"N Harkawat" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I am using this formula to calculate a geomean, but would like to omit
> > line
> > 13 and 17 and 19 in the o9:o24 range.
> >
> > {=EXP(AVERAGE(LN(1+O9:O24)))-1}
> >
> > --
> > Stephen
>
>
>

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



"Stephen" <[email protected]> wrote in message
news:[email protected]...
>I don't understand why the formula won't allow me to add several ranges
>after
> each other?
>
> "N Harkawat" wrote:
>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi,
>> >
>> > I am using this formula to calculate a geomean, but would like to omit
>> > line
>> > 13 and 17 and 19 in the o9:o24 range.
>> >
>> > {=EXP(AVERAGE(LN(1+O9:O24)))-1}
>> >
>> > --
>> > Stephen
>>
>>
>>

Stephen wrote...
>I am using this formula to calculate a geomean, but would like to omit
line
>13 and 17 and 19 in the o9:o24 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