Excel 2003 Array Formula in Excel 2007

[Ben C.]

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

 

[Ron Rosenfeld]

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

 

[pub]

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]

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

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

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