Highest total of any three consecutive numbers in a range.

[skablaw]

I am searching for a function or formula that might give me the
biggest sum of three consecutive integers in a range. For example if
I have the numbers 1,5,2,7,8,5,2,4,9,1 in a range of cells, the number
I'm hoping to get is 20 because in the range, the sum of the
consecutive string of 7,8,5 is the biggest.

I'm not looking to sum the three highest numbers in the range. That
would be the sum of 7,8, and 9. What I want to know is the highest
total of any three consecutive numbers.

An example of how it might be used would be to track the peak shift in
a production environment. So let's say that we have a 24 hour day,
and I want to know inside that 24 hour day, what are the 8 consecutive
hours (length of a shift) that produce the most product.

It almost goes without saying, but many thanks to any and all who
would care to help me tackle this one!

-Stephen

 

[krcowen]

Stephen

If you data is in column A, starting in row 1 and going to row 20, you
could put in cell b3, =sum(a1:a3) and copy it down to the end of your
data. Then somewhere else you put the formula =max(b3:b20) and that
should give you the maximim total.

You could use conditional formatting to highlight the end of the range
that matches the maximum 3 interval total.

Good luck.

Ken
Norfolk, Va

 

[Mike H]

with your numbers in a1 - a10 try this

=SUMPRODUCT(MAX(A1:A10+OFFSET(A1:A10,1,0)+OFFSET(A1:A10,2,0)))

Mike

 

[Ron Coderre]

If your values are in A1:J1
try this:
=MAX(INDEX(A1:H1+B1:I1+C1:J1,0))

But, if your values are in A1:a10
then use this:
=MAX(INDEX(A1:A8+A2:A9+A3:A10,0))

Using your posted data,
both formulas return: 20....the sum of 7, 8, and 5

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[T. Valko]

Try this:

With your data in the range A1:A10.

=SUMPRODUCT(MAX(A1:A8+A2:A9+A3:A10))

Note the pattern of how each range reference is offset.

 

[ryguy7272]

Place your numbers in Col A and the sum in Cell B1, then this macro will show
you the series of consecutive numbers that sum to the value in Cell B1:
Sub FindSeries()

Dim StartRng As Range
Dim EndRng As Range
Dim Answer As Long
Dim TestTotal As Long

Answer = Range("B1") '<<< CHANGE

Set StartRng = Range("A1")
Set EndRng = StartRng
Do Until False
TestTotal = Application.Sum(Range(StartRng, EndRng))
If TestTotal = Answer Then
Range(StartRng, EndRng).Select
Exit Do
ElseIf TestTotal > Answer Then
Set StartRng = StartRng(2, 1)
Set EndRng = StartRng
Else
Set EndRng = EndRng(2, 1)
If EndRng.Value = vbNullString Then
MsgBox "No series found"
Exit Do
End If
End If
Loop
End Sub

The hard part, however, is to find the number that max you will sum to. I
suppose you can sum the numbers in an adjacent column and look for the max in
that range...

Regards,
Ryan---

 

[ryguy7272]

Use this file:
http://www.freefilehosting.net/download/3f6j5

Regards,
Ryan---

 

[ryguy7272]

=SUM(LARGE(A1:A20,{1,2,3,4,5}))
=AVERAGE(LARGE(A1:A20,{1,2,3,4,5}))


Regards,
Ryan---

 

[skablaw]

I've attempted Biff's solution multiple times with varying ranges and
integers and it always seems to give me the result I want so I think
I'm set. I always get nervous when I see responses of such varying
technique and complexity because it makes me think I'm missing
something haha!

Of all of the solutions I think this is the simplest and easiest for
me to duplicate for the specific result I'm looking for, although I
see some very powerful tools that could potentially be customized in
useful ways.

Has anyone tried Biff's method and found a specific scenario in which
it does not work?

-Stephen
Omaha, NE

 

[T. Valko]

Hmmm...

I don't know if your reply is a vote of confidence or not! <g>

*Almost any* formula can fail if it's subjected to situations that are not
accounted for. But, only *you* know what the formula is to be used for and
under what conditions. For example, the formula *will* fail if there are
error values in your range or if there are TEXT entries in the range. I
don't know if it's possible that either of those conditions will occur so I
don't account for them in the formula. From my perspective, I can only make
suggestions based on the info you provide. If I make a generic suggestion
that accounts for errors and/or text entries and those are not possible
conditions then the formula I suggest is bloated with overkill and a waste
of resources.

I'm pretty sure the formula suggested does what you asked for! (but someone
will *force* it to fail under a not probable condition and let me know about
it (read: rub my face in it!))

 

[skablaw]

No no, I certainly didn't mean to cast doubt on you. The formula is
working splendidly for me and I can't thank you enough. I always
think it's good to have a bit of peer scrutiny just in case there is a
little quirk that can be easily hashed out on the front end, but that
doesn't preclude a vote of confidence! I can't thank you enough for
your help. You have honestly saved me countless hours of work.

I'm employed by a Fortune 20 company and our entire consulting branch
is currently doing the process I described by SIGHT. They have FTEs
that sit in front of spreadsheets and literally look at thousands of
daily consumption reports and try to calculate the highest consecutive
totals of varying numbers of days by adding groups of three or more
over and over and over until they find the peak. It boggles my mind
that no one in the company ever stopped to say "hey, isn't there a way
to automate this?" Your little gem is promotion material for how much
time it will save this division! Now I just need to run a Kaizen
event around it and pretend to come up with this idea during the
week.. that'll really make the brass smile.

-Stephen

 

[T. Valko]

I always think it's good to have a bit of peer scrutiny

I agree. There are a handful of regular contributors to this group that will
usually let someone know if there's a problem or a better way to do
something. This thread is a good example of just that. Look at how many
different suggestions were made. I can think of a couple more ways to do
this but I think the *best* solution(s) have already been made.

Thanks for the feedback!