Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
summary of values for a period of time
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
summary of values for a period of time
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,""))} -- Regards Roger Govier "karmela" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
summary of values for a period of time
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. -- Regards, Sébastien <http://www.ondemandanalysis.com "karmela" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
summary of values for a period of time
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 "sebastienm" wrote: 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. -- Regards, Sébastien <http://www.ondemandanalysis.com "karmela" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to average values based on time period | Excel Worksheet Functions | |||
Saving a file with time and date at a set time period | Excel Programming | |||
average rate of change per given time period between 2 moments in time of a value | Excel Worksheet Functions | |||
average rate of change per given time period between 2 moments in time of a value | Excel Programming | |||
Every time i put a number with period it becomes a date and time | Excel Discussion (Misc queries) |