Identify positive and negative numbers in excel

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%.

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

B7>0.

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).

Identify positive and negative numbers

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:

B7<0.

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.