# 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.

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?

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,

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?

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

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)

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!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1