MrExcel's Learn Excel #348 - Max or If

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Nov 18, 2009.
Calculate Sales over Quota using either the IF or MAX functions. Episode 348 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
maxresdefault.jpg


Transcript of the video:
Welcome back to the MrExcel podcast, I'm Bill Jelen.
Today we have a pretty simple dataset, we have sales rep, the date, what their quota in sales were for the day, and their actual sales.
And our manager asks us to build a formula that will show how much the sales were over quota.
Now your first attempt at this might just be to take Sales-Quota, and you come up with a number, copy it down, but when you show this to your manager he says “No, no, that's not what I want, I don't want to see the negatives if they didn't meet their quota, I just want a zero there!” Well, there's two ways to go about this.
The first way is the IF function, the IF function requires three arguments.
The first argument is a logical test, and in that case the test would be: are the Sales in D greater than the Quota in C, put a comma, and then put the calculation to use.
If that's TRUE, we want to take Sales-Quota, otherwise, with another comma, put a 0, and Enter that, copy it down.
Now all of the negatives are gone, we only see the numbers that are greater than 0.
The IF function is the traditional way to solve this, but if you're a math guru, you'll recognize that the MAX function can also be used.
When we had the original formula D2-C2, some of the numbers were greater than 0 and some were negative, and we want all the negatives to go away.
So if I use the MAX function, =MAX, and I put in that calculation D2-C2, and also put in the number 0, it will look at both of those calculations.
If the number is positive I get the positive number, if the number is negative, well, 0 is larger than if, so the MAX returns that answer.
When you get right down to it, entering the MAX is much faster than entering the IF statement, and so, if you ever have this kind of a problem, you can use the MAX function, or the IF function, whichever you prefer.
Hey, thanks for stopping by, we'll see you next time for another podcast from MrExcel!
 

Forum statistics

Threads
1,223,700
Messages
6,173,909
Members
452,536
Latest member
Chiz511

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top