3.51%
3.45%
6.60%
3.04%
3.88%
-7.79%
6.14%
9.08%
-3.63%
0.58%
-9.72%
3.55%

when i enter the formula =product(A1:A12+1)-1, the cell shows the error
#value.

where am I wrong
--
rgds

The error is being generated in the PRODUCT(A1:A12+1) portion of your
formula. What exactly are you trying to do? are you trying to add 1 to each
of the 12 values and then get their product? Trying to convert negative
result to positive?
Might also provide us with the result that you expect given the values in
your example, that would help us see if our offered solution(s) actually work
properly for you.



"spaceage" wrote:

3.51%
3.45%
6.60%
3.04%
3.88%
-7.79%
6.14%
9.08%
-3.63%
0.58%
-9.72%
3.55%

when i enter the formula =product(A1:A12+1)-1, the cell shows the error
#value.

where am I wrong
--
rgds

You are correct. I add 1 to each of the 12 values.Find the product after
adding and then from the product of this, I deduct one to get the return. The
return must be 18.19%.
[(1+0.0351)*(1+0.0345)*(1+0.066)*(1+0.0304)*(1+0.03 88)*(1-0.0779).......................]-1 .
--
rgds


"JLatham" wrote:

The error is being generated in the PRODUCT(A1:A12+1) portion of your
formula. What exactly are you trying to do? are you trying to add 1 to each
of the 12 values and then get their product? Trying to convert negative
result to positive?
Might also provide us with the result that you expect given the values in
your example, that would help us see if our offered solution(s) actually work
properly for you.



"spaceage" wrote:

3.51%
3.45%
6.60%
3.04%
3.88%
-7.79%
6.14%
9.08%
-3.63%
0.58%
-9.72%
3.55%

when i enter the formula =product(A1:A12+1)-1, the cell shows the error
#value.

where am I wrong
--
rgds

You need to enter the formula as an array formula: Control Shift Enter.
--
David Biddulph

"spaceage" wrote in message
...
3.51%
3.45%
6.60%
3.04%
3.88%
-7.79%
6.14%
9.08%
-3.63%
0.58%
-9.72%
3.55%

when i enter the formula =product(A1:A12+1)-1, the cell shows the error
#value.

where am I wrong
--
rgds

still does not work.
--
rgds


"David Biddulph" wrote:

You need to enter the formula as an array formula: Control Shift Enter.
--
David Biddulph

"spaceage" wrote in message
...
3.51%
3.45%
6.60%
3.04%
3.88%
-7.79%
6.14%
9.08%
-3.63%
0.58%
-9.72%
3.55%

when i enter the formula =product(A1:A12+1)-1, the cell shows the error
#value.

where am I wrong
--
rgds

It works for me. Using the values you posted early and committing your
formula with Ctrl+Shift+Enter yields an answer of 0.181966527362382 which,
when formatted as a percentage to 2 decimal places, is 18.20% (not the
18.19% you indicated, but close enough I would say).

Rick


"spaceage" wrote in message
...
still does not work.
--
rgds


"David Biddulph" wrote:

You need to enter the formula as an array formula: Control Shift Enter.
--
David Biddulph

"spaceage" wrote in message
...
3.51%
3.45%
6.60%
3.04%
3.88%
-7.79%
6.14%
9.08%
-3.63%
0.58%
-9.72%
3.55%

when i enter the formula =product(A1:A12+1)-1, the cell shows the error
#value.

where am I wrong
--
rgds

is it possible my numbers as stored as text.
I already did text to column to convert text format into numbers.
or is it something related to add-ins
--
rgds


"Rick Rothstein (MVP - VB)" wrote:

It works for me. Using the values you posted early and committing your
formula with Ctrl+Shift+Enter yields an answer of 0.181966527362382 which,
when formatted as a percentage to 2 decimal places, is 18.20% (not the
18.19% you indicated, but close enough I would say).

Rick


"spaceage" wrote in message
...
still does not work.
--
rgds


"David Biddulph" wrote:

You need to enter the formula as an array formula: Control Shift Enter.
--
David Biddulph

