Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Blank Cells showing as 0 in pivot
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Blank Cells showing as 0 in pivot
Hi,
Use the Pivot table filters and uncheck the "0" that will solve your problem "DLoewenthal" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Blank Cells showing as 0 in pivot
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. "Eduardo" wrote: Hi, Use the Pivot table filters and uncheck the "0" that will solve your problem "DLoewenthal" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blank Pivot Cells | Excel Discussion (Misc queries) | |||
Not showing blank and non blank items in filter mode for values | Excel Worksheet Functions | |||
Showing Zero for all blank cells in a range | Excel Discussion (Misc queries) | |||
blank cells showing up as a month 1 | Excel Worksheet Functions | |||
showing blank cells | Excel Discussion (Misc queries) |