Conditional formatting when cell is blank

Glenno557

New Member
Joined
Sep 28, 2020
Messages
14
Office Version
  1. 2013
Platform
  1. Windows
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?
1644829807693.png
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Forum statistics

Threads
1,214,652
Messages
6,120,746
Members
448,989
Latest member
mariah3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top