+ Reply to Thread
Results 1 to 21 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
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,671

    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
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,671

    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
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,671

    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
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,671

    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
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,671

    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,823

    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
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,671

    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
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,281

    Re: Select data within a range

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

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,671

    Re: Select data within a range

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

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

    Re: Select data within a range

    Wow! That's even better than what I compiled and much better than what I expected!
    How did you do that?!

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

    Re: Select data within a range

    Hello, what if i want to take the file 1 step further? can i post here again?

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

    Re: Select data within a range

    Hi, I have attached the modified file here.

    So the idea is to put multiple ranges and it calculated the average values between that range. I have modified it to include more data.

    however i am unable to get the colors for the selection across the calculated average values. as this will help in tracking which selection has which average.

    is it possible to do it?
    Attached Files Attached Files

  20. #20
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,281

    Re: Select data within a range

    I have had a quick look.
    Not immediately obvious to me.
    I will leave this one for a rainy day.
    torachan.

  21. #21
    Registered User
    Join Date
    03-16-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 365
    Posts
    16
    Quote Originally Posted by torachan View Post
    I have had a quick look.
    Not immediately obvious to me.
    I will leave this one for a rainy day.
    torachan.
    OK but thanks for looking in to this.

+ 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. Select range of data
    By vipx2 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-25-2016, 03:16 PM
  2. [SOLVED] Select range of data VBA
    By hanif in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-25-2016, 11:40 AM
  3. Replies: 3
    Last Post: 09-03-2014, 09:59 AM
  4. Select specific range in last row with data
    By violetmind in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-13-2014, 01:55 AM
  5. Replies: 4
    Last Post: 06-17-2013, 05:21 AM
  6. Select a set of data that matchs a range then input said data into a new column SOS
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-04-2012, 07:46 PM
  7. Select / range data
    By ciprian in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-04-2011, 02:49 AM

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