Search code examples
excelif-statementexcel-formulacalculated-columnscalculated-field

Excel IF statement in calculated field

I am trying to create a calculated field that uses an IF statement in an Excel pivot table. I have a ratio (let's say x/y) and I need the actual returned value to be displayed if the ratio is >=0 and "<0" to be displayed if the ratio is less than zero. I have been trying the following:

=IF((x/y>0),(x/y),"<0")

It returns values for anything >0 but just gives VALUE! instead of returning "<0" for the ratio values that are less than zero. Any ideas on how to fix this?

Thanks!

Solution

  • A calculated field in a Pivot Table cannot contain text, it can only contain numbers, because in a pivot table, the results of a calculated field are expected to be aggregate-able (through sum, for example).

    The fact that <0 is a text, it cannot be rendered as a number and thus results in a #VALUE! error.

    You could however use conditional formatting on the Pivot Table to make all the values below zero look like <0:

    enter image description here