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!
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?
Rory
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?
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks