Results 1 to 6 of 6

Search for Text within a Range of String, Return IQR of Corresponding Cells

Threaded View

  1. #1
    Registered User
    Join Date
    03-07-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Search for Text within a Range of String, Return IQR of Corresponding Cells

    I have been struggling with this formula. I am trying to search for a certain string of text inside a database of larger strings of text, and each time the string is present inside the larger string, I want to put a number corresponding to the text string into the array, and then return the interquartile range of all numbers inside the array.

    Simple example:

    Assume I am searching for "a:"

    sdsasds | 10
    fsdss | 24
    sawead | 20
    ssssss | 28
    sddsass | 30
    asadaa | 40
    jjjjjjj | 23
    jaaa | 50

    The array would include: 10, 20, 30, 40, and 50, and the inter-quartile range would be 40-20 = 20.

    I can piece together parts of the answer:
    =PERCENTILE(IF(I$2:I$15="r",B$2:B$15,""),0.75)  -  PERCENTILE(IF(I$2:I$15="r",B$2:B$15,""),0.25)
    This would return the interquartile range. It searches Row I and then uses Row B for purposes of forming the array. The problem is that the cells in Column I must be exactly "r" as opposed to merely containing "r."

    =IF(FIND("r",I$2:I$15),B$2:B$15,"")
    This returns instances of "r" inside any string, but the array attempt does not really work.

    I was able to use the following as part of another function:
    =SUMIF(Data!M$4:Data!M6551,"*r*",Data!J$4:Data!J$6547)
    Thanks for any and all help!
    Last edited by jrm0523; 03-08-2012 at 01:51 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1