Conditional formatting cells by percentages

Question

I am using office 2016.&nbsp;&nbsp;I have a document that I update daily. I&nbsp;am trying to have the top cell format red if there is a 20% discrepancy between the numbers.&nbsp;&nbsp;I add a new line every day, and copy/paste the numbers in (the blue numbers are the results of a formula)&nbsp;I want to just add my line, put in the numbers and have it check the field below with the percentage and highlight if it's higher or lower than normal.&nbsp;

erol sinan zorlu · Answer

write this formula to Conditonal Formating->New Rule-->Use a formula to determine which cells to format

=ABS/($B2-$H2)/$B2)>0,2

and below line to Applies to:

=$A$1:$M$1

if you want to have the same rule applied in more rows change the row number in the last formula.ç

joshua armstrong · Answer

Got this error listed. I changed the corresponding cells accordingly.&nbsp;&nbsp;&nbsp;

erol sinan zorlu · Answer

Sorry there was a syntax error. It should have been&nbsp;=ABS(($B2-$H2)/$B2)&gt;0,2

joshua armstrong · Answer

Thanks. Can you explain how the formula works so i can see how the calculation is processed?&nbsp;&nbsp;I'm still getting the same error code.&nbsp;&nbsp;If it helps to clarify, with the last screenshot I loaded. I wanted the value of B4 and B5 compared, and if B4 is more or less than 20% of B5, it would highlight.&nbsp;

erol sinan zorlu · Answer

First thing youı have to check is 0,2. Try changing it to 0.2&nbsp;How formula works. This formula calculates the difference between $B2 and $H2 and&nbsp;divides the result with $B2 to find percentage and incase this value is negative ABS will convert it to a positive number. The trick here is "$" sign before column name and no signt before row number. This will cause excel to apply same formula to all the cell the formatting is applied with column names to be same in every row but take the row number from the actual row where the condition is checked. so basically the formula updates itself to&nbsp;=ABS(($B3-$H3)/$B3)&gt;0.2;&nbsp;=ABS(($B4-$H4)/$B4)&gt;0.2 and so on.

Forum Discussion

Conditional formatting cells by percentages

Share

Resources