Conditional formatting in Pivot table not working propoerly

[b_garside]

I have a Pivot table that displays bandwidth as a percentage 0-1. I have bands of colors 5 of them based on .2 intervals.

For whatever reason my formula is not treating the colors equally. If I use a single column straight table it works fine.

In some cases a value done not get any color.

Unfortunately I can not upload a QVW on this one since its a customer.

Bandwitdht ranges usually in increments of 1.6 but once summed up you get larger values but the max rang is 95 on any given hour.

vDayScale = 30.42

MaxBandWidthRange = 95

eBandWidth = (Sum(BandWidth) / vDayScale) / MaxBandWidthRange

If( $(eBandWidth) >= 1 , RGB(0,110,255) ,
If( $(eBandWidth) >= .8 and $(eBandWidth) <= .99 , RGB(40,140,255) ,
If( $(eBandWidth) >= .60 and $(eBandWidth) <= .79 , RGB(75,155,255) ,
If( $(eBandWidth) >= .40 and $(eBandWidth) <= .59 , RGB(110,175,255) ,
If( $(eBandWidth) >= .20 and $(eBandWidth) <= .39 , RGB(145,195,255) ,
If( $(eBandWidth) >= .01 and $(eBandWidth) <= .19 , RGB(221,240,255)
      )  )  )  )  )  )

[swuehl]

Could it be a rounding issue?

I mean, if sum(BandWidth) is for example .194 or .196, it may be shown as 0.19 resp. 0.20, but your background color expression seems not to cover these values.

[b_garside]

I was starting to think the same and still may be one of the issues.

But when I used the ColorMix wizard It would only evaluate a Single column at a time so you would have dark colors with high value next a low.

[b_garside]

How would i round it ? use Round function or Ceil, Floor. 

[swuehl]

No, I was thinking of something like

If( $(eBandWidth) >= 1 , RGB(0,110,255) ,
If( $(eBandWidth) >= .8 and $(eBandWidth) < 1 , RGB(40,140,255) ,
If( $(eBandWidth) >= .60 and $(eBandWidth) < .8 , RGB(75,155,255) ,
If( $(eBandWidth) >= .40 and $(eBandWidth) < .6 , RGB(110,175,255) ,
If( $(eBandWidth) >= .20 and $(eBandWidth) < .4 , RGB(145,195,255) ,
If( $(eBandWidth) >= .01 and $(eBandWidth) < .2 , RGB(221,240,255)
      )  )  )  )  )  )

[b_garside]

Still left some uncolored while smaller values disappeared since its rounding up.

Round((Sum(BandWidth / vDayScale)) / MaxBandWidth, 0.01 )

[b_garside]

Ok yeah. That really helped. Makes sense. Don't leave any small gaps.

[b_garside]

So it seemed too good to be true. See below

Percent values...correlate no colors in some cells.

[swuehl]

Where do the large values suddenly come from? Have you removed the division by your constants?

I thought it is intentional, but your lower limit for a color is still >= 0.01?

[b_garside]

It must be since the values are too low? I'll let it be.