# Multiple IF statements in a pivot calculated field

1. ## Multiple IF statements in a pivot calculated field

Hello,

I am trying to use the following IF statement as a calculated field in my pivot table. However, it only recognizes the first IF and rounds to the tenth decimal place as stated first.

=IF((Conf_pos/Scrn_acn)>=1,ROUND((Conf_pos/Scrn_acn),1),IF((AND(((Conf_pos/Scrn_acn)<1),((Conf_pos/Scrn_acn)>=0.0195))),ROUND((Conf_pos/Scrn_acn),2),ROUND((Conf_pos/Scrn_acn),3)))

Both "Conf_pos" and "Scrn_acn" are numeric variables in my pivot table.

Thank you!  Register To Reply

2. ## Re: Multiple IF statements in a pivot calculated field

Calculated fields always sum the fields in the formula before doing anything else, so your formula is actually:

=IF((SUM(Conf_pos)/SUM(Scrn_acn))>=1,ROUND((SUM(Conf_pos)/SUM(Scrn_acn)),1),IF((AND(((SUM(Conf_pos)/SUM(Scrn_acn))<1),((SUM(Conf_pos)/SUM(Scrn_acn))>=0.0195))),ROUND((SUM(Conf_pos)/SUM(Scrn_acn)),2),ROUND((SUM(Conf_pos)/SUM(Scrn_acn)),3)))

which I suspect explains what you are seeing?  Register To Reply

3. ## Re: Multiple IF statements in a pivot calculated field

Yes, I did know that it sums the fields first, which is what I would like it to do. Why would that prevent the formula from executing correctly?  Register To Reply

4. ## Re: Multiple IF statements in a pivot calculated field

It works for me. Can you post a workbook (see the yellow banner at the top of the page) showing it not working? I assume you haven't applied a specific number format to the field/cells.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 