Help with a calculated field in a PivotTable
Question
-
Hi, I am trying to write a formula for a calculated field inside a Pivot Table. I want to return the value 1 if the row value is equal to "New York", and zero otherwise. My formula is
=IF(ISNUMBER(FIND(City, "New York" ) ),1,0 )
However, the formula always returns zero. Please see the attached screen shot.
Thanks, Ben.
All replies
-
Hi,
Because "New York" is a text value you need to reference and return the result with text value "False" and "True", the text values are seen as 0 in calculate flied. You can try to test with number value in calculate filed, then you will get the correct result.
In my opinion, you can try to add the formula to source table.
Hope it's helpful.
Regards,
Emi Zhang
Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.
Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.
- Proposed as answer by Emily HuaMicrosoft contingent staff Tuesday, November 19, 2019 1:08 AM
