# Calculate percentage changes for containing negative numbers and zero

How to calculate percentage change for negative numbers in Excel?

### For calculating the % change which doesn't contain a negative or zero value we can use one of these two formulas

1. (new value – old value) / old value

2. (new value / old value) – 1

### Why is a custom function required?

1. When the data set has 0 values then the above formula will run into #DIV error.

2. When the data set has negative values or to calculate negative growth

### What do we know as a basic math

• 0 can't be divided by any number

• A number can't be divided by 0

• Changes from 0 to any positive number will be a 100% increase

• Changes from a positive value to 0 will be a 100% decrease

# EXCEL FORMULA AND EXAMPLE

Generic formula - suitable for a data set that doesn't contain 0 or negative values

1. =(B3-A3)/A3

2. =B3/A3-1

• A3 = old value

• B3 = new value

Divided by absolute old value - suitable for a data set containing positive and negative values

• =(B3-A3)/ABS(A3)

• A3 = old value

• B3 = new value

Custom formula

• =IF(AND(B3=0,A3=0),0,IF(AND(B3>0,A3=0),1,(B3-A3)/ABS(A3)))

• A3 = old value

• B3 = new value

💡 View the Google Sheets file used in this exercise here

# Calculate percentage changes for containing zero or negative values in Google Sheets

How to calculate percentage change for negative numbers in Google Sheets?

# GOOGLE SHEETS FORMULA AND EXAMPLE

Generic formula - suitable for a data set that doesn't contain 0 or negative values

1. =(B3-A3)/A3

2. =B3/A3-1

• A3 = old value

• B3 = new value

Divided by absolute old value - suitable for a data set containing positive and negative values

• =(B3-A3)/ABS(A3)

• A3 = old value

• B3 = new value

Custom formula

• =IF(AND(B3=0,A3=0),0,IF(AND(B3>0,A3=0),1,(B3-A3)/ABS(A3)))

• A3 = old value

• B3 = new value

💡 View the Google Sheets file used in this exercise here