I'm trying to conditionally format a cell based on the values in a table on the report. I can do so using the data "Highlight" feature, but only for very limited conditions. Specifically, I can only do comparisons of positive numbers.

E.g., =IF({Forecast Variance}>50000,"yellow","none") works fine

but

=IF({Forecast Variance}<-50000,"yellow","none") does not work at all.

I've tried being creative and using formulas to make all the comparisons be to positive numbers, but I can't get the formulas to work either.

E.g., =IF((0 - {Forecast Variance}) > 50000,"yellow","none")

or, what I really want, which is =IF(ABS({Forecast Variance})> 50000,"yellow", "none")

Anyone else have similar problems and figure out how to do this correctly?

Thanks in advance!

Hi Kyle,

Could you slightly tweak the formula to the following and see if that works ?

=IF({Forecast Variance}<Value("-50000"),"yellow","none")

I just tried this in my instance, and it looks like it's working

Let me know how it goes.

Varghese

Yeah, I saw a post here that led me to try this and it doesn't help ... oddly (and as a hint to the root cause, I imagine) ... it works with ">" but not with "<"

Which led me to try =IF(0-{Forecast Variance}>Value("50000"),"yellow","none") which doesn't work either ...

Kyle Kindle,

Try qualifying the column name with the table name. so use Table_Name.Column_Name instead

=IF(ABS({<Table_Name>.Forecast Variance}) > 50000,"yellow", "none")

Nope. Still doesn't work.

Works for me Kyle Kindle.

Here's the formula I'm using:

      =IF(ABS({Test Cond Format.Value}) > 40,"yellow","orange")

where Test Cond Format is the table name.

Here's the result:

Nope ... here's what I used: =IF(ABS({Cost Source.Forecast Variance}) > 5,"yellow","orange") ... everything appeared Orange. Everything should have appeared Yellow.

I've tried it with both table columns and calculated metrics with no change in behavior.

So, I tried adding a calculated column with this formula ... and, it always comes back "orange"

I notice though, that as I edit the formula for the calculated column, there is a message in red that says "The formula you specified should NOT reference the SOURCE data."

My problem most likely lays with understanding what that message means ... any ideas?

And when I take out the Cost Source reference, the calculated column is ok.

And, when I now conditionally format the column, it works.

[Theme from twilight zone playing ...]

Arggh ... but now my formula does not evaluate correctly ...

My work around has been to create a calculated column "Monthly Explanation Required":

=If(ABS(Forecast Variance)>50000 OR (ABS(Forecast Variance)>10000 AND (ABS(Forecast Variance Percent)>.05 OR Forecast Variance Percent = "")),"Yes","No")

And then to highlight using:

=IF({Monthly Explanation Required}="Yes","#E6B8B7","#F2DCDB")

This works.

If I try to highlight directly:

=If(ABS(Forecast Variance)>50000 OR (ABS(Forecast Variance)>10000 AND (ABS(Forecast Variance Percent)>.05 OR Forecast Variance Percent = "")),"#E6B8B7","#F2DCDB")

It does not work.

[Theme music continues ...]

Kyle Kindle,

Seems you are so close to solving it, but circling around it. It is rather difficult to suggest a solution without looking at the actual report you are working on.

I suggest contacting your Apptio CSM  - @Kendra Elliott to review and assist.

Opening support ticket. Will post back here with any interesting findings.

Previous config you indicated you were trying to use:

=If(ABS(Forecast Variance)>50000 OR (ABS(Forecast Variance)>10000 AND (ABS(Forecast Variance Percent)>.05 OR Forecast Variance Percent = "")),"#E6B8B7","#F2DCDB")

Numbers are un-formated so you would want to use Value() to express numbers such as below:

=If(ABS(Forecast Variance)>Value("50000") OR (ABS(Forecast Variance)>Value("10000") AND (ABS(Forecast Variance Percent)>Value(".05") OR Forecast Variance Percent = "")),"#E6B8B7","#F2DCDB")