I have a series of dates in column A in date order, against each date there is a variable numeric value.
Starting at the first date and for each date after I would like to add the numeric values for 22 days forward. This is to find the peak number for any series of 22 consecutive days.
Hope this makes sense
Thanks
Not sure why you want to "add the numeric values" if you want to get the peak - you need to use the MAX function.
What cell is your starting date in?
Hope this helps.
Pete
Thanks for replying.
Dates start in a4, values in g4.
I have managed to solve this myself, although I doubt it is the most efficient solution. I'll share anyway.
=IFERROR(SUM(IF(A4:A1000>=A4,IF(A4:A1000<=A4+22,C4:C1000))),"") as an array formula.
This will determine the series of dates of 22 days that contain the peak value.
Then use this =INDEX(A4:A1000,MATCH(MAX(H4:H1000),H4:H1000,0)) to return the date of the first occurence of the peak value.
There are a couple of ways at looking at your problem.
The first is that the days are consecutive days without gaps. The second way is that there are gaps in the dates
This formula will sum 22 days into the future....total of 23 days....the same as your formula. My formula for the date is the same as yours and returns the same date.
This give the sum for 22 "dates" into the future
Formula:
Please Login or Register to view this content.
If there are gaps in the dates then this will work for 22 "days" into the future:
Formula:Please Login or Register to view this content.
Last edited by newdoverman; 11-01-2014 at 01:59 PM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Hi
another possible approach (ispired to newdoverman's proposal) could be
Formula:Please Login or Register to view this content.
Regards
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
@canapone
That works very well like the first situation that I and sipa gave.....consecutive dates without gaps in the dates. I thought, after posting, that there could be great gaps in the dates that would/could skew the results from having as little as 1 day to as many as 23 days. I then added the SUMIFS formula that would take the actual dates into consideration. The formula for returning the date with the highest value remains the same as what sipa posted.
What are your thoughts/
Here is yet another way to calculate the sum using actual date spreads of 23 days and not counts of consecutive days:
Formula:Please Login or Register to view this content.
Last edited by newdoverman; 11-01-2014 at 02:27 PM.
Thank you both for the reply's and the alternative methods too.
Newdoverman, this formula returns the same result as mine =SUMIFS(C4:C1000,A4:A1000,">="&A4,A4:A1000,"<="&A4+22)
as does this one too, =SUMPRODUCT((A4:A1000>=A4)*(A4:A1000<=A4+22),C4:C1000)
Canapone, your formula, =SUM(C4:INDEX(C4:C1000,23)) yields the same result as Newdoverman with this =SUM(OFFSET(C4,0,0,23))
But neither of these return the result I'm looking for. At least I now have 3 alternative solutions to work with.
I Appreciate your time.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks