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
(new value – old value) / old value
(new value / old value) – 1
Why is a custom function required?
When the data set has 0 values then the above formula will run into #DIV error.
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
Calculate percentage changes for containing zero or negative values in the Excel
EXCEL FORMULA AND EXAMPLE
Generic formula - suitable for a data set that doesn't contain 0 or negative values
=(B3-A3)/A3
=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
=(B3-A3)/A3
=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
- Change the negative number to zero
- Change negative to positive numbers
- Change positive numbers to negative
- Remove negative sign from numbers
- Sum by month
- SUMIF cells if contains part of a text string
- Sum total sales based on quantity & price
- Combine date and time
- Convert 1-12 to month name
- Dynamic current date and time
- Count blank cell
- Count cell between two valuesÂ
- Combine two or more cell
- Extract left before first space
- Extract the nth words in a text string
- Flip the first and last name
- SUMIF formulas and example
- SUMIFS formulas and example
- COUNTIF formulas and example
- IF formulas and example
- TEXT formulas and example
- CONVERT formulas and example
- Calculate percentage changes for containing zero or negative values
- Convert date to text
- Convert time to text
- Convert weekday string to number
- Combine two or more cells with a line break
- Combine two or more cells and transpose
- Count cells over 10 characters
- Count cells that are not blank
- Count if cells contain a specific text
- Count cells that begin with a specific text
- COUNTIF based on multiple criteria