Look Who it Was Random video

Excel - Prevent Negative Numbers Using Max or If - Episode 348

763 views · Published 18 November 2009 · 2:27 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Prevent Negative Numbers Using Max or If.

Welcome back to another episode of the MrExcel podcast. In today's video, we will be discussing a common problem faced by many Excel users - how to show only positive numbers and replace negative numbers with zeros. We have a simple dataset with sales reps, dates, quotas, and actual sales. Our manager has asked us to create a formula that will show how much the sales were over quota.

At first glance, you might think that subtracting the quota from the sales would give you the desired result. However, when you show this to your manager, they might say that they only want to see zeros instead of negative numbers. Well, there are two ways to solve this problem. The first one is by using the IF function, which requires three arguments. The first argument is a logical test, where we check if the sales are greater than the quota. If that's true, we subtract the quota from the sales. If not, we put a zero. This formula can be copied down to all the cells, and it will only show positive numbers.

The second way to solve this problem is by using the MAX function. This function is perfect for math gurus as it eliminates the need for an IF statement. We simply use the MAX function and input the calculation of sales minus quota, along with the number zero. The MAX function will compare both calculations and return the larger number, which in this case, will always be a positive number. This method is much faster than using the IF statement and can be used interchangeably depending on your preference.

In conclusion, whether you prefer the traditional IF function or the faster MAX function, both can be used to solve the problem of showing only positive numbers and replacing negative numbers with zeros. I hope you found this video helpful. Don't forget to subscribe to our channel for more Excel tips and tricks. Thanks for watching, and we'll see you in the next podcast from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Introduction to the topic 
(00:17) Description of the dataset 
(00:32) Two ways to solve the problem 
(00:48) Explanation of the IF function 
(01:14) Use of the MAX function 
(01:56) Comparison of the two methods 
(02:08) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Comparing the IF function and the MAX function in Excel
Excel formula for calculating sales over quota
Excel tips for handling negative numbers in sales calculations
Excel tutorial for beginners: calculating sales over quota
How to calculate sales over quota in Excel
MrExcel podcast episode on sales calculations and formulas
Quick and efficient method for calculating sales over quota in Excel
Simplifying sales calculations using the MAX function
Solving sales over quota problem with the IF function
Understanding the logical test in the IF function
Using the MAX function to remove negative numbers in Excel
YouTube video tutorial on using the IF function in Excel


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152762/

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!

More from this channel