#Value Error using arrays
#Value Error using arrays
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: #Value Error using arrays

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    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

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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))

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

      

    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com