A common requirement in Excel is to identify positive and negative numbers. This is usually to apply a different calculation to it e.g. a positive (credit) bank balance earns say 5% but a negative bank balance (debit or overdraft) incurs an interest charge of 10%.
Table of contents
YouTube Identify positive and negative numbers
Using IF to find positive and negative numbers
Although there are a number of ways to do it with special functions, the easiest way is to use an IF function. The question is how do we tell Excel to look for a positive or negative number. The logical operator generally needs to use a bigger than, equals to or less than.
A positive number is defined as any number bigger than zero. So in Excel terms a positive number is >0. As shown below the logical test for the bank balance in cell B7 would be
If this is TRUE, then we know it is a positive number. If it is not TRUE it must be a negative number (or a zero but this won’t have any impact in this case).
If you prefer to look for a negative number then the logical test of the IF statement would change. In this case the test would be:
If this test is TRUE then we have a negative number. If it is FALSE we have a positive number.
To learn more about the IF function, have a look at our free Excel Fundamentals Course.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.