In a table, what I have is a date column and an amount column. How to get week wise max values from the amount column from this table in Google Sheets?
If you are well aware of the available functions in Google Docs Sheets, for this purpose, you will try to use Query.
No doubt, you can get week wise max values in Google Sheets using Query. But there are other methods too.
If you want the result in two columns, like in a week number column and max amount column, you can use Query.
I have demonstrated this in cell range E1:F4 on the image below.
The above Query excludes weekends. You can include the weekends using the tips given in the latter part of this tutorial.
If you want the result against the source table in a single column, as you can see in column C on the image above, I have another formula.
The second solution returns the max values against the first dates in weeks.
Further, the formula excludes weekends in the max value calculation. Similar to in Query, if you want, you can include weekends too. That part is also included in this tutorial.
Note:-
In my formulas, I have considered Saturday and Sunday as the weekends. If the weekends are different in your business, then you can change that.
Modify the ‘type’ value in the Weekday as well as Weeknum functions in the formulas. I have used type 2 in those functions.
Related: How to Utilize Google Sheets Date Functions.
Let’s go to the formulas to get week wise max values as above in Google Sheets.
Get Week Wise Max Values Using the Query Function (Solution 1)
I am going to code the solution # 1 (please see the image above) formula here.
To get week wise max value using Query in Google Sheets, we should group the week number column.
Our above table in A1:B doesn’t have a week number column. Further, Query has no Weeknum function in it.
We can solve it using the Weeknum date function as below.
We can use {weeknum(A2:A,2),A2:B} as the Query data instead of A2:B.
Here I have used the ‘type’ 2 in Weeknum. It means Day week begins on Monday (1) and Day week ends on Sunday (7). The week numbers will be based on it.
Now we have three columns in the table, and they are week number column, date column, and amount column.
Now, here is the Query formula that returns week wise max values in the range E2:F4. Insert the following Query formula in cell E2.
=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)''"))If you don’t want to exclude weekends in the week wise max value calculation, remove (not(dayofweek(Col2))=1 and not(dayofweek(Col2))=7) and part from the formula.
Note:- In Query Day week begins on Sunday (1) and Day week ends on Saturday (7).
See the formula live in 'With Weekends'!E2 in my shared sheet (find the link at the last part).
Assign Week Wise Max Values Against Week Start Dates (Solution 2)
It is the second solution, and some of you may like how the solution # 2 formula returns the result.
It is also not a complicated formula. But it requires step by step instructions to make you understand the formula. Here are the steps.
I have categorized the steps under two sub-categories. They are;
- Week Wise Max Amount Steps.
- Vlookup Steps to Assign Max Values to the Table.
Here also, I am excluding weekends. To include weekends, I will, later on, explain what changes you should make in the formula.
1. Week Wise Max Amount Steps
The following steps will help us find the week wise max values from the amount column B2:B in Google Sheets.
1. Use the Weeknum function similar to in Query to add a week number column to the existing table in A2:B. Then filter out the weekends using Filter.
=FILTER({weeknum(A2:A,2),A2:B},weekday(A2:A,2)<=5)
2. Now let’s sort the formula # 1 result. We aim to bring the max values to the top.
To do that, we will sort 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)3. Let’s make unique the step # 2 result using the SORTN function. The Unique won’t work here because we want to unique a particular (week number) column.
=sortn(sort(FILTER({weeknum(A2:A,2),A2:B},weekday(A2:A,2)<=5),1,1,3,0),9^9,2,1,1)
We can use the above combination to get the week wise max values in Google Sheets.
In the formula result, the first column contains the week numbers, the third column contains the max amounts in those weeks, and the second column contains the corresponding dates.
But for our purpose, we only require the third column. In the next step, we can extract that.
4. Use the Index function to extract the Max amount column (offset other columns). The generic formula for the same is index(formula_3,0,3).
=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)Keep this formula in a distant column to use later.
2. Vlookup Steps to Assign Max Values to the Table
We have the week wise max values in our hand. Now we want to assign those values against the first dates at every start of the week. For that, we can use Vlookup.
But to use Vlookup, first, we have to extract the first dates from every week. Then form a table using the above result. I’ll explain it.
1. Filter out weekends from the date column B and add week number column as the first column.
=filter({WEEKNUM(A2:A,2),A2:A},weekday(A2:A,2)<=5)2. Sort the date column in ascending order.
=sort(filter({WEEKNUM(A2:A,2),A2:A},weekday(A2:A,2)<=5),2,1)3. 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)
4. 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)This way, we can extract the first date of every week from the date column A in Google Sheets.
5. Finally, combine the 4th step formula under “Week Wise Max Amount Steps” with the just above formula.
={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)}
Note:- After combining, you may notice the changes in the date column. The dates are now in date value format. It’s not an issue.
In cell C2, use the below Vlookup to assign max values week wise in Google Sheets.
=ArrayFormula(IFNA(vlookup(A2:A,F2:G4,2,0)))Change F2:G4 with the corresponding formula in F2.
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)))How Do I Include Weekends?
To include weekends in the above week wise max value formula, you should replace the filter formulas.
There are two filter formulas in the above final formula.
Replace;
FILTER({WEEKNUM(A2:A,2),A2:A},weekday(A2:A,2)<=5)with
FILTER({WEEKNUM(A2:A,2),A2:A},len(A2:A))Then replace;
FILTER({weeknum(A2:A,2),A2:B},weekday(A2:A,2)<=5)with
FILTER({weeknum(A2:A,2),A2:B},len(A2:A))That’s all. Thanks for the stay, enjoy!
