I
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,
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,