PC Review


Reply
Thread Tools Rate Thread

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!

Thanks in advance!

Ben Cacace
 
Reply With Quote
 
 
 
 
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!
>
>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
 
Reply With Quote
 
 
 
 
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!
>>
>>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.
 
Reply With Quote
 
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



 
Reply With Quote
 
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
 
Reply With Quote
 
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
 
Reply With Quote
 
JustAsking
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



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) Keith R Microsoft Excel Programming 3 13th Nov 2007 05:08 PM
meaning of : IF(Switch; Average(array A, array B); array A) =?Utf-8?B?RFhBVA==?= Microsoft Excel Worksheet Functions 1 24th Oct 2006 06:11 PM
Tricky array formula issue - Using array formula on one cell, then autofilling down a range aspenbordr Microsoft Excel Programming 0 27th Jul 2005 03:59 PM
Can I have a Cell Value = ARRAY FORMULA - ARRAY FORMULA =?Utf-8?B?QmV0aCBEeWU=?= Microsoft Excel Worksheet Functions 1 22nd Apr 2004 08:19 AM
array formula nested in non-array formula Monte Manning Microsoft Excel Worksheet Functions 4 16th Jan 2004 03:03 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:11 AM.