+ Reply to Thread
Results 1 to 10 of 10

Average of specific Location

  1. #1
    Registered User
    Join Date
    09-06-2016
    Location
    Fort Worth, Texas
    MS-Off Ver
    2013
    Posts
    10

    Average of specific Location

    I use a software that exports out results in a single .csv (about 22k rows). The averages I need are located in the neighboring column, and 23 rows down (for 24 cells) and then 2 rows below that as well (for an additional 24 cells). Can someone help me out? I attached a cutout, I've highlighted the value I'm looking up and also the cells that I need the average of.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    05-16-2018
    Location
    Pittsburgh, PA
    MS-Off Ver
    2016
    Posts
    109

    Re: Average of specific Location

    I am assuming you pull these reports once a week, and by average you mean the hourly average?

    I would use the average function and select your range. We would need to see more data examples if you are trying to use more advance function for lookups or what not

  3. #3
    Registered User
    Join Date
    09-06-2016
    Location
    Fort Worth, Texas
    MS-Off Ver
    2013
    Posts
    10

    Re: Average of specific Location

    That's just for one meter, there are +200 more. So the next meter would be directly after that, so on and so on. Using average and then selecting the range is possible, however also very time consuming, as there is over 22,000 rows of data to scroll through.

  4. #4
    Forum Contributor
    Join Date
    03-28-2018
    Location
    Houston, TX
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    127

    Re: Average of specific Location

    It would be helpful if you gave us the data for more than one meter. Also, are you wanting the average for all meters combined or just an average for each meter?

  5. #5
    Registered User
    Join Date
    09-06-2016
    Location
    Fort Worth, Texas
    MS-Off Ver
    2013
    Posts
    10

    Re: Average of specific Location

    I uploaded another section of data, it just goes on and on after that.

    Over to the right, you'll see a small table. I need a list of the Profile Number, average of calibration weekday flows, and average of calibration weekend flows, all have been highlighted yellow.
    Attached Files Attached Files

  6. #6
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Average of specific Location

    Formula for cell J5:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula for cell K5:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit: But, given the extra info you just gave, this ain't gonna work like that.
    Last edited by Tsjallie; 09-28-2018 at 03:28 PM.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  7. #7
    Registered User
    Join Date
    09-06-2016
    Location
    Fort Worth, Texas
    MS-Off Ver
    2013
    Posts
    10

    Re: Average of specific Location

    That works, but only for the 1st meter, if I drag it down it just gives the same value.

  8. #8
    Forum Contributor
    Join Date
    05-16-2018
    Location
    Pittsburgh, PA
    MS-Off Ver
    2016
    Posts
    109

    Re: Average of specific Location

    Exactly why more data was needed. Its based off the profile number. Maybe tsjallie can throw in a search from column A and adjust his indirect from that

  9. #9
    Forum Contributor
    Join Date
    03-28-2018
    Location
    Houston, TX
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    127

    Re: Average of specific Location

    Here you go. This formula is based on 2 things being true. 1) The data starts with 4 rows of information before the "PROFILE_NUMBER" data starts for each meter, and 2) the data for each meter is 114 lines long. I assume they will be, since the output looks standardized for each meter. You can paste these formulas in a table going down, where the first row of averages starts in row 5:

    Weekday Avg:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Weekend Avg:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Hopefully you can follow the logic of these formulas.

    Thanks!

  10. #10
    Registered User
    Join Date
    09-06-2016
    Location
    Fort Worth, Texas
    MS-Off Ver
    2013
    Posts
    10

    Re: Average of specific Location

    Magic! That did it! Thank you so much!

+ 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. [SOLVED] Copying specific cells and pasting in specific location VBA Help.
    By N3CREN in forum Excel General
    Replies: 9
    Last Post: 09-21-2018, 04:18 PM
  2. Saving a Workbook With A Specific File Name in a Specific Location
    By kyjae in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-06-2016, 02:49 PM
  3. Open hyperlink (photo) in a specific spreadsheet location and specific size
    By bigtex in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-08-2015, 02:45 PM
  4. [SOLVED] Macro to manually choose PDF file and save it to a specific location with specific name
    By tnuis in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-18-2014, 07:47 AM
  5. Replies: 0
    Last Post: 07-11-2013, 02:08 AM
  6. Replies: 1
    Last Post: 06-30-2013, 05:58 PM
  7. Searching for specific letter in specific location in cells
    By pmoniz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-12-2013, 08:26 AM

Tags for this Thread

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