# Adding to a Range of Cells

1. ## Adding to a Range of Cells

This may seem like a elementary question but I guess I never ran into this issue before.

I have a range of values as follows:

A 0
B .5
C .2
D .9
E .1

I am doing a geomean function and need to do the follow formula:

=geomean(1+B1:B5)-1

However, when I evaluate the formula excel is looking at the formula B1:B5 instead of evaluating 1+B1:B5. I keep getting a #VALUE! error.  Register To Reply

2. ## Re: Adding to a Range of Cells

Hello,

from the Excel Help:
The GEOMEAN function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Number1, number2, ... Number1 is required, subsequent numbers are optional. 1 to 255 arguments for which you want to calculate the mean. You can also use a single array or a reference to an array instead of arguments separated by commas.
I'm not quite clear what exactly you want to achieve with 1+B1:B5

Can you explain? Add a value of 1 to each of these cells? Add a parameter of 1 to the function?  Register To Reply

3. ## Re: Adding to a Range of Cells

You appear to have set up an array formula (to avoid the "0"?). Try using Ctrl-Shift-Enter with it.

Cheers,  Register To Reply

4. ## Re: Adding to a Range of Cells

Hi jdot,

In the remarks for the GEOMEAN function is says:
If any data point ≤ 0, GEOMEAN returns the #NUM! error value.
See http://office.microsoft.com/en-gb/ex...005209105.aspx
Your first value in B1 is zero.

I also think you want 1, B1:B5 instead of 1+B1:B5

Does that help?  Register To Reply

5. ## Re: Adding to a Range of Cells

Thanks for the quick response, so what I want to do is for excel to evaluate the formula as follows:

A 0
B .5
C .2
D .9
E .1

=geomean(1+B1:B5)-1

=geomean(1:1.5:1.2:1.9:1.1)-1

Instead, Excel is doing the following:

=geomean(1+B1:B5)-1
=geomean(1+#value!)-1  Register To Reply

6. ## Re: Adding to a Range of Cells

The syntax is

=GEOMEAN(A2:A8

I don't understand what you want it to do with =geomean(1:1.5:1.2:1.9:1.1)-1

The colon is the operator that defines a range of cells

If you want to add a 1 to each value in the range, then use Ctrl-shift-Enter to create an array formula. It will then evaluate as

=Geomean(1,1.5,1.2,1.9,2)  Register To Reply

7. ## Re: Adding to a Range of Cells

What I'm trying to do is if I use geomean on a range of cells that are less than zero, then when geomean takes the nth root of the product of the numbers the results will be calculated incorrectly.

The workaround would be to do the following:

A 0
B .5
C .2
D .9
E .1

Add a third column with =B1+1 and drag down so I have

A 0 1
B .5 1.5
C .2 1.2
D .9 1.9
E .1 1.1

Then do =geomean(C1:C5)

However, to keep my spreadsheet cleaner, I want excel to do the +1 within the formula.

I want excel to do =geomean(1+B1:B5)-1. If excel does it correct, excel should add 1 to all the values in column B1:B5 then take the geomean and subtract 1 last.

Edit:
The ctrl-shift-enter did exactly what I was looking for. Thanks everyone!  Register To Reply