Select data within a range

**lone.vaas** · 03-16-2019, 04:15 AM

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.

**AliGW** · 03-16-2019, 04:19 AM

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)

**lone.vaas** · 03-16-2019, 04:51 AM

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.

**AliGW** · 03-16-2019, 04:55 AM

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!

**lone.vaas** · 03-16-2019, 05:01 AM

Hello,

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

**AliGW** · 03-16-2019, 05:16 AM

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

**lone.vaas** · 03-16-2019, 05:29 AM

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.

**AliGW** · 03-16-2019, 05:31 AM

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!

**lone.vaas** · 03-16-2019, 05:45 AM

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

**AliGW** · 03-16-2019, 06:14 AM

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).

**lone.vaas** · 03-16-2019, 06:34 AM

Alright, sorry for taking your time!

your help was very appreciated!

**avk** · 03-16-2019, 07:02 AM

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

**AliGW** · 03-16-2019, 07:04 AM

Originally Posted by lone.vaas

Alright, sorry for taking your time!

I wasn't complaining! Just had to go away and do something else.

**lone.vaas** · 03-16-2019, 10:02 AM

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

**torachan** · 03-16-2019, 12:25 PM

An 'elastic' version with no on sheet formula.

torachan.

**AliGW** · 03-16-2019, 12:26 PM

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.

**lone.vaas** · 03-18-2019, 01:20 AM

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

**lone.vaas** · 04-07-2019, 09:52 AM

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

**lone.vaas** · 04-09-2019, 01:12 AM

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?

**torachan** · 04-14-2019, 10:27 AM

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

**lone.vaas** · 04-19-2019, 01:32 AM

Originally Posted by torachan

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.

Select data within a range

LinkBack

Thread Tools

Rate This Thread

Display

Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Re: Select data within a range

Thread Information

Users Browsing this Thread

Similar Threads

Select range of data

[SOLVED] Select range of data VBA

Select start + end date using ComboBoxes, then copy data in that data range to sheet.

Select specific range in last row with data

Look up cell, use as reference to select from a range, to then select data in a column...

Select a set of data that matchs a range then input said data into a new column SOS

Select / range data

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions