Could the Column Data Type in the Column Adjustment tab be to 'Number' as default and NOT 'Text'.
Because it allows you to transpose in bulk so 'N/A' or 'X' can be transposed to '0' but the calculated column frequently still doesn't work with empties etc if the format is Text.