If statement with non-numeric data in a pivot table

[misterfluff]

Im trying to do a pivot table in which one column is a calculated field based on non-numeric data (eg the if statement is
If('Question'="why",'Answer',0), where 'Question' and 'answer' are fields), but i just get 0 for everything.

If I change the logical test to a numerical one it works, but then if its a numerical test and the false value is non numeric (say, IF('Number'=5,'Prime',"Gosh") then whenever it is false it just says #VALUE instead of Gosh. yet with the same test, if i swap "Gosh" for a numeric field it works.

What is going on? How do i solve this?

 

[VoG]

Duplicate http://www.mrexcel.com/forum/showthread.php?t=561464

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).