+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 21

Select data within a range

  1. #1
    Registered User
    Join Date
    03-16-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 365
    Posts
    16

    Select data within a range

    I have to work with multiple data sets everyday and I need to follow the same procedure for each data set.
    So this is not a 'one time' thing. I am fine with basic excel and that's where my experience is limited to.

    I need to select data based on a range that I define.

    Column A to B are values I define
    Column E to H is the data range
    Column J to M are the results I need. (just the average values within that range actually)

    A formula that takes the range i define, looks through the data range and gives the average within that range would make my life a whole lot easier.

    If anyone could help me please.
    Attached Images Attached Images

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,074

    Re: Select data within a range

    Welcome to the forum!

    You need to take a look at the AVERAGEIFS function, e.g. for K6:

    =AVERAGEIFS(F$2:F$16,$E$2:$E$16,">="&$B2,$E$2:$E$16,"<="&$C2)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    03-16-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 365
    Posts
    16

    Re: Select data within a range

    Wow! that worked amazingly well. Thank you so much!

    but is there a way for excel to display the values in the range as seen in columns J to M?
    because even though it plots the average value, I still need to review the data consistency across the range.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,074

    Re: Select data within a range

    I thought you said you didn't want them:

    Column J to M are the results I need. (just the average values within that range actually)
    Attach a sample workbook. This is not as straightforward as you made it sound!

  5. #5
    Registered User
    Join Date
    03-16-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 365
    Posts
    16

    Re: Select data within a range

    Hello,

    thanks for your help. I have attached a workbook with the data
    I hope it helps!
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,074

    Re: Select data within a range

    The layout you seem to want will make it tricky to achieve. Does it really need to be presented that way?

  7. #7
    Registered User
    Join Date
    03-16-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 365
    Posts
    16

    Re: Select data within a range

    No, not necessarily. I just displayed it like that in the hopes to explain it better.

    The average values will undergo further percentage calculations but I limited the file to selections over the range.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,074

    Re: Select data within a range

    OK - so provide a more complete example of what you are ultimately trying to achieve, please. If we solve this, and then you came back and say you want to do something else, we could spend a long time getting to where you want to be. Tell us everything up front!

  9. #9
    Registered User
    Join Date
    03-16-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 365
    Posts
    16

    Re: Select data within a range

    Apologies, the excel file is the limit to what i want to achieve.

    basically,
    Column A to C are values I define
    Column E to H is the data range
    Column J to M are the results I need. I need a formula here that displays the results from columns E to H and averages the values based on the criteria defined from A to C.

    thats it...

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,074

    Re: Select data within a range

    No, because you said this:

    The average values will undergo further percentage calculations but I limited the file to selections over the range
    Sorry - I am not able to help further this morning, as I am running out of time. I am sure others will step in (although remember it is the weekend).

  11. #11
    Registered User
    Join Date
    03-16-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 365
    Posts
    16

    Re: Select data within a range

    Alright, sorry for taking your time!

    your help was very appreciated!

  12. #12
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,651

    Re: Select data within a range

    As per you data, simply way in "J2" : =MIN(B2:C2) & "J5" =MAX(B2:C2) between two value =J2+1 copy down
    in "K2" =SUMIF($E$2:$E$16,$J2,F$2:F$16) copy paste across.
    same applicable for row 7


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,074

    Re: Select data within a range

    Quote Originally Posted by lone.vaas View Post
    Alright, sorry for taking your time!
    I wasn't complaining! Just had to go away and do something else.

  14. #14
    Registered User
    Join Date
    03-16-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 365
    Posts
    16

    Re: Select data within a range

    Thank you so much, I combined and used elements from all the answers. That seems to have given me what I need!

  15. #15
    Valued Forum Contributor
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    658

    Re: Select data within a range

    An 'elastic' version with no on sheet formula.
    torachan.
    Attached Files Attached Files

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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