Please help to find a single formula to calculate negative and positive stream of returns. The problem is that =geomean formula does not work with negative numbers.

Here's an example on how to calculate geometric mean with 5 numbers:

Stream of numbers: 0.5, -1.4, -6.5, 0.3, -2.7

First step: I have to add 1 to all numbers (they are positive now)

Second step: =Product (multiply all numbers)

Third step (result): find 5th root(there are 5 numbers) of their product (or raise it to 1/5 power)

I can substitute steps 2 & 3 with a formula =geomean() and the answer will be correct except it won't be negative...but i think there should be another solution, a single formula.

Any idea? Thank you so much!

Here's an example on how to calculate geometric mean with 5 numbers:

Stream of numbers: 0.5, -1.4, -6.5, 0.3, -2.7

First step: I have to add 1 to all numbers (they are positive now)

Second step: =Product (multiply all numbers)

Third step (result): find 5th root(there are 5 numbers) of their product (or raise it to 1/5 power)

I can substitute steps 2 & 3 with a formula =geomean() and the answer will be correct except it won't be negative...but i think there should be another solution, a single formula.

Any idea? Thank you so much!

- Custom VBA Function for Geometric mean
- Question about Geometric Means for annualized returns
- Geometric Mean
- What is the formula for geometric mean with some negative data
- Calculating Geometric Mean in a Pivot Table
- How to return min, mean, max using criteria?
- Compound Average Annual Growth Rate
- Array Formula to find Average Return
- Array Formula to find Average Return
- VBA function for annualized return (geometric mean)
- How to input a calculation in excel to find the geometric mean
- If and statement with average return
- A Geometric Mean function that ignores negative numbers
- Geomean vs Product & Chain Linking Investment Returns
- Average Annual Growth
- Compounding Formula
- Geometric Mean Alternative
- How to turn daily returns into weekly (average) returns?
- AVERAGE Row of Numbers and Return Corresponding Numeric Label
- AVERAGE Row of Numbers and Return Corresponding Numeric Label
- Need help creating custom VBA function in excel 2007
- Array Formula to Pick Average
- Return list of values index/match
- Dynamic Averaging Code

Write a custom function to calculate the geometric average of the returns listed in cells B6:B15. Below is the formula for a geometric average:

[(1+R1 ) * (1+R2) *...*(1+Rt)]^(1/t)-1

There are three requirements for your function:

i. Your function should have a single input – the range of cells that contain the return series.

ii. Use a For/Next loop to cycle through the returns in the range provided by the user.

iii. Use an If/Then statement to determine if the returns are entered in decimal or percentage point form. You may assume that values above 1 are in percentage point form.

ANY HELP PLEASE!!!

============================================================================================================================ Function MyGEO(Returns As Range) Dim Varcount As Integer Dim t As Integer Dim mean As Double Dim geomean As Double If Returns >= 1 Then Returns = Returns / 100 Varcount = Returns.Count mean = 1 For t = 1 To Varcount mean = mean * (1 + Returns(t)) Next t geomean = mean ^ (1 / Varcount) - 1 MyGEO = geomean End Function

Basically what I am trying to do is get get a geometric mean for a series that includes negative numbers.

I know that the procedure to deal with this is to add a number (x) to the series to make them all positive, take the geometric mean, then subtract (x) from the series.

I know that it is done in an array format too.

However, I want to know how to find the ideal number that won't overstate the value.

Take for instance finding the return of this simple series:

(.25, -.25, and .25)

Conventional knowledge says that it would be roughly 5.43%

This is due to people adding 1 to the whole series, making it

(1.25, .75, and 1.25)

Taking the geometric mean, or

(1.25 * .75 * 1.25) ^(1/3)

Then subtracting 1 from the Geometric mean series

or in excel...

={GEOMEAN(1.25,.75,1.25) - 1}

Next, I tried adding 100 to the series. I the figured that the number should not have an effect, as it should give and take away by the same amount. I was wrong.

So the series becomes

(100.25, 99.75, 100.25)

and thus

={GEOMEAN(100.25,99.75,100.25) - 100}

But, the percentage ends up as 8.31%

As you can see, by adding a greater number, it adds more to the percentage.

Now I figured, why not use the smallest possible increment? I thought that if the number I added to the series was small enough, it should also be small enough to not affect my result.

So, I added .2500000000001 to the series.

This way, the series would be

.5000000000001, .0000000000001, .5000000000001

So, my formula ended up being

