+ Reply to Thread
Results 1 to 5 of 5

Selecting median data points for integral values in a long raw data

  1. #1
    Registered User
    Join Date
    09-23-2019
    Location
    Enschede, Netherlands
    MS-Off Ver
    Home and student 2016
    Posts
    49

    Selecting median data points for integral values in a long raw data

    Please refer the attachment

    Hello
    I get raw data in the form as seen in the columns A and B.
    From this data, I have to select median points corresponding to each integer.
    For example, if we see in column A, I need one median point from 18..., one from 19…, one from 20… and so on.
    The number of data points vary for each number.
    But the numbers are always in increasing (or decreasing) order.
    I can do it one by one, but real data has around 5000 data points. Each number has 45-50 data points and number are also from, for example 18 - 80.
    So I am looking for a code that I can apply to get median points for column A and corresponding points to them from column B.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,212

    Re: Selecting median data points for integral values in a long raw data

    Your median returns the value between 2 points and then you take the higher value, if you are happier with the lower value
    f2=18

    g2 as an array with Shift Control enter
    =MEDIAN(IF(INT($A$1:$A$39)=F2,$A$1:$A$39,""))
    h2
    =VLOOKUP(G2,$A$1:$B$39,2)

    to get your value for h2 as in the example (there may be shorter formula)
    =IF(INDEX($A$1:$A$39,MATCH(G2,$A$1:$A$39,1))=G2,INDEX($B$1:$B$39,MATCH(G2,$A$1:$A$39,1)),INDEX($B$1:$B$39,MATCH(G2,$A$1:$A$39,1)+1))

  3. #3
    Registered User
    Join Date
    09-23-2019
    Location
    Enschede, Netherlands
    MS-Off Ver
    Home and student 2016
    Posts
    49

    Re: Selecting median data points for integral values in a long raw data

    Dear Davsth

    Thank you soo much for the reply! This works like a charm!!

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Selecting median data points for integral values in a long raw data

    or... If still interested, with 18,19,20, etc down from F2...

    in G2, array entered and copied across/down:

    =MEDIAN(IF(INT($A$1:$A$39/1)=$F2,A$1:A$39))
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Selecting median data points for integral values in a long raw data

    Another option, using Power Query - format your source data as a table, with headers {"Number", "Value"}, then use:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Selecting particular data points
    By mmsrd2 in forum Excel General
    Replies: 1
    Last Post: 03-25-2019, 08:29 PM
  2. Calculating median values for grouped data
    By anita2017 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-17-2017, 04:01 PM
  3. Selecting needed grid points data
    By akumagaavese in forum Excel General
    Replies: 3
    Last Post: 04-18-2014, 12:43 PM
  4. selecting grid points of data
    By akumagaavese in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-15-2014, 12:53 PM
  5. [SOLVED] Quartile/Average/Median In Array With Missing Data Points
    By splendidus in forum Excel General
    Replies: 10
    Last Post: 05-14-2012, 09:51 AM
  6. Selecting data points
    By Xmosis in forum Excel General
    Replies: 2
    Last Post: 03-07-2011, 04:46 AM
  7. [SOLVED] Selecting Data Points from graph
    By Keith Bramley in forum Excel General
    Replies: 3
    Last Post: 03-03-2005, 01:06 PM

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