The max function returns the highest value provided to it. You start by typing in equals max and then open brackets, and you then need to provide numbers to it. Those numbers, or values could be hard coded numbers typed into the formula. They could be named cells or links to cells where those cells have numbers in them. Importantly, what the max function does, it ignores empty cells and zeroes.

So how could we use this? Well, you could be looking to find the highest sales figure for the year. Maybe you're trying to hone in on a particular salesperson. It's also good for providing a lower limit. For example, maybe you've got a list of sales figures, but one of them is a negative. You don't want to include that negative. Maybe it's a refund. You don't wanna include refunds in your sales list. So you would take the maximum of all of your sales, and zero meaning zero would become a lower limit, which can't be gone past. Let's look at some examples in Excel.

If we scroll over to column H, you'll see we have got some sales amounts here. What I'd like to do is I'd like to, underneath these sales amounts, find the maximum and give the salesperson who made that sale a bonus. So in H16, I'll start by typing in equals max.

I'm going to use my keyboard to select the cells. So I press the up arrow, hold down shift, and then the up arrow again until I get to H5 close brackets and press enter. And I can see that the maximum sale amount is 250.3. If I go and find that number, here it is in H5 and I scroll to the left. I can see the name of the person that made that sale and they can get the bonus.

Now, something I notice in the sale amount is there is a negative in there. There is a refund, and I'd like to redo the numbers in the sale amount column and exclude that refund. So I'm going to go into column J and press equals max. Open brackets. Then I'm going to go and select the sale amount in H5 press comma. You may have to use a semicolon depending on which country you're in. And then I'm going to type in zero, I close and press enter. And at the moment, if I want the maximum of 250.3 and zero, it's going to give me 250.3. I'm going to copy this down by holding in shift and the down arrow and then pressing Control D.

So I can see in the first few, it just returns the sale amount. But when I get down to J10, I get a zero, and that is because this is linking to H 10. H 10 is negative, and the maximum of negative 22.1 and zero is zero. So I have excluded any refunds from my list of sale amounts.