Forum Discussion
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 want to see the most recent fiscal year in the pivot table for each unique membership id, I added MAX of FiscalYear in the pivot table Value section.
In the FiscalYear data column:
- There are no text entries.
- There are no blank cells in the column in the pivot table range.
- The formatting in the Value Field Settings for MAX of Fiscal Year is numeric.
- I have refreshed the pivot table.
Yet I am seeing a zero for each Membership ID rather that the most recent fiscal year.
Would you please explain why the Max of FiscalYear is not working correctly, and once I understand how to correct the problem, will the correction also work when I expand the pivot table range to reference the entire FiscalYear column rather than the column cell values in the first 29 rows? There will be blanks in the unpopulated FiscalYear column cells as the remaining rows will be blank for all three data columns until more rows are added over time.
Link to the Excel file on OneDrive
https://docs.google.com/spreadsheets/d/16B3JJ_Zn1848R4THgxYcz1piwju5Zlg7/edit?usp=sharing&ouid=111420556245583625384&rtpof=true&sd=true
Thanks in advance for your help?
Column C is all text. Check with ISNUMBER() or ISTEXT().
Change the formula:
=IF(A2 <> "", --LEFT(A2,4),0)
2 Replies
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.
Column C is all text. Check with ISNUMBER() or ISTEXT().
Change the formula:
=IF(A2 <> "", --LEFT(A2,4),0)
