Comparing values across all columns in pivot table.

[stascher]

Hi,

I've got the following pivot table:

What I'd like to do is...

...for each row, compare the "%of group affected" value for each Group and if the difference between the highest and lowest "% of group affected" is >= 50, set the background color of the highest "%of group affected" in the row to yellow.

I already know how to calculate "%of group affected" and how to identify when a cell contains the highest or lowest value by using hrank().

What I need is an expression I can use when specifying a  cell's background color that will calculate the difference between highest and lowest "%of group affected" in the row.  I tried using the rangemax() and rangemin() with before() and after() functions but I couldn't get them to work.

Thanks,
Steve

[stascher]

well this works, but I wonder if there's a better way....

=rangemax(before(count(distinct subject)/Aggr(NODISTINCT Count(DISTINCT subject), group),0,ColumnNo()),after(count(distinct subject)/Aggr(NODISTINCT Count(DISTINCT subject), group),0,noofcolumns()-columnno()+1))

-rangemin(before(count(distinct subject)/Aggr(NODISTINCT Count(DISTINCT subject), group),0,ColumnNo()),after(count(distinct subject)/Aggr(NODISTINCT Count(DISTINCT subject), group),0,noofcolumns()-columnno()+1))