Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 57
Default I need to calculate a Geometric mean

I need to calculate a geometric mean for an array of numbers of which some
are negative. The geomean function in Excel does not calculate if there are
negative numbers in the array. I'm not sure what the purprose of the
function is if you can't include negative return numbers, but that is the
fact. Does anyone know how a work around or another way to calculate the
geometric mean with an array of negative numbers included?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: I need to calculate a Geometric mean

Yes, there is a workaround to calculate the geometric mean of an array of numbers that includes negative numbers in Excel. Here are the steps:
  1. Use the ABS function to convert all negative numbers in the array to positive numbers. For example, if your array is in cells A1:A10, you can use the formula
    Code:
    =ABS(A1:A10)
    in another column to convert the negative numbers to positive numbers.
  2. Use the GEOMEAN function to calculate the geometric mean of the absolute values of the numbers. For example, if your converted array is in cells B1:B10, you can use the formula
    Code:
    =GEOMEAN(B1:B10)
    to calculate the geometric mean.
  3. Use the SIGN function to determine the sign of the original array. For example, if your original array is in cells A1:A10, you can use the formula
    Code:
    =SIGN(A1:A10)
    in another column to determine the sign of each number.
  4. Use the PRODUCT function to multiply the geometric mean by the sign of the original array. For example, if your sign array is in cells C1:C10, you can use the formula
    Code:
    =PRODUCT(C1:C10)*GEOMEAN(B1:B10)
    to calculate the final geometric mean.

This workaround allows you to calculate the geometric mean of an array of numbers that includes negative numbers in Excel.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 837
Default I need to calculate a Geometric mean

How do you want negative numbers handled? The geometric mean is not usually
defined with negative numers, because the nth root of a negative number
(product including an odd number of negatives) is a complex number (involving
the imaginary number i=SQRT(-1)).

Jerry

"Biff" wrote:

I need to calculate a geometric mean for an array of numbers of which some
are negative. The geomean function in Excel does not calculate if there are
negative numbers in the array. I'm not sure what the purprose of the
function is if you can't include negative return numbers, but that is the
fact. Does anyone know how a work around or another way to calculate the
geometric mean with an array of negative numbers included?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 57
Default I need to calculate a Geometric mean

Hi Jerry,

I am trying to calculate the annualized rate of return of a portfolio over
three years. If the portofolio is up 25% year 1, down 25% year 2, and then
up 25% in year three, what is the geometric mean or annualized rate of return
over that three year period. The arithmetic mean of 25% would be misleading,
but I can't figure out the geometric mean which I know is smaller. Any help
would be appreciated.

Biff

"Jerry W. Lewis" wrote:

How do you want negative numbers handled? The geometric mean is not usually
defined with negative numers, because the nth root of a negative number
(product including an odd number of negatives) is a complex number (involving
the imaginary number i=SQRT(-1)).

Jerry

"Biff" wrote:

I need to calculate a geometric mean for an array of numbers of which some
are negative. The geomean function in Excel does not calculate if there are
negative numbers in the array. I'm not sure what the purprose of the
function is if you can't include negative return numbers, but that is the
fact. Does anyone know how a work around or another way to calculate the
geometric mean with an array of negative numbers included?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 57
Default I need to calculate a Geometric mean

Hi Jerry,

I noticed my last reply didn't go through. I am trying to calculate the
annualized rate of return for a portfolio over 3 years. If after year 1 the
return is +25%, year 2 -25% and year three +25%, the arthemetic mean gives me
8.33% which is not reality, while the geometric mean is 5.4%. The
multiplication of the the returns plus one should be positive before taking
the nth root. Does this make sense?

Biff

"Jerry W. Lewis" wrote:

How do you want negative numbers handled? The geometric mean is not usually
defined with negative numers, because the nth root of a negative number
(product including an odd number of negatives) is a complex number (involving
the imaginary number i=SQRT(-1)).

Jerry

"Biff" wrote:

I need to calculate a geometric mean for an array of numbers of which some
are negative. The geomean function in Excel does not calculate if there are
negative numbers in the array. I'm not sure what the purprose of the
function is if you can't include negative return numbers, but that is the
fact. Does anyone know how a work around or another way to calculate the
geometric mean with an array of negative numbers included?



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 84
Default I need to calculate a Geometric mean

=SUMPRODUCT(GEOMEAN(A1:A3+1))

This will add 1 to each value before taking the geometric mean.

- David


Biff wrote:
Hi Jerry,

