Find cell range based on criteria

User avatar
Sundog
5StarLounger
Posts: 704
Joined: 28 Jan 2010, 22:47
Location: Alien Country (Roswell NM)

Find cell range based on criteria

Post by Sundog »

I have an Excel worksheet with five years of historical data. I'm trying to automate finding the low average by month, the DAY of the month it occurs, and the WEEKDAY of that day.

Hans has helped me find month breaks using =MONTH(A2)<>MONTH(A3). That helped me search for low average using MIN, manually. I then use INDEX and MATCH to find the DAY of the month and the WEEKDAY. To automate this manual search, it seems I need to find the cell range from the first through the last day of each month.

A worksheet with the top 77 rows is attached. Any help is appreciated, to avoid searching through the balance of the data manually.
BTChistory3months.xlsx
You do not have the required permissions to view the files attached to this post.
Sundog

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Find cell range based on criteria

Post by HansV »

See columns P to T in the attached version.

BTChistory3months.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
Sundog
5StarLounger
Posts: 704
Joined: 28 Jan 2010, 22:47
Location: Alien Country (Roswell NM)

Re: Find cell range based on criteria

Post by Sundog »

I see the correct results in Q2:T4. How do I replicate that result for the remaining rows? Neither Copy & Paste nor Ctrl+D seems to work?
Sundog

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Find cell range based on criteria

Post by HansV »

The rest returns #N/A in this workbook since there are no data for those months. They should return correct results once there are data for all months.
Best wishes,
Hans

User avatar
Sundog
5StarLounger
Posts: 704
Joined: 28 Jan 2010, 22:47
Location: Alien Country (Roswell NM)

Re: Find cell range based on criteria

Post by Sundog »

I copied P2:P4 to one row short of the bottom of my data, and copied & pasted Q4:T4 to Q5:T5. Results there are 0, #N/A, #N/A. #N/A. What should I have done instead?
Sundog

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Find cell range based on criteria

Post by HansV »

Huh? That makes no sense.
Your data in column A cover only March, April and May of 2018. That's why you only see meaningful results for those months in columns Q to T.

As son as I enter some data for June of 2018 in columns A to E and fill down the average formula in column I, you'll see a result for June in columns R to T:

S2359.png

S2360.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
Sundog
5StarLounger
Posts: 704
Joined: 28 Jan 2010, 22:47
Location: Alien Country (Roswell NM)

Re: Find cell range based on criteria

Post by Sundog »

My error. When I pasted those rows into my original 1828 row workbook, it all worked as expected.
Sundog

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: Find cell range based on criteria

Post by snb »

MS invented Pivottables for this kind of questions
You do not have the required permissions to view the files attached to this post.

User avatar
Sundog
5StarLounger
Posts: 704
Joined: 28 Jan 2010, 22:47
Location: Alien Country (Roswell NM)

Re: Find cell range based on criteria

Post by Sundog »

Thanks for the suggestion, but I don't know how you did it! Please suggest a good free ms excel pivot table course.
Sundog

User avatar
p45cal
2StarLounger
Posts: 142
Joined: 11 Jun 2012, 20:37

Re: Find cell range based on criteria

Post by p45cal »

Power Query offering. If you update the source data table on the left, update the results in the table at cell I3 by right-clicking somewhere in that table and choosing Refresh.
You do not have the required permissions to view the files attached to this post.