Blank Cells showing as 0 in pivot

[DLoewenthal]

I am running a column with an if formula to return "" when false and then
when true to return what amounts to an expense ratio. there are cases where
the expense ratio should show as 0.

I am then running a pivot table to do the min/max of expense ratios. The
problem I am running into is the fields that are populated with "" are
filling as 0 on the pivot in addition to the fields that should rightly show
0.

My question is, is there a way to have the "" cells be blank while still
keeping the appropriate 0 cells?

My way around it to create 2 separate columns with instead of "" a high
number for the min pivot and a low number for the max pivot thereby leaving
the 0s where appropriate and having the previous "" read as either a high/low
to avoid being used in the end result min/max. For the future I'd prefer an
easier way. Thanks.




The pivot is populating the "" cells as 0. I can't simply remove the zeros
because there are times when the cell is correctly populated with

 

[Eduardo]

Hi,
Use the Pivot table filters and uncheck the "0" that will solve your problem

 

[DLoewenthal]

Can you please elaborate.

I noticed that for some of the "" cells the 0 is not included in the Min
Subtotal, but in others it is. When clicking on the pivot cell to see the
underlying data, there is clearly a cell that is blank and others with
information in both instances. It seems there is no consistency to when the
Min Function is factoring in the 0 or not.