+ Reply to Thread
Results 1 to 8 of 8

Finding data within an array.

  1. #1
    Registered User
    Join Date
    11-13-2008
    Location
    Colorado
    Posts
    80

    Finding data within an array.

    So I'm close on this one. I have data in an array...walking 10 steps. I need the max value for each step so let's say a step takes 1000 data points. I need the max of the first 1k, 2nd 1k etc. I think I use a row reference for the row I'm in and as I drag down it needs to jump every 1000 finding the max for each step.

  2. #2
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365
    Posts
    952

    Re: Finding data within an array.

    what you need is not clear. here's why:

    => you may be confused regarding the definition of "array". you talking about a coding object or an actual worksheet in excel?
    => you say you need a max value for each step and a step takes 1000 data points. sooooo....what does your table look like? like this?
    Please Login or Register  to view this content.
    -Adam (please don't call me "sir"!)

  3. #3
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,258

    Re: Finding data within an array.

    I did this example with more mangeable numbers:
    3 steps with 5 data points per step.
    It should be readily adaptable to your larger data set.
    It looks like this:

    gannon_w.png

    The data points are in B2 on down.
    In C3 copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    StartRow is set to 2. Change it per your needs.
    Change the 5's to 1000 for your data set.

    I provided a second approach in G1:H4 if you want to collect the maxima in a separate table. In H2 copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If I have guessed wrong here then please upload a workbook.
    Attached Files Attached Files
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

  4. #4
    Registered User
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    250

    Re: Finding data within an array.

    Suppose your data is in column A, drag under the formula B1

    HTML Code: 
    =MAX(OFFSET(A$1,(ROW(1:1)-1)*1000,,1000,))

  5. #5
    Registered User
    Join Date
    11-13-2008
    Location
    Colorado
    Posts
    80

    Re: Finding data within an array.

    That's it wk9128....Thanks!!

  6. #6
    Registered User
    Join Date
    11-13-2008
    Location
    Colorado
    Posts
    80

    Re: Finding data within an array.

    That also works Geoff! Thanks!

  7. #7
    Registered User
    Join Date
    11-13-2008
    Location
    Colorado
    Posts
    80

    Re: Finding data within an array.

    Both Geoff and wk9128 got it...I can never remember the OFFSET function!

  8. #8
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,258

    Re: Finding data within an array.

    No problem, glad to help. Thanks for the rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Finding a specific set of numbers in an array of numerical data
    By sameltopia in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-26-2019, 07:06 AM
  2. [SOLVED] Finding Max value in one row of a 2D array (array is not pulled from sheet)
    By marrott2 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-12-2014, 06:13 PM
  3. Finding the Open in an Array of Data
    By MagicMike in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-23-2012, 03:41 PM
  4. Finding the first value in an array
    By remiral.s in forum Excel General
    Replies: 3
    Last Post: 03-23-2012, 05:09 PM
  5. finding a data point in an array, and the labels
    By NRZ in forum Excel General
    Replies: 5
    Last Post: 08-13-2010, 12:05 PM
  6. Finding 2nd max in an array
    By jartzh in forum Excel General
    Replies: 3
    Last Post: 11-12-2008, 05:55 PM
  7. Finding a value in a 2D array
    By mwc0914 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-11-2008, 02:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1