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.
Find cell range based on criteria
-
- 5StarLounger
- Posts: 704
- Joined: 28 Jan 2010, 22:47
- Location: Alien Country (Roswell NM)
Find cell range based on criteria
You do not have the required permissions to view the files attached to this post.
Sundog
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Find cell range based on criteria
See columns P to T in the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 704
- Joined: 28 Jan 2010, 22:47
- Location: Alien Country (Roswell NM)
Re: Find cell range based on criteria
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
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Find cell range based on criteria
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
Hans
-
- 5StarLounger
- Posts: 704
- Joined: 28 Jan 2010, 22:47
- Location: Alien Country (Roswell NM)
Re: Find cell range based on criteria
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
-
- Administrator
- Posts: 78485
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Find cell range based on criteria
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:
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:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 704
- Joined: 28 Jan 2010, 22:47
- Location: Alien Country (Roswell NM)
Re: Find cell range based on criteria
My error. When I pasted those rows into my original 1828 row workbook, it all worked as expected.
Sundog
-
- 4StarLounger
- Posts: 575
- Joined: 14 Nov 2012, 16:06
Re: Find cell range based on criteria
MS invented Pivottables for this kind of questions
You do not have the required permissions to view the files attached to this post.
-
- 5StarLounger
- Posts: 704
- Joined: 28 Jan 2010, 22:47
- Location: Alien Country (Roswell NM)
Re: Find cell range based on criteria
Thanks for the suggestion, but I don't know how you did it! Please suggest a good free ms excel pivot table course.
Sundog
-
- 2StarLounger
- Posts: 147
- Joined: 11 Jun 2012, 20:37
Re: Find cell range based on criteria
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.