finding largest gap within 9 rows

[Methix]

ok so i have this excel spreadsheet for work and i need to add a number at the botton of a row of numbers which represent the biggest gap between the numbers above. (the gap cannot exeed 9 rows)

example:

9/7/07 - 52.41
9/14/07 - 50.63
9/21/07 - 48.72
9/28/07 - 54
10/5/07 - 51
10/12/07 - 48
10/19/07 - 55
10/26/07 - 58
11/2/07 - 85
11/9/07 - 116
11/16/07 - 127
11/23/07 - 200
11/30/07 - 169
12/7/07 - 156
12/14/07 - 143
12/21/07 - 152
12/28/07 - 160

biggest gap: 152

i cannot manually do every single row (this excel is gigantic)

isnt there some excel formula to do this for me automatically?

thanks

[oldchippy]

Try this,

=MAX(B1:B17)-MIN(B1:B17)

[Methix]

oldchippy, thanks for the quick reply!
i did this at first but the difference may not exceed more than one month, in this case that would be 9 rows. in the example here this does apply but its only a part of the whole row, where smaller and bigger numbers exist in other months. hence it would take e.g. 13 from 8/11/06 as the minimum and 212 from 12/7/07 as the maximum where the difference is bigger but timewise exceeds the limit..

[oldchippy]

Hello there,

It’s not exactly clear what you are trying to achieve from the information given. Can you provide a small zipped sample of your data together with expected results, then hopefully a solution can be found.

To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this zipped file to your post.

[daddylonglegs]

Assuming data in B1:B100 try this formula

=MAX(SUBTOTAL(4,OFFSET(B1,ROW(B1:B100)-ROW(B1),,9))-SUBTOTAL(5,OFFSET(B1,ROW(B1:B100)-ROW(B1),,9)))

This is an array formula which needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

[Methix]

Someone send me this formula:
=MAX(MMULT(SUBTOTAL({4,5},OFFSET(B1:B18,ROW(INDIRECT("1:"&ROWS(B1:B18)-8))-1,0,9)),{1;-1}))
for the given example it calculates 152.28.
Can i still have the chosen rows automatically highlighted?

[NBVC]

Do not crosspost without referencing a link... the same question is being answered in 2 different forums....

http://www.mrexcel.com/forum/showthr...25#post1452825