Hi,
Currently I have a table that has partially entered data. eg:
Row Tree Value1 Value 2 Value 3
10 1 130 140 150
10 2 0 20 10
11 1
11 2 20 0 0
11 3 0 0 0
This includes zeros (which is what the value should be) and blanks where no data has been entered yet.
I Have "For empty cells show:" ticked with nothing in the box.
The problem is when I put this data into a pivot table and create a calculated field such as:
= Value1 + Value2 + Value3
It treats the blanks and the Zeros as the same, as in 11-1 will show 0 (Incorrect) as well as 11-3 as 0 (Correct).
I have tried:
=IF(Field<>"",formula,"")
=IF(Field="","",formula)
=IF(Field="",NA(),formula)
and many others but the formula still treats the blank as a Zero. Is there a way to differentiate between Zeros and Blanks?
Thanks.
Bookmarks