Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: MATCH??

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have data in A1:A20 - I would like to input a formula in A21 to tell me if data in A1:A20 is the same (i.e. each cell equals 1%). Also, can i include a threshold (if one cell equals 1.0002% and another cell equals 1.0003% the formula would still return "TRUE".

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

    Default

    what about something like this, what lets you set the tolerance...... =IF(MAX(A1:A20)-MIN(A1:A20)<=0.005,"true","FALSE")

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

    Default

    consider one of the following

    =AND(SUM(A1:A20)>=0.2,SUM(A1:A20)<=0.200005,COUNTIF(A1:A20,0.01)>=18)

    =AND(SUM(A1:A20)>=0.2,SUM(A1:A20)<=0.200005)

    =IF((SUM(A1:A20)>=0.2)*(SUM(A1:A20)<=0.200005),"Yes within range","No outside range")

    [ This Message was edited by: Dave Patton on 2002-04-02 07:59 ]

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following:

    =IF(C1:C5<0.02,IF(LEFT(C1:C5,4="0.01","TRUE","FALSE"),"FALSE")

    You can change the tolerance by changing LEFT(C1:C5,4="0.01"
    Since this is an array formula (CSE formula) you have to enter it in by pressing Ctr+Shift+Enter not just a plain enter.

    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #5
    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

    Consider the array formula...

    {=AND(ROUND(A1:A20,2)=0.01)}

    The desired precision is governed by the 2nd ROUND argument (in red).

    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.

    [ This Message was edited by: Mark W. on 2002-04-02 08:37 ]

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,619
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Probably a bit convoluted, but try:

    =AND(ROUND(MAX(A1:A20),LEN(B2)-2)<=ROUND(B1+B2,LEN(B2)-2),ROUND(MIN(A1:A20),LEN(B2)-2)>=ROUND(B1-B2,LEN(B2)-2))

    where B1 houses the essential value (that is, 1%) and B2 the allowed difference or the tolerance, e.g., 0.0002%.


  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin, a BIT convoluted????? I got a nosebleed when trying to read that one.

    Just kidding....nicely done as always

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,619
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-02 08:37, wi_guy wrote:
    Aladin, a BIT convoluted????? I got a nosebleed when trying to read that one.

    Just kidding....nicely done as always
    Inspired from yours... forget to record that.

    Aladin

Some videos you may like

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
  •