pivot table question

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,

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

<[email protected]> wrote in message
news:[email protected]...
> 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,
>