Do you want to extract week-wise maximum values from a table in Google Sheets? Well, you’re in luck! I’ll walk you through different methods to achieve this using built-in functions. So, let’s dive in!
Using the Query Function (Solution 1)
First, let’s explore the Query function to obtain week-wise maximum values. We’ll begin by grouping the week numbers. But wait, our table doesn’t have a week number column. No worries! We can use the Weeknum date function to solve this.
Here’s the formula for extracting week-wise maximum values using Query:
=ArrayFormula(Query({weeknum(A2:A,2),A2:B},"Select Col1,max(Col3) where (not(dayofweek(Col2))=1 and not(dayofweek(Col2))=7) and Col2 is not null group by Col1 label max(Col3)''"))This formula excludes weekends, but if you want to include them, simply remove the (not(dayofweek(Col2))=1 and not(dayofweek(Col2))=7) and part from the formula.
Assigning Week Wise Max Values Against Week Start Dates (Solution 2)
If you prefer another approach, we have a solution for you too! In this method, we’ll extract the first dates of each week and assign the corresponding maximum values.
Let’s break down the steps:
1. Week Wise Max Amount Steps
To find week-wise maximum values, follow these steps:
- Use the Weeknum function to add a week number column to your existing table and filter out weekends using the Filter function.
=FILTER({weeknum(A2:A,2),A2:B},weekday(A2:A,2)<=5)- Sort the above result by the week number column in ascending order and the amount column in descending order.
=SORT(FILTER({weeknum(A2:A,2),A2:B},weekday(A2:A,2)<=5),1,1,3,0)- Use the SORTN function to make the result unique.
=SORTN(SORT(FILTER({weeknum(A2:A,2),A2:B},weekday(A2:A,2)<=5),1,1,3,0),9^9,2,1,1)2. Vlookup Steps to Assign Max Values to the Table
Now, let’s assign the maximum values against the first dates of each week using Vlookup.
Follow these steps:
- Filter out weekends from the date column and add the week number column.
=FILTER({WEEKNUM(A2:A,2),A2:A},weekday(A2:A,2)<=5)- Sort the date column in ascending order.
=SORT(FILTER({WEEKNUM(A2:A,2),A2:A},weekday(A2:A,2)<=5),2,1)- Remove duplicate week numbers.
=SORTN(SORT(FILTER({WEEKNUM(A2:A,2),A2:A},weekday(A2:A,2)<=5),2,1),9^9,2,1,1)- Remove the week number column.
=INDEX(SORTN(SORT(FILTER({WEEKNUM(A2:A,2),A2:A},weekday(A2:A,2)<=5),2,1),9^9,2,1,1),0,2)Lastly, combine the fourth step formula with the previous formula under “Week Wise Max Amount Steps” using curly braces.
Here’s the final formula:
=ArrayFormula(IFNA(vlookup(A2:A,{index(sortn(sort(filter({WEEKNUM(A2:A,2),A2:A},weekday(A2:A,2)<=5),2,1),9^9,2,1,1),0,2),index(sortn(sort(FILTER({weeknum(A2:A,2),A2:B},weekday(A2:A,2)<=5),1,1,3,0),9^9,2,1,1),0,3)},2,0)))And there you have it! Week-wise maximum values assigned against the first dates of each week.
Feel free to customize the formulas according to your specific needs. If you want to include weekends, replace the filter formulas accordingly.
Now go ahead and give it a try! Happy exploring!
Learn more about Google Sheets Date Functions at Crawlan.com
