+ Reply to Thread
Results 1 to 5 of 5

Search for a value in a worksheet and based on its address add another value to an average

  1. #1
    Registered User
    Join Date
    03-11-2021
    Location
    Romania
    MS-Off Ver
    2019
    Posts
    2

    Question Search for a value in a worksheet and based on its address add another value to an average

    Hi guys. Hopefully someone with a better understand of Excel than me can point me on the right path here. Been trying everything with Index, Match, Address functions, but still no luck with this.

    So I have this main worksheet "Dashboard", where I want to showcase a value of an average.

    Then, I have a different worksheet "EngagementRate", where I broke down each week of the year, and added a field for a weekly 'Engagement Rate' value to be added.

    What I mean to do is this. Go through every value in Column 1 of the "EngagementRate" worksheet, and if the string within the cell matches another given string (say "January"), then based on the address of that found cell, I could point to my Engagement Rate value. In this case if A3 = "January" that would be Row = 3, Column = 1. And my Engagement Rate value could be calculated by pointing at Row+2, and Column+1, since they all follow the same structure over and over. And then I would like to add that Engagement rate value I just calculated to an average. And all of this by pointing to the "EngagementRate" worksheet from the "Dashboard" one, where I want my final average showcased.

    I hope that made even a tiny bit of sense, and I'm happy to receive any tips to make this work. Been driving me crazy so far. I've added a small worksheet where I added only these fields.

    Thank you guys!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    648

    Re: Search for a value in a worksheet and based on its address add another value to an ave

    Try in A2 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Good luck!

  3. #3
    Registered User
    Join Date
    03-11-2021
    Location
    Romania
    MS-Off Ver
    2019
    Posts
    2

    Re: Search for a value in a worksheet and based on its address add another value to an ave

    Thank you very much for your help. However this posed another problem down the line when I tried to replicate it for another set of data. Since all of these monthly structures repeat until December (both within the Dashboard worksheet, and within the other secondary worksheets) I was able to make the formula work for the first month, but it won't work for months 2-12.

    I have created another Test sheet for the new data structure that I'd appreciate if you could take a look at. I couldn't even get it to work within this worksheet at all this time. I am really confused about what I am doing wrong here. Hopefully you could help me out again, and thank you for your time!
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,766

    Re: Search for a value in a worksheet and based on its address add another value to an ave

    I recommend to change your data format to the in "FollowersGroth2" as you current format is far from practical

    =IFERROR(AVERAGEIFS(FollowersGrowth2!C4:C10,FollowersGrowth2!$A4:$A10,">="&Dashboard!$A3,FollowersGrowth2!$A4:$A10,"<="&EOMONTH(Dashboard!$A3,0)),"")
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    648

    Re: Search for a value in a worksheet and based on its address add another value to an ave

    For the new file in post #3, this formula in A2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    And this in D2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that in the first formula, the criteria range starts in A3, where the first instance of MONTH is entered, and goes down to A100, or any other row of your choosing.
    The average range starts in A6, where the first instance of VALUE is entered, and goes down to A103, so both ranges have the same number of rows.

    Also, note that the AVERAGEIF function ignores blank cells.

+ 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. VBA Excel Google Search Address and Pull First Search Result
    By senker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-19-2017, 11:21 PM
  2. vba search based on address in database
    By excel girl in forum Access Tables & Databases
    Replies: 7
    Last Post: 11-14-2015, 08:48 PM
  3. Macro to send individual tabs from a worksheet to different email address based on in
    By mikeyexcel in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-30-2014, 07:46 PM
  4. [SOLVED] Use address of named range to find same address in another worksheet
    By dwsteyl in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-08-2013, 04:56 PM
  5. Replies: 11
    Last Post: 05-26-2013, 07:45 AM
  6. Search...Return...give info based on the return address
    By deek1004 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-11-2012, 03:36 PM
  7. Indirect Address Match - search value based of 2 variables
    By arazoe in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-09-2010, 06:04 PM

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