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

Hits: 136

 

(Excel examples for Beginners)

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

 

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

Generic formula
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Summary

To find the maximum value in a given month, you can use the MAXIFS function or one of the other alternatives below. In the example shown, the formula in G5 is:

=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))

Where sales (D5:D15), dates (B5:B15) and values (C5:C15) are named ranges.

Explanation

The MAXIFS function can find the maximum value in a range based on one or more criteria. In the example shown, we are using MAXIFS to find the max sales value based in a given month by “bracketing” dates between the first day of the month and the last day of the month. The first criteria checks if dates are greater than or equal to the first of the month:

dates,">="&G4,dates // first of month

Note: we are assuming date in G4 is a “first of month” date.

The second criteria checks if dates are less than or equal to the last of the month, calculated with the EOMONTH function:

dates,"<="&EOMONTH(G4,0) // last of month

When both criteria return TRUE, the date is in the given month, and MAXIFS returns the max of value of dates that meet criteria.

Alternative formulas

If your version of Excel doesn’t include the MAXIFS function, there are other options. One option is a simple array formula based on the MAX and IF functions:

{=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales))}

Note: This is an array formula and must be entered with with control + shift + enter.

Another option is to use the AGGREGATE function like this:

=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)

Both options above use a simpler method of testing dates based on the TEXT function which uses a number format to extract the month and year from a date. The TEXT function can’t be used like this inside the MAXIFS function, because MAXIFS requires a range. These alternative approaches are explained in more detail here.

The max client

To retrieve the client associated with the max value in a given month, the the formula in G6 is:

{=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0))}

This is an array formula and must be entered with with control + shift + enter.

The idea here is that we already know the max value in a month (G5) and we can use that value as a “key” to lookup the client. Note we must repeat the logic used to isolate values in the given month, in order to avoid a false match in a different month.

Like the alternatives mentioned above, this formulas also uses a simplified test for dates based on the TEXT function.

For an all-in-one formula, replace G5 with one of the formula options explained above for determining the max value in a given month.

 


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

 

Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

95% Discount on “Projects & Recipes, tutorials, ebooks”

Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)


Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.

Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners

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!