={GEOMEAN(.5000000000001, .0000000000001, .5000000000001) - .2500000000001 }

The percentage ended up being -25%.

My question to you then, is which one is the true answer? Why is it the true one? If none of these are correct how would I go about finding the true answer?

Maybe my finance math is just horrendous, but the number(x) I add to the series, in theory, should not affect the results to such a grand degree.

See the attached data for further clarificiation.

geometric mean of returns for different portfolios with some negative data.

I have a very large database that I am trying to perform some

calculation on using Excel. I need to calculate geometric means on

subsets of the data (specifically, I need to calculate geomean for

several fields for each "site" within the dataset over a specific

period of time). I think that using pivot tables is the way to go, but

I can't quite figure it out. This is what my database spreadsheet looks

like:

site date a b c d etc.

1 6/1/06

6/2/06

6/3/06

etc.

2

3

etc.

where a, b, c, d, etc. are all my fields (there are about 100 of them).

Each site has been sampled on multiple dates. For most of the fields I

want to calculate the mean (average) for each site, which I can do

quite easily in a pivot table. Four of the fields need to be calculated

as geometric means, however. When I set up my pivot table it looks like

this:

site data total

1 mean a #

mean b #

mean c #

etc.

2

3

etc.

Is there a way to calculate the geomean? I tried using a calculated

field but I think you can only sum the values, which does not seem to

help (at least, I seem to come up with sums when I try it). In my

searching of the forums I get the sense that maybe a helper column is

the way to go, but I can't wrap my head around how to do it. Can anyone

help?

Also, within the database there are quite a few cells that contain "no

data" - this happens when sampling occurs on a given date but not at a

given site. Are these blank cells being included in the mean

calculations, or not? I definitely don't want them to be.

Thanks very much!

Carrie

--

Carrie

value (criterion) in another column. For example, how can I return the

mean price (col 2)for all SWE4s (col 1)? Thanks for your guidance!

Item Price

NETAD 3 $8,985.60

SWE 4 $10,368.00

NET 2 $7,142.40

AD A4 $4,867.20

LYST 1 $5,529.60

LYST 2 $5,184.00

SWE 4 $11,059.20

NET 3 $9,216.00

NET 4 $1,209.60

NET 4 $1,152.00

IA 4 $1,324.80

SE 4 $1,198.08

LYST 2 $6,946.56

--

stevenva

------------------------------------------------------------------------

stevenva's Profile: http://www.excelforum.com/member.php...o&userid=16100

View this thread: http://www.excelforum.com/showthread...hreadid=275496

rate (CAGR) starting with an initial amount which grows or shrinks depending

upon the earnings for the year? Is the formula included in the formulae

included in Excel?

That data is in columns:

A: Year

B: Year's average interest rate growth or loss

C: Value of investment, e,g. starting at $1,000 and changing each year by

the rate in column B.

D: The CAGR effective to that year.

Thank you for your assistance.

Pat

I have rows of data for which I am trying to find the average return in

%.

How can I do this correctly with an array formula? I need to take the

((exit minus the entry)/entry) (b-a)/a. for each row, then average

that number.

I've tried to use an array {=AVERAGE(B2:B3-A2:A3)/A2:A3}

but this seems to find: (average B-average A)/ averageA

This returns a slightly different (read: wrong) answer.

Any ideas. I think there is a quick solution involving arrays, but I

don't use them very often.

Thanks

Paul

--

Paul987

------------------------------------------------------------------------

Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850

View this thread: http://www.excelforum.com/showthread...hreadid=540360

I have rows of data for which I am trying to find the average return in %.

How can I do this correctly with an array formula? I need to take the ((exit minus the entry)/entry) (b-a)/a. for each row, then average that number.

I've tried to use an array {=AVERAGE(B2:B3-A2:A3)/A2:A3}

but this seems to find: (average B-average A)/ averageA

This returns a slightly different (read: wrong) answer.

Any ideas. I think there is a quick solution involving arrays, but I don't use them very often.

Thanks

Paul

I am trying to write this fuction for calculating annualized return

from the monthly returns I have. If I use, (1+Rm) as my range input, I

can calculate the annualized return. But when I am trying to add 1 to

monthly returns within the function and get the answer, I am getting an

error. (apparently I can not add 1 to each element in the input

array/range).

Can somebody please help! (thanks in advance)

Function ROR(d As Range, n As Integer)

Dim a() As Single

Dim f As Integer

'f = Application.Count(d)

c = d.Rows.Count

