algorithm help

**david90** · 10-29-2012, 11:56 PM

Let say I have an array with the following numbers:

1,4,12,22,53,51,49,55,2,5,50,51,100

I'm trying to develop an algorithm that finds the max number of consecutive number in the array that is between 40 and 60.

For the example above, the algorithm should return 53,51,49 and 55. 50,51 are not returned because that is only 2 consecutive number that are between 40 and 60.

**rylo** · 10-30-2012, 12:38 AM

Hi

How about

Please Login or Register  to view this content.

rylo

**Moo the Dog** · 10-30-2012, 01:29 AM

Used a helper column method to return values. Let me know what you think. Just in case VBA is an issue.

- Moo

**Paul** · 10-30-2012, 02:14 AM

Building on Moo's formulaic approach, the formulas returning 1's and 0's in column B can be skipped if you use the following array formula (in column B) instead. It will produce the same results as shown in Moo's column C. Thanks to Domenic's post here: http://www.excelforum.com/excel-gene...ro-values.html.

Please Login or Register  to view this content.

Put that in B2 (using CTRL+SHIFT+ENTER, not just ENTER) and then fill down to B14. Moo's formulas to find the row with the max value and ultimately the values you're after can be moved to columns C and D. Slight adjusting to reference one less column, e.g.

C2: =MATCH(MAX(B2:B14),B2:B14,0)

and

D2, then filled down: =IF(ROW()-1>MAX($B$2:$B$14),"",INDEX($A2:$A$14,$C$2))

**david90** · 10-30-2012, 02:24 AM

How do people come up with algorithm? What's your method?

Oh and thank all who responded.

**Moo the Dog** · 10-30-2012, 10:33 AM

You're welcome, David.

Regarding 'coming up with algorithms' (assume you mean formulas and VBA), most of us just do a lot of self-teaching. Reading books, tutorials, experimentation to see what work and what doesn't. As well as reading through as many posts on this site as possible. It is amazing the things you can learn how to do by reading through the solutions to posts like yours on here! I've only been a member for a little over a month and I have already learned a LOT of new things. So, my advice to you, read, read, and read some more!

The most important thing I have learned is that there is almost always more than one way to accomplish the same task in Excel. Some are a lot more efficient than others - so I save those. We're always learning new tricks to manipulate the data the way we need to.

Good luck!

- Moo

- - - - - - - - - -
A good way to thank users who have helped is to click on the small star in one of their posts (below their name). We appreciate it!