pivot table question

[i_makino]

I have two columns of data:

Date Value
1/1/05 9
1/1/05 10
1/1/05 12
....
....
1/9/05 16
1/9/05 4

There may be a variable number of dates, and does not contain a
complete list of consecutive dates (i.e. some dates have no associated
data and are not in the list)

I want to produce a calculation the that subtracts the MIN for each
date from the MAX of each date. I do not necessarily have to display
the max and min values - I am really after the result

Date (max - min)
1/1/05 3 <12-9=3>
1/9/05 12 <16-4>




What is the best way to do this?
I have experimented with pivot tables, and have easily gotten the min
and max by date. I was looking at the calculated field option to
subtract the min from the max, but it doesn't work as I expected. Can
the Pivot Table calculated field do this?

I have also looked at subtotals, advanced filter, and dmin / dmax
calculations. None of these seem to easily deliver what I am after.

I am not against writing the VBA to "scan" my data - but I was just
wondering if there is an eaiser way -

thanks,

 

[Bob Umlas]

If you have a list of the distinct dates (which you can get from advanced
filter), then if the first unique date is in E1, for example, this formula
in F1 (& filled down) will do what you want: -- NOTE: it must be entered via
ctrl/shift/enter
=MAX(IF($A$1:$A$100=E1,$B$1:$B$100))-MIN(IF($A$1:$A$100=E1,$B$1:$B$100))
Bob Umlas
Excel MVP