summary of values for a period of time

[karmela]

Hi,
is there some function or macro to do a summary of values, which were made
in a peoriod of dates? For example from this tab...

Value Date
5 1/1/2007
5 2/1/2007
5 3/1/2007
6 4/1/2007
6 5/1/2007
..
..
..
6 20/1/2007
9 21/1/2007
9 22/1/2007 etc...

this summary:

Value From TIll
5 1/1/2007 3/1/2007
6 4/1/2007 20/1/2007
9 21/1/2007 .....


Thanks a lot for advice

karmela

 

[Roger Govier]

Hi

Try the following array entered formulae.
To enter or edit an Array formula, use Control+SHift+Enter (CSE) not just
Enter.
Do not type the curly braces { } yourself. When you use CSE, Excel will
enter them for you.

for the earliest date, use
{=MIN(IF(AND(A:A=A1,B:B<>""),B:B,""))}
for the latest date use
{=MAX(IF(A:A=A1,B:B,""))}

 

[sebastienm]

Hi,
you can use a pivot table which would be usefull if your data table's size
changes and new Values gets added.
Pivot:
Use Value as a row field and use Date twice as a Data field. Select the
first Date data field, choose Field Setting and use the aggregate function
Min. Do the same thing for the second data field using the aggregate function
Max. Now, you just need to format these 2 fields as Date.

 

[karmela]

Hi,

thanks for advice, I used it as well but unfortunatelly doesn't solve
problem in the case, when the values are changing up and also down.
It means for
1.day 7
2.day 7
3.day 7
4.day 5
5.day 5
6.day 7
7.day 7, .....

In the pivot table I get
7 1. day - 7.day
5 4.day - 5.day

It seems, that the value was for all these 7days.
There should be
7 1.-3.day
5 4.-5.day
7 6.-7. day

Well, I'm going to think about a macro....


karmela