# 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.

### 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

#### Free Machine Learning & Data Science Coding Tutorials in Python & R for Beginners. Subscribe @ Western Australian Center for Applied Machine Learning & Data Science.

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!