Locked lesson.

Upgrade

About this lesson

Illustration and issues with MAX and MIN.

Exercise files

Download this lesson’s related exercise files.

MAX and MIN.xlsx
10.2 KB MAX and MIN - Solution.xlsx
10.2 KB

Quick reference

MAX and MIN

Discover how to use MAX & MIN functions in a formula.

When to use

These functions take the maximum and the minimum of their references respectively.

Instructions

Example

  • The formula =MAX(B5:B13) will return the maximum value in cells B5:B13 = 15
  • The formula =MIN(B5:B13) will return the minimum value in cells B5:B13 = -8

  • Some useful hints when using these functions:
    • If a cell is ‘blank’ then the MAX & MIN functions won’t treat the blank cell as a zero
    • The MAX & MIN functions will treat ‘blanks’ in formula as a zero
    • Empty strings are treated as text; MAX & MIN cannot evaluate text strings
Login to download
  • 00:03 I'm afraid I've been lying to you.
  • 00:07 Sorry about that.
  • 00:09 I told you went to look at the dirty dozen, the top 12 functions most
  • 00:13 commonly used in financial modeling from my subjective opinion.
  • 00:18 Not been quite true, if you've done a count back.
  • 00:21 Even excluding VLOOKUP and HLOOKUP, which, please, don't use.
  • 00:24 If you count back, we've actually looked at IF,
  • 00:29 SUM, SUMIF, SUMIFS, SUMPRODUCT, INDEX,
  • 00:34 MATCH, LOOKUP, OFFSET, MOD and EOMONTH.
  • 00:38 That's 11, and now we're going to do MAX and MIN.
  • 00:43 Now, technically you could argue, yeah, that's 13, but quite frankly, MAX and
  • 00:47 MIN are two sides of the same coin, so I'm going to as my 12.
  • 00:51 And yes, I am cheating.
  • 00:53 MAX does exactly what it says on the tin.
  • 00:57 It returns the largest value in a set of values.
  • 01:00 And it will only evaluate numbers, and return the maximum number in that range.
  • 01:05 It's basically an in-built condition.
  • 01:09 Now, numbers and arguments that can be used with the MAX function can be
  • 01:12 anything that returns a number.
  • 01:14 It could be empty cells, logical values, or text representations of such numbers.
  • 01:19 Other functions can be manipulated to return a number as well,
  • 01:22 and therefore be included in the MAX function too.
  • 01:26 And basically it's exactly the same thing for MIN.
  • 01:31 So I can ramble through this slide, but I thought I'll give you a second to read it.
  • 01:36 Shall we go and have a look at some Excel examples to wrap up this section, then?
  • 01:42 You see, MAX and MIN are very, very simple.
  • 01:45 I've already covered them while I was talking about depreciation,
  • 01:48 well MIN, anyway.
  • 01:50 If I go here, =MAX, I can just take the whole range.
  • 01:55 That gives me the maximum value in that range, and it's 15.
  • 02:00 That cell there.
  • 02:01 MIN takes the minimum.
  • 02:05 Now I can actually select the the whole range, but this time I'm going to do one
  • 02:09 at a time, holding the Ctrl button down as I go through.
  • 02:13 See, it automatically puts in the commas in for me.
  • 02:17 Now, you've only got a finite number of arguments you can use.
  • 02:25 But don't worry, we got plenty left.
  • 02:26 You see, it still works, that is the minimum number in that range, too.
  • 02:31 And if I start putting in things like dog in here, or forty as a word.
  • 02:39 It's okay, but if I put =15/0,
  • 02:44 then it does cause a problem.
  • 02:48 MAX and MIN can't cope with errors in ranges.
  • 02:53 Some more things to note.
  • 02:55 This section here, looking at MAXes.
  • 02:59 So here, I've done the maximum of these two cells.
  • 03:04 Notice at the moment that there's nothing in that cell.
  • 03:07 So it's taking the maximum of -139, which is -139.
  • 03:11 But if I put 0 here, it's 0.
  • 03:15 So blank and 0 are not the same thing with MAX and MIN, so be careful.
  • 03:20 You'll remember, when I looked at OFFSET, I actually suppressed the zeroes.
  • 03:24 That was fine, but you can't do that with MAX and MIN.
  • 03:28 If it's actually in the formula =MAX(B6,)
  • 03:33 then it treats blanks in the formula as zeroes.
  • 03:40 Therefore it's not taking -320, it's taking 0.
  • 03:46 But if I put instead the empty string by doing open speech mark, close speech
  • 03:50 marks, well, that's read as text and MAX can't evaluate text strings, so in this
  • 03:55 case, it's hash value, so that's different than putting text in an actual range.
  • 04:02 MIN, this is showing exactly the same thing.
  • 04:05 If I put in 0 here, you see it generates exactly the same problem.
  • 04:11 So just be careful when using MAX and MIN, they're pretty easy to understand.
  • 04:15 Yes, it's the maximum of a set of values, yes it's the minimum of a set of values.
  • 04:19 But it's values. When you start putting text error, things
  • 04:22 in, blank text strings and goodness knows what else, then it can cause problems.
  • 04:27 So be careful when doing this.
  • 04:29 If you're doing scenario analysis on a financial model,
  • 04:32 make sure things don't go blank or they go empty set or whatever, cuz it can
  • 04:36 cause your model to go hash value if you're using MAX and MIN everywhere.
  • 04:42 All right so that's my functions, pretty much, all over and done with.
  • 04:46 It's been a pretty meaty set of sessions, I think you'd agree.
  • 04:51 Let's have one last deck.

Lesson notes are only available for subscribers.

Gift this course