We will figure out how could we display negative values in a field in excel as “0”.
For some reasons we don’t want to show the actual negative values in our report or presentation so we show them as “0” or “NA” etc.
Below is the sales data for previous and current year along with the percentage change in the numbers from previous number.
The formula that is used to calculate the percentage is =(B2-A2)/A2, now we will just edit the formula with an if condition to make it display the value as “0” rather than the actual negative values.
=IF((B2-A2)/A2<0,0,(B2-A2)/A2)
We could replace the “0” with “NA” in case we wanted to display “NA” instead of negative values like below.
=IF((B2-A2)/A2<0,”NA”,(B2-A2)/A2)
As you could see, the negative percentages are displayed as 0.
Hope this helped.
