# Excel 2003 Array Formula in Excel 2007

Ben C.
Guest
Posts: n/a

 7th Jul 2008
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!

Ben Cacace

Ron Rosenfeld
Guest
Posts: n/a

 7th Jul 2008
On Sun, 6 Jul 2008 19:33:04 -0700 (PDT), "Ben C." <(E-Mail Removed)> 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!
>
>
>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

pub
Guest
Posts: n/a

 7th Jul 2008
Ron Rosenfeld <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> On Sun, 6 Jul 2008 19:33:04 -0700 (PDT), "Ben C." <(E-Mail Removed)>
> 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!
>>
>>
>>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.

Bob Phillips
Guest
Posts: n/a

 7th Jul 2008
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." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
Guest
Posts: n/a

 7th Jul 2008
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.
Guest
Posts: n/a

 20th Jul 2008
On Jul 7, 8:42*am, "Ben C." <(E-Mail Removed)> 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

Guest
Posts: n/a

 29th Jul 2008
Test - do not reply

"Ben C." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Keith R Microsoft Excel Programming 3 13th Nov 2007 05:08 PM =?Utf-8?B?RFhBVA==?= Microsoft Excel Worksheet Functions 1 24th Oct 2006 06:11 PM aspenbordr Microsoft Excel Programming 0 27th Jul 2005 03:59 PM =?Utf-8?B?QmV0aCBEeWU=?= Microsoft Excel Worksheet Functions 1 22nd Apr 2004 08:19 AM Monte Manning Microsoft Excel Worksheet Functions 4 16th Jan 2004 03:03 PM

Features