WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » MAX Excel Function

MAX Excel Function

MAX in Excel

Excel MAX function is categorized under statistical functions in Microsoft Excel. The Excel MAX Formula is used to find out the maximum value from a given set of data/ array. MAX function in Excel returns the highest value from a given set of numeric values.

Excel MAX formula will count numbers but ignore empty cells, text, the logical values TRUE and FALSE, and text values.

Excel MAX formula can be used to calculate the highest salary of the employee, the fastest time/score, the highest expense or revenue amount, etc.

MAX Formula in Excel

Below is the formula for MAX in Excel.

MAX Formula in Excel

MAX formula in excel has at least one compulsory parameter, i.e., number1, and the rest of subsequent numbers are optional.

Compulsory Parameter:

  • number1: it is the required number.

Optional Parameter:

  • [number2]:  Rest subsequent numbers are optional.

How to Use MAX Function in Excel?

MAX function in excel is very simple and easy to use. Let us understand the working of the MAX Function by some examples. MAX formula in Excel can be used as a worksheet function and as a VBA function.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course
You can download this MAX Function Excel Template here – MAX Function Excel Template

MAX in Excel Example #1

In this example, we have a student database with their score details. Now we need to find out the max score from these students.

Here apply the MAX formula in Excel =MAX(C4:C19)

MAX Function in Excel - Example 1

it will return you the max score from the given list of scores, as shown in the below table.

MAX Function in Excel - Example 1-1

MAX in Excel Example #2

In this example, we have student details with their score, but here some students did not have any score.

Now apply the MAX formula in Excel here =MAX(G4:G19)

MAX Function in Excel - Example 2

MAX Function ignores the empty cells and then calculates the MAX score from the given data, as shown in the below table.

MAX Function in Excel - Example 2-1

MAX in Excel Example #3

Suppose we have student details with their score, but some of the student’s score values are Boolean.

apply the MAX formula in Excel here =MAX(J4:J19)

MAX Function in Excel - Example 3

MAX function in Excel ignores these Boolean values cells and then calculates the MAX score from the given data, as shown in the below table.

MAX Function in Excel - Example 3-1

MAX in Excel Example #4

Suppose we have a list of names, and we have to calculate the name with maximum length.

Here we have to apply the LEN function to calculate the length of the name.

MAX Function in Excel - Example 4

Apply the MAX formula in Excel to find out the name with maximum length.

MAX Function in Excel - Example 4-1

MAX in Excel Example #5

MAX formula can be used to find the max date from the given set of dates and maximum time from the given time and can be used to find the maximum currency from the given data, as shown in the below table.

MAX Function in Excel - Example 5

MAX Function in Excel VBA

MAX Function in Excel can be used as a VBA function.

Sub Maxcal()

Dim Ans As Integer //declare the Ans as integer

Ans = Applicaltion.WorksheetFunction.Max(Range(“A1:B5”))  // Apply max function on range A1 to B5

MsgBox Ans //Display the max value in the Message box.

End Sub

Things to Remember About MAX Function in Excel

  • MAX function through #VALUE! Error if any of the supplied values are non-numeric.
  • MAX function on Excel will count numbers but ignore empty cells, text, the logical values TRUE and FALSE, and text values.
  • If the MAX function does not have any arguments, then it will return the 0 as output.

MAX Excel Function Video

Recommended Articles

This has been a guide to MAX in Excel. Here we discuss the MAX Formula in excel and how to use the MAX Excel function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • MEDIAN Formula in Excel
  • COUNTIF Formula in Excel
  • Median Formula
  • Max IF Formula in Excel
  • VLOOKUP on Different Sheets
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download MAX Function Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More