Conditional Formatting Excel Query

Cotta

Hi All,


I have a formula driven cell set to turn red if it is at any value other than "0". My problem is the cell currently appears red even though the value in the cell is "0". However looking at the exact figure in the cell it appears to be 0.00000003, is there any way around this?


Thanks

grumbler

Well, 0.00000003 isn't 0, is it?
If it's a calculated value, either ROUND(...) it as you need or in conditional formatting "Use a formula to determine which cells to format" with the formula, e.g. ABS(...)<0.0001.

Cotta

Ok, below is my formula and it work perfect and is initially returning "0", how do I add the "Round" function to this in order to ensure that decimal points are ignored?


=GETPIVOTDATA("Total Fees",$A$6,"Financial Quarter","Q1")-SUMIF('Matter Data'!C2:C1048576,'RBS Summary Pivots'!B7,'Matter Data'!AQ2 :Q1048576)

GunJack

Cotta wrote: »

probably

=ROUNDDOWN(GETPIVOTDATA("Total Fees",$A$6,"Financial Quarter","Q1")-SUMIF('Matter Data'!C2:C1048576,'RBS Summary Pivots'!B7,'Matter Data'!AQ2 :Q1048576))

this will round down to zero

grumbler

You don't want to use ROUNDDOWN because, say, -0.0000000000001 will be rounded to -1.

The correct way of doing this is ROUND(......, 2) for rounding to pence or ROUND(......, 0) for rounding to pounds.

Cotta

grumbler wrote: »

You don't want to use ROUNDDOWN because, say, -0.0000000000001 will be rounded to -1.

The correct way of doing this is ROUND(......, 2) for rounding to pence or ROUND(......, 0) for rounding to pounds.

What way is this incorporated into the above formula? I'm rounding to pound.

grumbler

=ROUND(GETPIVOTDATA("Total Fees",$A$6,"Financial Quarter","Q1")-SUMIF('Matter Data'!C2:C1048576,'RBS Summary Pivots'!B7,'Matter Data'!AQ2 :Q1048576),0)

Le_Kirk

....... or in your conditional formatting formula just put "<1" as that will cover zero and 0.00000003. Of course it will also be a problem if there is a possibility of negative numbers as -1 is also <1.

Cotta

grumbler wrote: »

=ROUND(GETPIVOTDATA("Total Fees",$A$6,"Financial Quarter","Q1")-SUMIF('Matter Data'!C2:C1048576,'RBS Summary Pivots'!B7,'Matter Data'!AQ2 :Q1048576),0)

Perfect, that worked a treat, thank you so much.