"spaceage" wrote in message
...
3.51%
3.45%
6.60%
3.04%
3.88%
-7.79%
6.14%
9.08%
-3.63%
0.58%
-9.72%
3.55%

when i enter the formula =product(A1:A12+1)-1, the cell shows the error
#value.

where am I wrong
--
rgds

Sub fixmynums()
On Error Resume Next
For Each C In Selection 'Range("a1:a25")
C.NumberFormat = "General"
C.Value = CDbl(C)
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"spaceage" wrote in message
...
is it possible my numbers as stored as text.
I already did text to column to convert text format into numbers.
or is it something related to add-ins
--
rgds


"Rick Rothstein (MVP - VB)" wrote:

It works for me. Using the values you posted early and committing your
formula with Ctrl+Shift+Enter yields an answer of 0.181966527362382
which,
when formatted as a percentage to 2 decimal places, is 18.20% (not the
18.19% you indicated, but close enough I would say).

Rick


"spaceage" wrote in message
...
still does not work.
--
rgds


"David Biddulph" wrote:

You need to enter the formula as an array formula: Control Shift
Enter.
--
David Biddulph

"spaceage" wrote in message
...
3.51%
3.45%
6.60%
3.04%
3.88%
-7.79%
6.14%
9.08%
-3.63%
0.58%
-9.72%
3.55%

when i enter the formula =product(A1:A12+1)-1, the cell shows the
error
#value.

where am I wrong
--
rgds

On Mar 30, 6:23*am, spaceage
wrote:
is it possible my numbers as stored as text.

First, PRODUCT(A1:A12+1) works for me even when A1:A12 are formatted
as text. I am using Office Excel 2003.

Second, you can verify if that is your problem either by trying to
format a cell -- the Format Number menu shows you the current format
-- or by entering the formula =TYPE(A1) into B1 and copy down through
B12. Type 1 is numeric; type 2 is text.

As everyone has said: the most likely problem is that you did not
enter the formula as an array formula in the first place. I wonder if
you are failing to make it an array formula subsequently. It is not
sufficient to select the cell and press ctrl-shift-Enter at this
point.

You must select the cell, press F2, then press ctrl-shift-Enter.
Afterwards, be sure there are curly braces around the formula when you
select the cell. Note that you cannot type the curly braces
yourself. You must use ctrl-shift-Enter.

It does for me, and returns the answer 18.20%.

When you did Control Shift Enter, did Excel put curly brackets { } around
the formula to show that it recognised it as an array formula?
--
David Biddulph

"spaceage" wrote in message
...
still does not work.
--
rgds


"David Biddulph" wrote:

You need to enter the formula as an array formula: Control Shift Enter.
--
David Biddulph

"spaceage" wrote in message
...
3.51%
3.45%
6.60%
3.04%
3.88%
-7.79%
6.14%
9.08%
-3.63%
0.58%
-9.72%
3.55%

when i enter the formula =product(A1:A12+1)-1, the cell shows the error
#value.

where am I wrong
--
rgds

If you have monthly returns, the formula you are using will calculate the
annual return.

However, if your periods are something other than monthly, you will also
need to annualize your total return using the Rate function.

Regards,
Fred.

"spaceage" wrote in message
...
3.51%
3.45%
6.60%
3.04%
3.88%
-7.79%
6.14%
9.08%
-3.63%
0.58%
-9.72%
3.55%

when i enter the formula =product(A1:A12+1)-1, the cell shows the error
#value.

where am I wrong
--
rgds

Something like:
=PRODUCT(B4:B15+1)^(1/numyears)-1


ctrl-shift- enter.




"Fred Smith" wrote in message
...
If you have monthly returns, the formula you are using will calculate the
annual return.

However, if your periods are something other than monthly, you will also
need to annualize your total return using the Rate function.

Regards,
Fred.

"spaceage" wrote in message
...
3.51%
3.45%
6.60%
3.04%
3.88%
-7.79%
6.14%
9.08%
-3.63%
0.58%
-9.72%
3.55%

when i enter the formula =product(A1:A12+1)-1, the cell shows the error
#value.

where am I wrong
--
rgds