adding a calculated field to a pivot table

[merlin777]

I have a pivot table to which I'm trying to add a calculated field but having no luck.

I am trying to check the difference between 2 numeric fields and depending on whether or not if it is positive or negative I want to display 'over' or 'under'.

I can get it to accept the formula =column1-column2 but when I try to add an IF statement such as this:

=IF(column1-column2>0,"OVER","UNDER")

then excel wont accept it. Is there any way I can achieve this?

tia

 

[Fazza]

What if you make the formula column1 - column2
and then custom format it with
"over";"under"

 

[merlin777]

Hi fazza. Do you mean Conditional formatting? Can you do that with text?

 

[Fazza]

hi, merlin

Not conditional formatting. A custom format.
So go to normal numbers formatting (CTRL-1 then N). there is a list of categories on the LHS. Last one is custom. Near middle of dialog at type enter, for example "positive";"negative"

OK?

 

[merlin777]

Wow - never knew it could do that! What's the convention for this? Does it just accept 2 strings and apply the first to positive values and the second to negative?

It gets be in the ball park but I needed a more complex formula to take into account values which are almost the same e.g. a value might be within 5% of the other but still negative so I would ignore it. I know I can do that with IFs. If I can't pull that off I'll go with the custom formatting as you suggest.

 

[Fazza]

refer Excel help or https://support.office.com/en-us/ar...r-format-78f2a361-936b-4c03-8772-09fab54be7f4

there is a section 'specify conditions'

say you change your formula to return a percentage you can work with <.05 for example

I think this has been in Excel as long as I can remember. I guess it was the pre-cursor to conditional formatting

see how you go

 

[merlin777]

It's a nifty little thing and I'll be using it again!

I cracked my problem by adding a column to my table and inserting 2 columns/formula into my pivot table. I did a bit of maths to give me either a positive result or a negative one and then using this number formatting. Not elegant but it works....

Thanks for all the help.

 

[Fazza]

You're very welcome, merlin. All the best