Pivot tables with diferent Value Field settings from the Subtotal field

[abv14]

Hello,

I am having trouble with a pivot feature and I have Excel 2007. My raw data for example has different entries for a same date, When building the Pivot, I need to check the Min of each date. So when I change the Value field setting to Min, the Subtotal will also be the Min (I need the Sum of all the Min). When I try a calculated formula field in the Pivot it will not return the absolute minimum data for each date.

Please help!

Andres Benavides

[Ken Puls]

I don't believe that you're able to do this with PivotTables. With PowerPivot yes, but you need a minimum of Excel 2010 for that.

If you can modify your data source (add columns to it) then you can do it that way in 2007 though. You just need to add a column that works out the MIN for any given date, returning 0 if the row is not the minimum. Then you could use the SUM function in the PivotTable. Does that make sense?

[abv14]

Hey Ken, thanks for the quick response.

Well that kind of helps I usually add some help columns to the source data but in this case I am kind of lost with the formula because the MIN needs to be from the start time and do a cross check with the person who logged in.

Basically what I have in my data source would be dates in column A, worker name in column B, start time in column C. It may have more that one row per worker per day, that is why I am interested in the minimum per worker per day and compare it to a table with the target start time for each. I don't know if I explained myself correctly, I could try to upload some dummy data later today to recreate my problem.

Thanks again Ken

[Ken Puls]

Dummy data would definitely help.