CODEWITHSUNDEEP
excelpivot-table
cristi _b
cristi _b

Reputation: 1813

Pivot Table Max Returns Blank instead of real value

I have a pivot table built with the following source data

Group  Product Date added
Group1 Item1   2012-01-01
Group1 Item2   2012-01-02
Group1 Item3   (blank)
Group2 Item4   2011-10-03
Group2 Item5   2011-10-05

When I construct the pivot table as following

Row label : Group
Values    : MAX(Date added)

I get the following results

Groups MAX(Date added)
Group1 (blank)
Group2 2011-10-05

I'm not sure why is this happening, I'm expecting for group1 items to get 2012-01-02.

The same behavior happends even if I have #ERROR or #VALUE errors.

How can I handle this kind of issue , can I populate my source data with 1900-01-01 or is there a function that ignores null or error values ?

Thank you.

Upvotes: 1

Views: 5866

Answers (1)

TH3 EX3CUT1VE
TH3 EX3CUT1VE

Reputation: 36

You "date added" field is most likely a formula that is producing a text field, so the pivot table likely doesn't understand the field as a value.

Add a column to the right of the "Date added" field that is

=VALUE(C2)

*where c2 is the first value in the date added field. Copy that down the entire contents of the date added column.

You can also write in that same column as:

=IF(ISERROR(VALUE(C2)),"",VALUE(C2))

which will essentially make all "blank" spots just null so the pivot table can read them.

Then use that column (Date added 2) in the pivot table instead.

Upvotes: 2

Related Questions