e = d.Columns.Count

If c > e Then

f = c

Else

f = e

End If

ReDim a(d.Cells.Count)

For i = 1 To UBound(a)

a(i) = d.Cells(i)

Next i

prod = Application.WorksheetFunction.Product(a())

k = n / f

ROR = prod ^ k - 1

End Function

best regards

8 2011 39.49073466

8 2011 40.10209175

7 2011 41.21885669

7 2011 40.36205606

7 2011 39.45128469

7 2011 41.65746244

7 2011 41.20755325

7 2011 43.41200631

7 2011 44.34023394

7 2011 44.24648197

7 2011 43.49374838

7 2011 44.51996125

7 2011 40.87890875

7 2011 43.80680931

7 2011 42.25107419

7 2011 38.73625294

6 2011 42.39128759

6 2011 42.68405703

6 2011 43.03057088

6 2011 40.92321709

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.

Taking 3 monthly returns, adding 1 to each. I then use the geomean formula of that range less 1. Then do the same with the product formula and get a very different answer.

What's the right approach? I always thought geomean was correct for chain-linking investment returns but when I think about it, it's only returning the geometric mean-- ie the average distance around the mean.

When do you use geomean then?

For example, monthly returns are:

0.41%

0.41%

0.42%

geomean gives me 0.41%

product gives me 1.25%

In cells A1:A5 enter 100, 105, 110, 115, 120.

In A7 enter {=AVERAGE((A2:A5-A1:A4)/A1:A4)}, as recommended here,

in A8, {=GEOMEAN(A2:A5/A1:A4)-1} and

in A9, enter =(A5/A1)^(1/4)-1.

This last formula is a simple compounding growth rate where 4 is the number of year's growth.

In cells B2 enter =A1*(1+A7)

in B3, =B2*(1+$A$7)

in B4, =B3*(1+$A$7) and

in B5, =B4*(1+$A$7).

In C5 enter =A5-B5.

If the growth formula is accurate, B5 should = A5; i.e. C5=0. Use four or more decimals of precision because these numbers are not big--real world numbers can be five or more orders of magnitude bigger.

If you use the averaging formula (A7) in column B, B5 does not = A5; if you use the geometric mean (A8), it does; if you use the simple growth formula (A9), it also does.

Conclusion: using the arithmetic mean is not as precise as using the other two methods.

Problem: now change A5 to -20. The formulas collapse. The arithmetic mean gives an answer of -25.7710%, which cannot be right because it is asymptotic to zero, while the other two cannot be calculated. Yet the progression in column A, or its inverse, often arises in financial data (profits are not a bad example).

Question: what is to be done?

Regards, John.

jasharp@telstra.com

Can anyone suggest a formula (or is there already a spreadsheet floating around for download) that can perform compounding for return on investment?

My goal is to simply create THREE inputs:

1. Period of Years

2. Average Percent Return Per Year

3. Average Monthly Contribution

From those three inputs, let's say 30 years, 12% per year, and $500 per month investment, how much money would it be compounded at by the end of 30 years? I know how to code in Excel to the point of doing it by hand, but then the 30 years input wouldn't be as efficient. Is there a way to do this without writing out a return for each month?

Thanks so much! EDR

4.06

1.28

3.411

#N/A N.A.

2.627

1.157

4.9

4.9

2.963

I would like to put geometric averages of each column from each tab in a summary tab.

* I tried using =GEOMEAN(...), but it can not handle the quantity of data that I have nor can it take non-numeric fields.

* Also Tried something like this =EXP(AVERAGE(IF(ISNUMBER(C:C),LN(C:C)))) as an array, but unless cells are specified explicitly, ie c10:c300, I get an answer of 1. I can not specify the cells because I have a variable height of each column.

* Perhaps someone can show me a VBA solution

greetings

Mirko

I have one Row that houses numbers 80-90 in seperate cells (11 columns A20:

K20) - these are my Numeric Labels.

I then have several other Rows that span the same number of columns as the

Numeric Labels (A21:K100) and house Numeric Values that relate to the Numeric

Labels.

I would like to AVERAGE the Numeric Values in each Row and have a Numeric

Label (value) that corresponds to the calculated average returned as the

result.

Example: Sample Data

A20:K20 (Numeric Label) 80 81 82 83 84 85 86 87 88 89 90

A21:K21 (Numeric Value) 07 06 05 03 09 09 00 02 04 12 10

Based on the above data - the Expected Result is 81

