Conditional formatting based on distance from median value - excel 2010

**greyscale** · 07-23-2012, 12:20 AM

Hi,

I have a 2010 excel document that formats cells green if they are 15% above the median value of cells in the same column, and red if they are 15% below. This was working OK until I tried copying the formatting (using format painter) to adjacent cells. When I go into the rules, they are set exactly as per the previous column but for some reason, 1 or 2 cells will just randomly highlight even though they don't meet the conditions.

unfortunately, I cannot load my live report due to privacy. I have therefore attempted to recreate in a sample attached. You can see that C5 is highlighted but C15 is not, even though they have the same value. cell F7 is highlighted even though it is not 15% above the median.

Also, the blank cells are highlighting both red (G5) and green (H15) even though in my live report they appear as unformatted.

This has me stumped. Can anyone point out where I am going wrong? I do not wish to use absolute values in the median calculation as I wish to copy the formatting each week to a new range.

Thanks in advance

**mubashir aziz** · 07-23-2012, 01:36 AM

Actually in Column B you used wrong formulas =MEDIAN(B3:B15)+0.15 which will not be implemented on all cells so changed this with '=MEDIAN($B$3:$B$15)+0.15 and same with last formula as =MEDIAN($B$3:$B$15)-0.15
For blank cells use this formula after selecting whole range '=ISBLANK(B3)

I am attaching sheet as well for your better understanding ..

**greyscale** · 07-23-2012, 01:48 AM

Thanks so much for your reply and your sample data. I don't understand how it is working. If you go into EDIT RULE, everything seems to be looking at the median for column C? if you don't mind me asking, how does this work?

**mubashir aziz** · 07-23-2012, 02:07 AM

As I mentioned above that in Edit Rule your formula is not using absolute values (=MEDIAN(B3:B15)+0.15), I changed it to =MEDIAN($B$3:$B$15)+0.15 and applied other columns. In Excel 2010 when i click edit rules then the same values are appearing so i changed it to absolute.

**greyscale** · 07-23-2012, 02:11 AM

But when I go into EDIT RULES in the document you sent me, all of the columns are referencing MEDIAN($C$3:$C$15). What am I not getting?

**mubashir aziz** · 07-23-2012, 02:14 AM

There are two sheet one is Sheet1 and other is My Soution so i think you are checking my sheet?

**greyscale** · 07-23-2012, 08:01 PM

I am looking at your 'my solution' worksheet. If you highlight cells B3:B15 go to conditional formatting, select manage rules, and then select EDIT RULE for green formatting for >median, it is referencing $C$3:$C$15. Same for all other columns. They all reference column C. I don't understand how this works. When I try to apply this to my live worksheet it is still formatting the wrong cells.

**mubashir aziz** · 07-24-2012, 01:17 AM

Sorry my mistake and check this sheet and hope your problem ll be solved ....
Now Formulas are
=ISBLANK(B3)
=MEDIAN(B$3:B$15)+0.15
=MEDIAN(B$3:B$15)-0.15

You can use format painter to copy above conditoni on all the columns.

**greyscale** · 07-24-2012, 09:43 PM

Thank you! When I initially tried using absolutes it would not update with new cells references when using format painter. I don't know why as mine appeared to be set up the same as yours for the median calculation. Not sure what was originally happening but thanks for the clarification. I will be sure to use absolutes from now on.