formulas in pivot table source data

[greggatz]

I've got a formula in my source data...and my pivot table is making this a value...is there anyway around this issue?

The formula is to showcase when an order is past due...it gives a visual indicator of YES. But because there's a value in the cell, my pivot table is counting it in my summary.

Can anyone offer up some suggestions as to how to get around this?

[teylyn]

Hello,

move the formula into its own column and don't use that column in the pivot table.

If that does not help, please illustrate your data structure. A screen shot or sample file would be good.

cheers, teylyn

[greggatz]

here's the current file.

The biggest issue is my need to develop an Outside Vendor Scorecard for On Time Delivery as well as being able to see current orders that are past due.

Thanks for the help!

[teylyn]

You can change the formula to return a 1 or a 0 instead of a Yes and an empty string. Then you can use a Sum in the pivot table instead of a count.

=IF(AND([@Received]="",[@[Due Date]]<TEXT(NOW(),"mm/dd/yy")),1,IF([@Received]>[@[Due Date]],1,0))

Format the column with the custom format

"YES";;

and you will see YES and blank cells instead of 1 and 0. See attached.

cheers, teylyn