+ Reply to Thread
Results 1 to 7 of 7

Adding to a Range of Cells

  1. #1
    Registered User
    Join Date
    08-17-2008
    Location
    LA
    Posts
    22

    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.
    Last edited by jdot; 10-10-2011 at 12:58 AM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    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. #3
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    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,
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    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?
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    08-17-2008
    Location
    LA
    Posts
    22

    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. #6
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    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)
    Last edited by teylyn; 10-09-2011 at 09:20 PM.

  7. #7
    Registered User
    Join Date
    08-17-2008
    Location
    LA
    Posts
    22

    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!
    Last edited by jdot; 10-10-2011 at 12:57 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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