Negative values problem in macro

[Anonymous]

Hi everyone.

I have some KPIs in which they will be calculated based on the period chosen by the users.

And these KPIs will group into 5 ranges: less than 20%, 20% - 40%,..., more than 80%.

The colour of the text objects (contains different value) will change according to the group.

When there is no negative values, everything is okay.

Problem Example: Range 1 is -100 and value is -99, the colour just remained as white (RGB(255,255,255).

Please help to point out where the problem is.

Range1 = ActiveDocument.Evaluate("Num(Fractile ([$(vKPI)],0),'0000000.000')")

Range2 = ActiveDocument.Evaluate("Num(Fractile ([$(vKPI)],0.2),'0000000.000')")

Range3 = ActiveDocument.Evaluate("Num(Fractile ([$(vKPI)],0.4),'0000000.000')")

Range4 = ActiveDocument.Evaluate("Num(Fractile ([$(vKPI)],0.6),'0000000.000')")

Range5 = ActiveDocument.Evaluate("Num(Fractile ([$(vKPI)],0.8),'0000000.000')")

color = RGB(255,255,255)

If value >= Range1 Then color = RGB(165,0,38)

If value >= Range2 Then color = RGB(244,109,67)

If value >= Range3 Then color = RGB(255,217,16)

If value >= Range4 Then color = RGB(12,112,12)

If value >= Range5 Then color = RGB(8,122,248)

Thank you!

[marcus_sommer]

It seems that you don't query on negative values and therefore your color remained by the defined default-color. Therefore you need to extend this logic or you might avoid it by using: fabs(Fractile ([$(vKPI)],0)) to return the absolute value without positive/negative.

Btw. why using a macro to color a textbox - why not using the color-expressions within the textbox?

- Marcus

[Anonymous]

Hi Marcus.

I cant make them into absolute values because the range may be from negative to positive.

For example: Group 1: -100 to -50

                      Group 2: -50 to 0

                      Group 3: 0 to 50

                      Group 4: 50 to 100

                      Group 5: 100 to 150

So when the value is negative, the color won't change.

I have quite a number of textbox in a sheet, when I tried to use color-expressions in the beginning, I don't know why it won't change correctly. So I just use macro instead.

[marcus_sommer]

Like above mentioned I think the reason is that some values of your KPI are outside of your range-query and I would also not use a macro. Here a short example how I would probably do it:

[generating dummy data per]:

load rand() * 200 * if(mod(rowno(), 2) = 0, -1, 1) as KPI autogenerate 500;

two pivot-tables:

color-expression (here within the attribut-expressions):

pick(match(-1,

     KPI < fractile(total KPI, 0),

     KPI < fractile(total KPI, 0.2),

     KPI < fractile(total KPI, 0.4),

     KPI < fractile(total KPI, 0.6),

     KPI < fractile(total KPI, 0.8),

     KPI < fractile(total KPI, 1)),

    RGB(165,0,38),

     RGB(244,109,67),

     RGB(255,217,16),

     RGB(12,112,12),

     RGB(12,112,12),

     yellow())

- Marcus