Excel formula for Beginners – How to find Max value on given weekday in Excel

Hits: 59

 

 

(Excel examples for Beginners)

In this end-to-end excel example, you will learn – Excel formula for Beginners – How to find Max value on given weekday in Excel.

 

Excel formula for Beginners – How to find Max value on given weekday in Excel

Generic formula

{=MAX(IF(TEXT(dates,"ddd")="Mon",values))}

Summary

To find the maximum value on a given weekday (i.e. Monday, Tuesday, Wednesday, etc.) you can use a simple array formula based on the MAX, IF, and TEXT functions. In the example shown, the formula in cell F5 is:

=MAX(IF(TEXT(dates,"ddd")=F4,values))

Where dates (B5:B15) and values (C5:C15) are named ranges.

Note: this is an array formula and must be entered with Control + Shift + Enter.

Explanation

Working from the inside out, the TEXT function is used to extract a weekday value for each date:

=TEXT(dates,"ddd")

This results in an array like this:

{"Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon"}

which is then compared to the text in F4, “Mon”. The result is another array, which contains only TRUE and FALSE values:

{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}

Note each TRUE corresponds to a Monday. This array is returned inside the IF function as the logical test. It acts as a filter to screen out values on other days of the week. The final result from IF, which is returned directly to the MAX function, looks like this:

=MAX({85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52})

MAX automatically ignores FALSE values and returns the highest remaining value, 94.

With AGGREGATE

For a slighly more geeky formula that doesn’t require control + shift + enter, you can use the AGGREGATE function like this:

=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)

This is the formula used in cell F6 in the example shown. Here we give AGGREGATE 14 for the function argument (LARGE) and 6 for option argument (ignore errors). Then we build a logical expression using the TEXT function to check all dates for Mondays. The result of this operation is is an array of TRUE/FALSE values, which become the denominator of the original values. When used in a math operation, FALSE evaluates as zero, and throws a #DIV/0! error. TRUE evaluates as 1 and returns the original value. The final array of values and errors acts like a filter. AGGREGATE ignores all errors and returns the largest (maximum) of the surviving values.

MAXIFS

The MAXIFS function, available in Excel Office 365, can return a max value using on one or more criteria without the need for an array formula. However, MAXIFS is a ranged-based function, and won’t allow other functions like TEXT to process values in criteria ranges. You could however add a helper column to the data, generate weekday values with TEXT, then use MAXIFS with the helper column as a criteria range.

 


Excel formula for Beginners – How to find Max value on given weekday in Excel

 

 

Sign up to get end-to-end “Learn By Coding” example.


 

 

Introduction to Applied Machine Learning & Data Science for Beginners, Business Analysts, Students, Researchers and Freelancers with Python & R Codes @ Western Australian Center for Applied Machine Learning & Data Science (WACAMLDS) !!!

Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:

Applied Statistics with R for Beginners and Business Professionals

Data Science and Machine Learning Projects in Python: Tabular Data Analytics

Data Science and Machine Learning Projects in R: Tabular Data Analytics

Python Machine Learning & Data Science Recipes: Learn by Coding

R Machine Learning & Data Science Recipes: Learn by Coding

 

There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $19.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!