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

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

Western Australian Center for Applied Machine Learning & Data Science – Membership

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