Hi I am having trouble with this conditional formatting issue:
Percentage difference between cells =(A2-B2)/B2
A. Cell A2 = 1, cell B2 = 0 calculating a % difference returns the #DIV/0 error, so now my formula says =IFERROR((A2-B2)/B2),1) - now my error shows as 1 which is fine for my purposes.
Now I decide that I want to conditionally format the % difference cells to be green in colour if 0 difference
B. Cell A3 = 1, cell B3 = 1 Works fine - result 0 difference
C. Cell A4 = 0, cell B4 = 0 Does not work - result 1 difference as the value is being treated as an error
D, Cell A5 = Null, cell B5 = Null Does not work as the null value is being treated as an error
Is there another argument that I can add to the formula, so that if the cells that are "0" or Blank are treated as having numbers in them, so that 0 - 0 returns as 0 difference? Not sure if I've made myself clear but would appreciate any assistance if possible?
Percentage difference between cells =(A2-B2)/B2
A. Cell A2 = 1, cell B2 = 0 calculating a % difference returns the #DIV/0 error, so now my formula says =IFERROR((A2-B2)/B2),1) - now my error shows as 1 which is fine for my purposes.
Now I decide that I want to conditionally format the % difference cells to be green in colour if 0 difference
B. Cell A3 = 1, cell B3 = 1 Works fine - result 0 difference
C. Cell A4 = 0, cell B4 = 0 Does not work - result 1 difference as the value is being treated as an error
D, Cell A5 = Null, cell B5 = Null Does not work as the null value is being treated as an error
Is there another argument that I can add to the formula, so that if the cells that are "0" or Blank are treated as having numbers in them, so that 0 - 0 returns as 0 difference? Not sure if I've made myself clear but would appreciate any assistance if possible?