#Value Error using arrays

[mcashe]

I'm being tortured by the #Value error when trying to simply count the number of occurences between two dates that is <= 10. For example

{=sum(if(close date - open date <= 10,1,0))}
something like that. When I don't use a range the formula works. I need to compare several dates. Therefore, a range is necessary.

Thanks in advance

 

[Tim Francis-Wright]

On 2002-03-18 11:09, mcashe wrote:
I'm being tortured by the #Value error when trying to simply count the number of occurences between two dates that is <= 10. For example

{=sum(if(close date - open date <= 10,1,0))}
something like that. When I don't use a range the formula works. I need to compare several dates. Therefore, a range is necessary.

Thanks in advance

1. Try this (an array/control-shift-enter formula):
=SUM((close date - open date <=10)* 1)
where close date and open date are ranges that make sense.

2. You can also set up a third range to be TRUE if the appropriate cells in the other
two ranges are close enough, i.e.,
=(closedate1 - opendate1 <=10)

then use this standard formula:
=COUNTIF(comparerange, TRUE)

3. Or you can use a database function to do
the dirty work.

 

[Mark W.]

On 2002-03-18 11:09, mcashe wrote:
I'm being tortured by the #Value error when trying to simply count the number of occurences between two dates that is <= 10. For example

{=sum(if(close date - open date <= 10,1,0))}
something like that. When I don't use a range the formula works. I need to compare several dates. Therefore, a range is necessary.

Thanks in advance

Your formula must be entered as an array formula...

{=SUM(IF('close date'-'open date'<=10,1,0))}

Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

BTW, you don't need the IF function. The array formula...

{=SUM(('close date'-'open date'<=10)+0)}

...works as well.

 

[Dave Patton]

try

=SUM(IF(rB_-rA_<=10,1,0))

change ranges from rB_ and rA_
to reflect your named ranges.

N.B. Enter with Ctrl-Shift-Enter (CSE)

HTH Dave Patton

 

[mcashe]

Guys,
Thanks for all your responses. Unfortunately, the problem still exists. Every suggestion you guys gave actually worked until I entered the range. I simply enter like a 1 to 1 comparison everything is fine, but the range screws it up. i.e.

=sum(if('all closed'!$k$2:k$235 - 'all closed'!$I$2:I$235 <= 10,1,0))

 

[Mark W.]

On 2002-03-18 15:52, mcashe wrote:
Guys,
Thanks for all your responses. Unfortunately, the problem still exists. Every suggestion you guys gave actually worked until I entered the range. I simply enter like a 1 to 1 comparison everything is fine, but the range screws it up. i.e.

=sum(if('all closed'!$k$2:k$235 - 'all closed'!$I$2:I$235<= 10,1,0))

Check your values in the ranges listed above. Make sure that they contain date values rather than a text representation of dates (e.g., "3/18/02"). In an unused column enter =AND(ISNUMBER(I2),ISNUMBER(K2)) and copy down. If you don't get TRUE for all rows there's your problem.
This message was edited by Mark W. on 2002-03-18 16:05

 

[Dave Patton]

try the following

1. enter the formula on the same sheet as the
data is located
Ensure that you Array enter (CSE) see
above.

Are results correct?

2. go to where you want to enter the formula
and enter it again. When I entered the formula that included named ranges in another
sheet, it showed fully qualified address that included the file name.

HTH