Excel 2003 Array Formula in Excel 2007

Discussion in 'Microsoft Excel Discussion' started by Ben C., Jul 7, 2008.

  1. Ben C.

    Ben C. Guest

    Hello all,

    I am moving from Excel 2003 to Excel 2007. One problem I'm having is
    with the following array formula which works in 2003 but returns a
    #VALUE! in 2007. Is there something I need to change in my 2007 setup
    to make this work?

    {=(SUM(IF(LEN($H$7:$H$476)<>0,1,0))-1)-(SUM(IF(LEN($K$7:$K
    $476)<>0,1,0))-1)}

    .... I know there are probably more efficient ways of doing this so I'm
    not looking for better solutions. I'm wondering ... why won't it work?
    There are 3 array formulas on the spreadsheet and all are returning
    #VALUE!

    Thanks in advance!

    Ben Cacace
     
    Ben C., Jul 7, 2008
    #1
    1. Advertisements

  2. On Sun, 6 Jul 2008 19:33:04 -0700 (PDT), "Ben C." <> wrote:

    >Hello all,
    >
    >I am moving from Excel 2003 to Excel 2007. One problem I'm having is
    >with the following array formula which works in 2003 but returns a
    >#VALUE! in 2007. Is there something I need to change in my 2007 setup
    >to make this work?
    >
    >{=(SUM(IF(LEN($H$7:$H$476)<>0,1,0))-1)-(SUM(IF(LEN($K$7:$K
    >$476)<>0,1,0))-1)}
    >
    >... I know there are probably more efficient ways of doing this so I'm
    >not looking for better solutions. I'm wondering ... why won't it work?
    >There are 3 array formulas on the spreadsheet and all are returning
    >#VALUE!
    >
    >Thanks in advance!
    >
    >Ben Cacace


    Did you copy the formula *WITHOUT* the braces {...} and then hold down
    <ctrl><shift> while hitting <enter>, allowing Excel to place the braces
    automatically?
    --ron
     
    Ron Rosenfeld, Jul 7, 2008
    #2
    1. Advertisements

  3. Ben C.

    pub Guest

    Ron Rosenfeld <> wrote in
    news::

    > On Sun, 6 Jul 2008 19:33:04 -0700 (PDT), "Ben C." <>
    > wrote:
    >
    >>Hello all,
    >>
    >>I am moving from Excel 2003 to Excel 2007. One problem I'm having is
    >>with the following array formula which works in 2003 but returns a
    >>#VALUE! in 2007. Is there something I need to change in my 2007 setup
    >>to make this work?
    >>
    >>{=(SUM(IF(LEN($H$7:$H$476)<>0,1,0))-1)-(SUM(IF(LEN($K$7:$K
    >>$476)<>0,1,0))-1)}
    >>
    >>... I know there are probably more efficient ways of doing this so I'm
    >>not looking for better solutions. I'm wondering ... why won't it work?
    >>There are 3 array formulas on the spreadsheet and all are returning
    >>#VALUE!
    >>
    >>Thanks in advance!
    >>
    >>Ben Cacace

    >
    > Did you copy the formula *WITHOUT* the braces {...} and then hold down
    > <ctrl><shift> while hitting <enter>, allowing Excel to place the
    > braces automatically?
    > --ron


    Ben,
    ron is right.
    i just copied & pasted your formula,
    i had to delete the curly braces, and hit CSE,
    and the formula worked just fine in Excel 2007.
     
    pub, Jul 7, 2008
    #3
  4. Ben C.

    Bob Phillips Guest

    You can do it sans-array

    =COUNTIF($H$7:$H$476,"<>")-COUNTIF($K$7:$K$476,"<>")

    --
    HTH

    Bob

    (there's no email, no snail mail, but somewhere should be gmail in my addy)

    "Ben C." <> wrote in message
    news:...
    > Hello all,
    >
    > I am moving from Excel 2003 to Excel 2007. One problem I'm having is
    > with the following array formula which works in 2003 but returns a
    > #VALUE! in 2007. Is there something I need to change in my 2007 setup
    > to make this work?
    >
    > {=(SUM(IF(LEN($H$7:$H$476)<>0,1,0))-1)-(SUM(IF(LEN($K$7:$K
    > $476)<>0,1,0))-1)}
    >
    > ... I know there are probably more efficient ways of doing this so I'm
    > not looking for better solutions. I'm wondering ... why won't it work?
    > There are 3 array formulas on the spreadsheet and all are returning
    > #VALUE!
    >
    > Thanks in advance!
    >
    > Ben Cacace
     
    Bob Phillips, Jul 7, 2008
    #4
  5. Ben C.

    Ben C. Guest

    Thanks for the tips. I didn't copy the formula. This is a workbook I
    used in Excel 2003 that was working for years.

    I know how to enter an array with Ctrl+Shift+Enter so this is not the
    problem.

    All I did was open the file in Excel 2007 and the formula shows a
    #VALUE!. When I tried to edit [F2] and CSE the formula the same result
    happens.

    Thanks again.

    Ben Cacace
     
    Ben C., Jul 7, 2008
    #5
  6. Ben C.

    Ben C. Guest

    On Jul 7, 8:42 am, "Ben C." <> wrote:
    > All I did was open the file in Excel 2007 and the formula shows a
    > #VALUE!. When I tried to edit [F2] and CSE the formula the same result
    > happens.


    Thanks again for the tips. I wanted to post the resolution here in
    case someone locates this thread in the future looking an answer.
    Having made use of these groups for years I know how frustrating it is
    following a lead that doesn't resolve itself.

    The issue for me was related to the section called "Lotus
    Compatibility Settings for:". The setting for "Transition formula
    evalution" was checked. When I removed the checkmark for this item the
    formula worked as it did in Excel 2000 and Excel 2003.

    To get to this setting the old way:

    1. ALT + T)ools then select Advanced in the sidebar.
    2. The last section is called "Lotus Compatibility Settings for:"

    To get there the new way:

    1. Click on the Office button to the left of the Home menu item
    2. Select "Excel Options" on the bottom of the screen
    3. Then select Advanced in the sidebar.
    4. The last section is called "Lotus Compatibility Settings for:"

    All the best.

    Ben Cacace
    Manhattan, NYC
     
    Ben C., Jul 20, 2008
    #6
  7. Ben C.

    JustAsking Guest

    Test - do not reply

    "Ben C." <> wrote in message
    news:...
    > Hello all,
    >
    > I am moving from Excel 2003 to Excel 2007. One problem I'm having is
    > with the following array formula which works in 2003 but returns a
    > #VALUE! in 2007. Is there something I need to change in my 2007 setup
    > to make this work?
    >
    > {=(SUM(IF(LEN($H$7:$H$476)<>0,1,0))-1)-(SUM(IF(LEN($K$7:$K
    > $476)<>0,1,0))-1)}
    >
    > ... I know there are probably more efficient ways of doing this so I'm
    > not looking for better solutions. I'm wondering ... why won't it work?
    > There are 3 array formulas on the spreadsheet and all are returning
    > #VALUE!
    >
    > Thanks in advance!
    >
    > Ben Cacace
     
    JustAsking, Jul 29, 2008
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    0
    Views:
    157
  2. TKT-Tang

    Re : Excel Quest of an Array Formula

    TKT-Tang, Mar 29, 2006, in forum: Microsoft Excel Discussion
    Replies:
    1
    Views:
    202
    Harlan Grove
    Mar 29, 2006
  3. THOMAS CONLON
    Replies:
    3
    Views:
    1,473
  4. news.microsoft.com

    Excel 2003-Sp2 - Resize the formula box in the formula bar

    news.microsoft.com, Apr 26, 2007, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    1,521
    Bob Flanagan
    Apr 26, 2007
  5. Replies:
    4
    Views:
    284
    Albert
    Nov 10, 2013
Loading...

Share This Page