# Select data within a range

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

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

3. ## 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. ## 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. ## Re: Select data within a range

Hello,

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

6. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Select data within a range

Alright, sorry for taking your time!

your help was very appreciated!

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

13. ## Re: Select data within a range

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

14. ## 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. ## Re: Select data within a range

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

16. ## 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. ## 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. ## Re: Select data within a range

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

19. ## 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?

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

#### Thread Information

##### Users Browsing this Thread

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

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