Forum Discussion
Solved
MAX of numeric data column in excel pivot table returns 0
There are three data columns in the shared excel file - a Membership ID column formatted as text, a billing number in column Num formatted as text, and a FiscalYear column formatted as numbers. As I...
Column C is all text. Check with ISNUMBER() or ISTEXT().
Change the formula:
=IF(A2 <> "", --LEFT(A2,4),0)
Hello k_straz​,
The FiscalYear values are likely stored as text even though they appear numeric. PivotTables cannot calculate MAX on text values, so the result may show as 0.
Formatting the column as Number does not convert text to numbers.
Select the FiscalYear column, go to Data, choose Text to Columns, and click Finish to convert the values to numbers. Then refresh the PivotTable and MAX should return the correct year.
Blank cells will not affect the calculation because PivotTables ignore blanks.