I noticed my last reply didn't go through. I am trying to calculate the
annualized rate of return for a portfolio over 3 years. If after year 1 the
return is +25%, year 2 -25% and year three +25%, the arthemetic mean gives me
8.33% which is not reality, while the geometric mean is 5.4%. The
multiplication of the the returns plus one should be positive before taking
the nth root. Does this make sense?

Biff

"Jerry W. Lewis" wrote:

How do you want negative numbers handled? The geometric mean is not usually
defined with negative numers, because the nth root of a negative number
(product including an odd number of negatives) is a complex number (involving
the imaginary number i=SQRT(-1)).

Jerry

"Biff" wrote:

I need to calculate a geometric mean for an array of numbers of which some
are negative. The geomean function in Excel does not calculate if there are
negative numbers in the array. I'm not sure what the purprose of the
function is if you can't include negative return numbers, but that is the
fact. Does anyone know how a work around or another way to calculate the
geometric mean with an array of negative numbers included?

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 733
Default I need to calculate a Geometric mean

Biff wrote...
. . . I am trying to calculate the
annualized rate of return for a portfolio over 3 years. If after
year 1 the return is +25%, year 2 -25% and year three +25%, the
arthemetic mean gives me 8.33% which is not reality, while the
geometric mean is 5.4%. The multiplication of the the returns
plus one should be positive before taking the nth root. Does
this make sense?


You have to add 1 to the percentage returns (converting them into
ratios of ending values to beginning values), then take the geometric
mean of these POSITIVE values, then subtract 1 from that result. You'd
either need to use either of the following array formulas.

=GEOMEAN(Range_of_Percentages+1)-1

or

=EXP(AVERAGE(LN(Range_of_Percentages+1)))-1

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default I need to calculate a Geometric mean

On Wed, 25 Jul 2007 17:28:00 -0700, Biff
wrote:

Hi Jerry,

I noticed my last reply didn't go through. I am trying to calculate the
annualized rate of return for a portfolio over 3 years. If after year 1 the
return is +25%, year 2 -25% and year three +25%, the arthemetic mean gives me
8.33% which is not reality, while the geometric mean is 5.4%. The
multiplication of the the returns plus one should be positive before taking
the nth root. Does this make sense?

Biff


To handle this problem, Biff, you merely add 1 to your percentage changes, and
then subtract 1 from the result.

So your series is

1.25
0.75
1.25

You can also do this within a single **array** formula

e.g.

K1: 25%
K2: -25%
K3: 25%

=GEOMEAN(1+K1:K3)-1

(array-entered)


--ron
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 57
Default I need to calculate a Geometric mean

It worked. Thanks.

"David Hilberg" wrote:

=SUMPRODUCT(GEOMEAN(A1:A3+1))

This will add 1 to each value before taking the geometric mean.

- David


Biff wrote:
Hi Jerry,

I noticed my last reply didn't go through. I am trying to calculate the
annualized rate of return for a portfolio over 3 years. If after year 1 the
return is +25%, year 2 -25% and year three +25%, the arthemetic mean gives me
8.33% which is not reality, while the geometric mean is 5.4%. The
multiplication of the the returns plus one should be positive before taking
the nth root. Does this make sense?

Biff

"Jerry W. Lewis" wrote:

How do you want negative numbers handled? The geometric mean is not usually
defined with negative numers, because the nth root of a negative number
(product including an odd number of negatives) is a complex number (involving
the imaginary number i=SQRT(-1)).

Jerry

"Biff" wrote:

I need to calculate a geometric mean for an array of numbers of which some
are negative. The geomean function in Excel does not calculate if there are
negative numbers in the array. I'm not sure what the purprose of the
function is if you can't include negative return numbers, but that is the
fact. Does anyone know how a work around or another way to calculate the
geometric mean with an array of negative numbers included?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating Geometric Mean in a Pivot Table Carrie Excel Worksheet Functions 0 August 14th 06 06:47 PM
How to determine geometric mean of 152 values? Jerry G Excel Worksheet Functions 2 June 20th 06 08:52 PM
Geometric Characteristics Symbols Hank Hain Excel Discussion (Misc queries) 1 April 6th 06 11:26 PM
Wing Ding for geometric POSITION Katydid Excel Discussion (Misc queries) 9 July 26th 05 06:02 PM
Average (geometric) Stephen Excel Worksheet Functions 1 January 17th 05 06:19 PM


All times are GMT +1. The time now is 08:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"