Range - Page 2
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Range

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Aladin,

    How about this as the array formula

    IF(Diameter > LowerDiameterRange, IF(Diameter < UpperDiamerRange,IF(Length > LowerLengthRange, IF(Length < UpperLengthRange,RIDNumberRage,"No Match"))))

  2. #12
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-15 15:21, amitshah wrote:
    Hi guys thanks for the help .......

    I have one query though ...... Aladdin the formula works but it also gives false value .... what I mean is that in the second case of the finished goods, the diameter is 6.5 (which is 2<6.5<7, this is fine) but its length 27 is not in the range of fgoods item r1's lengths (5 and 15).

    Is there something that can be done about that?
    Sure. Define LowerL and UpperL following the same procedure that I described for LowerD and UpperD. I leave that to you.

    Then extend the formula in F2 as follows:

    =SUMPRODUCT((C2>=LowerD)*(C2<=UpperD)*(D2>=LowerL)*(D2<=UpperL)*(ROW(LowerD)))

    That's all.

    Aladin

  3. #13
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anyone know if my last array formula would work and if not, why not?

  4. #14
    Board Regular
    Join Date
    Apr 2002
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the help guys ..... sorry for the delay in reply was caught up in another project

    Aladin your fomula works great ...... does exactly what I need.

    Cosmos I tried your array formula it doesnt work. It doesnt get the reference to the item number of rgoods.

    once again thanks for the help

  5. #15
    Board Regular
    Join Date
    Apr 2002
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi this query is a continuation of the initial query I had posted. I hope someone can help me with it in particular aladin answered my question so I am hoping you do remember this question and can answer.

    The formula you gave works great although I have two questions:

    1) it slows the computer when it tries to compute values for a long list can I improve the performance?
    2) when I add a new range (which is a new row in rgoods sheet) it somehow does not include it in the list of the named range. Is there a way to update a named range so that it accpets the new row in the array?

    Please anyone who can answer this it would be greatly appreciated. Thank you in advance.


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