extracting every 5th value of a vertical array into another array

I have a vertical named 1 by 5000 array (my_array) and would Iike to extract every 5th value into another named array. Is this possible by insering a formula into the name manager without resorting to a number of formulas in individual cells?

Also, if the above is possible, how would I extract maximum of every 5 entires of "my_array" and put them into a new array? So. for example, the first entry in "new_array" would be =max(A1:A5), the second entry would be =max(A6:A10), and so on....

Thank you

You can use the attached UDF. For example, define

myArray Refers to ==Sheet1!$A$1:$A$5000

EveryFive Refers to =DisjointRange(INDEX(myArray,1), INDEX(myArray,6), 1000)

As a non-contiguous range, you won't be able to do all the same things you can do with a contiguous range.

Please Login or Register  to view this content.

Thank you. Did you mean

=DisjointRange(INDEX(myArray,1), INDEX(myArray,6), 1000) ?

If I try to enter

=DisjointRange(=DisjointRange(INDEX(myArray,1), INDEX(myArray,6), 1000))
into the name manager, it gives me an error message.

=DisjointRange(INDEX(myArray,1), INDEX(myArray,6), 1000) works, but unfortunately considerably slows down the computer...... are there any other ways of accomplishing what I'm after? Perhaps without using VB?

Thank you

Yes, corrected below.

but unfortunately considerably slows down the computer

No surprise; the Union function itself is slow. What are you doing with it?

Originally Posted by shg

What are you doing with it?

i'm plotting the result (every fifth value) on a chart, and as the data in the source vector changes, need to be able to see the result (every fifth value plotted) almost instanteneously, as i'm sorting throught 100th of chart manually (visual check - not much excel can do here)

Originally Posted by ExcelHero

luv2glyd -

This named formula will produce a range of every fifth element of your named vertical array:
=INDEX(NamedVerticalArray,ROW(INDIRECT("1:"&1000))*5)

/

Daniel,

I inserted the formula into the name manager, though it does not seem to work... It seems to only contain one value in it vs. 1000. I tried to have a chart refer to it, as my ultimte goal is to be able to plot it, but was given an error...........

Could you attach a worksheet with an example?

Originally Posted by siva972377

=index(datarange,rows(a$1:a1),1)
copy the formula

I'm actually looking to name a range with a formula vs. generating a new column of data in the cells of the worksheet.

Still no luck.......... attached is a sheet with a graph in question highlited in yellow. I copied the formuls from your post, and named the sheet "Some Sheet" just so there are no typos. Assuming everything is correct, I should be able just to change the named range a chart reffers to to EveryFiveAlt. When I try doing that I'm given an error for some reason...

Awesome!!! Thank you very much! That is one complicated formula This will speed things up quite a bit in what I'm trying to accomplish.

Is it possible to have this formula refer to myArray vector only, and not the cells in the worksheet? (Withouth going into too much detail, my vector's data in the real Worksheet I'm working with is actually located in secondary "Data" sheet, and the worksheet I'm working in never sees it's values in any of it's cells. myArray's numbers are imported into myArray from another worksheet using INDIRECT function.)

Also, any ideas on how to make the second part of my question work? The one with the max of every 5 consecutive cells?.......... It's in the first post.

Thanks again for your help.

Cool!! Let me see if I can make this work with what I'm doing. Thanks again for your help!

Ok, just did I already cheked out your site - looks pretty cool. I like all the different charts and illusions.

I made the first one work like this:

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

I have 100's of sheets to cycle throught and can do that just by entering the name of the sheet into cell named "data_sheet"

Having trouble with the second formula (one using MAX). As with the first one a few posts ago, when trying to plot and set the chart series to this named range, I'm givem an error........ Any idea why it's doing that?....

Thank you

Any suggestions on how to tweak this this named range so I can refer to it in a chart?

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

Thanks