Average = 6; Return Numeric Label = 81

The leading zeros shown in A21:K21 is for alignment purposes only.

I would appreciate two formulas:

1) Includes zeros to be averaged

2) Excludes zeros from being averaged

I have tried variations on this =INDEX($A$20:$KJ$20,MATCH(AVERAGE($A21:$K21),

$A21:$K21,0)) but receive the #N/A error message.

Thanks

Sam

--

Message posted via http://www.officekb.com

I have one Row that houses numbers 80-90 in seperate cells (11 columns A20:

K20) - these are my Numeric Labels.

I then have several other Rows that span the same number of columns as the

Numeric Labels (A21:K100) and house Numeric Values that relate to the Numeric

Labels.

I would like to AVERAGE the Numeric Values in each Row and have a Numeric

Label (value) that corresponds to the calculated average returned as the

result.

Example: Sample Data

A20:K20 (Numeric Label) 80 81 82 83 84 85 86 87 88 89 90

A21:K21 (Numeric Value) 07 06 05 03 09 09 00 02 04 12 10

Based on the above data - the Expected Result is 81

Average = 6; Return Numeric Label = 81

The leading zeros shown in A21:K21 is for alignment purposes only.

I would appreciate two formulas:

1) Includes zeros to be averaged

2) Excludes zeros from being averaged

I have tried variations on this =INDEX($A$20:$KJ$20,MATCH(AVERAGE($A21:$K21),

$A21:$K21,0)) but receive the #N/A error message.

Thanks

Sam

--

Message posted via http://www.officekb.com

Write a custom function to calculate the geometric average of the returns listed in cells B6:B15.

Below is the formula for a geometric average:

[(1 + R1) * (1 + R2) * ... * (1 + Rt)]^(1/t) - 1

There are three requirements for your function:

i. Your function should have a single input – the range of cells that contain the return series.

ii. Use a For/Next loop to cycle through the returns in the range provided by the user.

iii. Use an If/Then statement to determine if the returns are entered in decimal or percentage point form. You may assume that values above 1 are in percentage

---------------------------------------------------------------------------------------------------------------

This is my function so far and I keep getting a #NAME error. ANY HELP WOULD BE GREATLY APPRECIATED

Function MyGEO(Returns As Range)

Dim Varcount As Integer

Dim t As Integer

Dim mean As Double

Dim geomean As Double

If Returns >= 1 Then Returns = Returns / 100

Varcount = Returns.Count

mean = 1

For t = 1 To Varcount

mean = mean * (1 + Returns(t))

Next t

geomean = mean ^ (1 / Varcount) - 1

MyGEO = geomean

End Function

I work at a sewage treatment plant. For regulatory purposes we need to

report the highest 7-day moving average concentration for a number of

variables in our effluent. We also need to report the highest 7-day

geometric mean for coliform bacteria. All within a given month. (The bosses

won't be interested in seeing the highest seven-day average that occurred

within the first six days of the month.)

For years we simply configured a column holding 7-day averages (or 7-day

geomean) next to the respective columns holding daily data and used the MAX

function to find the highest running average.

For most of the reports we have monthly data with dates in column A. I've

tried setting up an array formula using the offset function with the

reference set one row above the start of the data and using DAY($a$11:$a41)

to pluck out the row offset. I haven't had much luck.

Similarly, I'd like to convert the files to year-long files (they're

currently monthly). Would there be an array formula that could be used to

pick out the highest seven day average all held within the same month for a

filtered spreadsheet?

Thanks,

EQC

I have a list of data that I want to average the number in column B when there is a match in column A

COLUMN A | COLUMN B

alpha | 100

beta | 200

gamma | 300

alpha | 400

beta | 500

What I'm looking to do is return the MEAN of values in column B where column A = alpha. Though, I would settle for returning a list of the values that match the criteria. Index/Match combinations don't seem to be helping me because they only return the first match, I'd like to return all matches.

What direction would you point me to accomplish this? Thanks!

I have a real beast of a query. I have an array of numbers, the array consists of lots of 0s with some positive numbers interspersed. I need to find all the possible 'pairs' of positive numbers and calculate an average of these numbers and those in between. I need these averages returned to a new column, with the distances over which the average was calculated returned in the column next to it.

I've attached a book to show what I mean that I hope makes it a great deal clearer than this description.

In reality, the arrays I'll be looking at will be a lot longer than this example.

Please shout if this and the example book doesn't make things clear

No luck finding an answer? You could always try Google.