extracting max of every N cells into a plottable array

I posted a similar question on general discussion forum (which is where the potential solution below was generated (from "ExcelHero"). But after almost 200 views did not get much......... Hoping that someone here knows more and can help.........

I have data in cells A1:A5000 and would like to extract the maximum of every 5 consecutive cells into a named array that I can then refer to and plot on a chart. So, the first entry of the array would be max(A1:A5), the next max(A6:A10), and so on.

I'm trying to do this so I do not have to generate another column of data in the spreadsheet. I'd also like to see if I can avoid using VB, as I have a lot of data, and VB seems to slow down the computer quite a bit as it calculates all 1000 max's and puts them together.

It appears that

=MAX(OFFSET(Sheet1!$A$1,ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,250))*5-5,0,5,1))

should work, but for some reason it does not. Would be greatful for any new ideas.....

Thank you

How about:

=MAX(INDEX(A:A,1+((ROW(A1)-1)*5)):INDEX(A:A,5+((ROW(A1)-1)*5)))

Still no luck.... I'm givem an error message when saving and opening the sheet, and the chart does not show the data. Please see attached.

It seems to be working for me... the top graph seems to coincide with the data in column C....

Hmmmm...... Strange... Could it be the version of excel I'm using (2007)? Something else?

I attached a screen shot of the error I'm given - it's on Sheet2

Does this one work?

I removed one of the named ranges...

The top chart shows up fine, but the bottom one gives an error still...

What does the bottom one plot? I don't see the definition of series?

On your latest attachment, the bottom chart's series still refers to maxFive, though because maxFive was deleted from the name manager, the chart does not show anything.....

Not sure if this is effecting it or not:

After entering the formula from your initial reply into the name manager, it converts it to:


=MAX(INDEX(Sheet1!XFC:XFC,1+((ROW(Sheet1!XFC1)-1)*5)):INDEX(Sheet1!XFC:XFC,5+((ROW(Sheet1!XFC1)-1)*5)))

I tried tying down the A's like this, but still no effect:

=MAX(INDEX(Sheet1!$A:$A,1+((ROW(Sheet1!$A$1)-1)*5)):INDEX(Sheet1!$A:$A,5+((ROW(Sheet1!$A$1)-1)*5)))

Any one else have any other suggestions/formula ideas?

That formula was not meant to be a named formula. Column C is already getting the max of each group of 5... so I don't understand what the difference would be between the 2 charts?

Ohhhhhhhhhhhhhhhhhh................... OK, I misunderstood, or we misunderstood eacher. Maybe I was not clear enough in my initial post. I'm specifically looking for a named formula (I called it "named array" in my initial post).

My goal is to only have the "source" data in cells A1:A5000, and no other formulas anywhere else in the spreadsheet cells. Then extract max of every 5 cells of that data into a named array ("maxFive") with a formula similar to what you have proposed, which will never appear in any of the cells of the spreadsheet. Then plot that named array in a chart, with a series of the chart reffering to it like this: =Sheet1!maxFive.

I used 2 chart to compare the plot of the raw data of max of every five cells (in column C) to a chart who's series was =Sheet1!maxFive to see if the charts would be the same.

I appologise for any confusion.

PS: If this is of any help, I used this formula to extract every 5th cell (not the max, though) into a named array that I was then able to plot. This formula did not go into any of the cells of the speadsheet, but was entered directly into the name manager under the name "everyFive". Then the chart's series reffered to it like this: Sheet1!everyFive.

=N(OFFSET(Sheet1!$A$1,ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,250))*5-1,0,1,1))

I am not a charting expert.. so I will have to think about this one... will try to get back in the morning...

Awesome! Thank you.

Hi I have tried your original formula and although it works in the worksheet, it gives me the same error you were experiencing originally (which I guess I misunderstood was your original point).

I can't seem to figure it out, though... I am sure that I am missing something....

I have asked a charting expert to look if he has time... hopefully we can help resolve this.

In the meantime, I will also continue to play around and see what I can come up with.

Great! I really appreciate your help.

I don't think the Array formula is getting executed the same within the Named Range as it is on the worksheet.

If I use this formula in a cell and then evaluate each of the nested functions you can see why the named range will error.

=N(MAX(OFFSET(Sheet1!$A$1,(ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,5))-1)*5,0,5,1)))

=N(MAX(OFFSET(Sheet1!$A$1,{0;5;10;15;20},0,5,1)))

=N(MAX({41;#VALUE!;#VALUE!;#VALUE!;#VALUE!}))

=N(#VALUE!)

#VALUE!

Use the same formula, across 5 rows, but entered as an array using CSE and the correct values will be returned.

I think you will need to use worksheet cells to get the MAX values of sections of the data.

Hi Andy,

Thanks for looking at this...

I figured similarly, that was my original suggestion (to create the range in the worksheet)...

... but I was curious as to how a similar formula to extract every 5th value from the range worked here in the last attachment from ExcelHero:

http://www.excelforum.com/excel-gene...her-array.html

he referenced a named range called "xxx" which contains this array formula:


=N(OFFSET(Sheet1!$A$1,ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,250))*5-1,0,1,1))

and it seems to work...

Is there a notable difference in the structure of the formulas? The only real difference is the addition of the MAX() function and the change of height in the offset function to include a range of 5 cells instead of just 1...

Are those the differences that don't allow it to work here?

I think it is the MAX function that causes the problem.

Andy, do you think that means that no other simple function can be used in this scenario, such as SUM, MIN, etc.? Are there any other ways to get around this without entering the furmulas into individual cells or using VB?

Probably, although somebody may know different.
I think, but can not recall exactly where I read it, it is to do with the fact that MAX does not return array of results

What is the reason for not wanting to use cells?

I think it will be the same with the other functions too.. it is the way the Named Range processes.. that additional Function seems to disengage the result from the reference to the sheet which is required in the named formula.... I tried applying indirect.. but to no avail.

Why, though can you not just have it on the worksheet and then use a dynamic named range to refer to the listing of results?... you can always hide the column.

Looks like I'll just have to use the cells.... The reason I was trying to use an array is that it is a bit faster than having the spreadhseet calculate each individual formula which will have to have INDIRECT in it to get the data from another worksheet (I'll have the worksheet that displays the data in a chart, and a separate data sheet which contains the 5000 rows of data). I have 5 of these of these kind of arrays I'll need, so that will be 500*5 = 2500 cells to fill with INDRECT. Since I need to scan throught many 100's of charts very quickly every 1/10 of a second makes a difference. I may just inter the formulas into the source sheet itself and then convert the values to text, and bring those values into a simple plottable array to speed up processing.

Thank you both for your help and the time spent looking at this. I guess we've just reached the limit of what excel can do in this area.....