+ Reply to Thread
Results 1 to 22 of 22

Average of specific cells with numbers determined by multiple criteria

  1. #1
    Registered User
    Join Date
    10-20-2015
    Location
    Las Vegas, NV
    MS-Off Ver
    2013
    Posts
    11

    Average of specific cells with numbers determined by multiple criteria

    I'm trying to create a formula on the first screenshot that references the cells in the second screenshot based on the name and week number. In the second screenshot, which shows data from the "HVL" tab, you can see the associate's name on the left and the week numbers across the top as the column headers. Because the week number appears more than once is where I believe I'm having the issue. The 100% that you see in the cell is not correct according to the data in the "HVL" tab. Here are some formulas that I've tried using:

    =AVERAGE(((HVL!$A$2:$A$144='Weekly Report Card'!$C$1)*(HVL!$B$1:$AG$1='Weekly Report Card'!$A6)*(HVL!$C$3:$AG$201<>0)))
    =AVERAGE(IF(HVL!A2:A144='Weekly Report Card'!C1:H1, IF(HVL!B1:AG1='Weekly Report Card'!A6, HVL!C3:AG201<>0, "ERROR")))
    Both as an array and as normal.
    =AVERAGEIF(HVL!$A$2:$A$144, "<>", INDEX(HVL!$C$3:$AG$201, MATCH($C$1, HVL!$A$2:$A$144, 0), MATCH('Weekly Report Card'!$A8, HVL!$C$1:$AG$1, 0)))
    Both as an array and as normal.

    If there's any other information that you need, please let me know.

    excel_weekly_report.png

    excel_hvl_tab.png

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Average of specific cells with numbers determined by multiple criteria

    Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

    If you need to post an image post it in the *.jpg format.

    Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    10-20-2015
    Location
    Las Vegas, NV
    MS-Off Ver
    2013
    Posts
    11

    Re: Average of specific cells with numbers determined by multiple criteria

    I don't think there's an attachment option for the forum but noted for the future about the image extension.

    I uploaded the sample file to the following address and contains both the Weekly Report Card tab and the HVL tab that are being worked with for the formulas.

    http://www.filehosting.org/file/deta...le%20File.xlsx

  4. #4
    Registered User
    Join Date
    10-20-2015
    Location
    Las Vegas, NV
    MS-Off Ver
    2013
    Posts
    11

    Re: Average of specific cells with numbers determined by multiple criteria

    Realized the download link is requesting an email to download. Here's one from my DropBox.

    https://dl.dropboxusercontent.com/u/...le%20File.xlsx

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Average of specific cells with numbers determined by multiple criteria

    OK, I downloaded your file.

    What am I looking for?

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Average of specific cells with numbers determined by multiple criteria

    You can attach a file directly to the formum by clicking "Go Advanced", then scroll down a little bit and look for "Manage Attachments"

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Average of specific cells with numbers determined by multiple criteria

    You need the rows in each range to line up with each other...
    =AVERAGEIF(HVL!$A$2:$A$144, "<>", INDEX(HVL!$C$3:$AG$201, MATCH($C$1, HVL!$A$2:$A$144, 0), MATCH('Weekly Report Card'!$A8, HVL!$C$1:$AG$1, 0)))

    You have HVL!$A$2:$A$144 (row 2 to 144)
    But then HVL!$C$3:$AG$201 (Row 3 to 201)
    They should all reference the same rows..
    Try this, covering from row 2 to 201
    =AVERAGEIF(HVL!$A$2:$A$201, "<>", INDEX(HVL!$C$2:$AG$201, MATCH($C$1, HVL!$A$2:$A$201, 0), MATCH('Weekly Report Card'!$A8, HVL!$C$1:$AG$1, 0)))

  8. #8
    Registered User
    Join Date
    10-20-2015
    Location
    Las Vegas, NV
    MS-Off Ver
    2013
    Posts
    11

    Re: Average of specific cells with numbers determined by multiple criteria

    I would like the average of percentages based on week number and by associate's name entered into the "Name" field under the productivity section from the HVL tab.

  9. #9
    Registered User
    Join Date
    10-20-2015
    Location
    Las Vegas, NV
    MS-Off Ver
    2013
    Posts
    11

    Re: Average of specific cells with numbers determined by multiple criteria

    I tried your formula in cell C7 for week 28 and moved the reference for the week to A7 to match under Weekly Report Card using the name Alex Arredondo in the name section but I am getting a #DIV/0 error. I changed "<>" to "<>0" as well and even tried entering as an array formula but I still get the error.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Average of specific cells with numbers determined by multiple criteria

    I get an error 404 when trying to download the file linked in post #4. I would suggest following Jonmo1's instructions (post #6) for uploading a file directly to the forum.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  11. #11
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Average of specific cells with numbers determined by multiple criteria

    Can you attach a file with the forum's attachment tool
    Click Go Advanced, then find and click "Manage Attachments"

  12. #12
    Registered User
    Join Date
    10-20-2015
    Location
    Las Vegas, NV
    MS-Off Ver
    2013
    Posts
    11

    Re: Average of specific cells with numbers determined by multiple criteria

    I am currently having an issue trying to attach directly to this forum. I clicked go advanced, then clicked on the attachments icon but it is only giving me a blank drop down menu. I'm not sure why it won't let me attach anything.

  13. #13
    Registered User
    Join Date
    10-20-2015
    Location
    Las Vegas, NV
    MS-Off Ver
    2013
    Posts
    11

    Re: Average of specific cells with numbers determined by multiple criteria

    Nevermind. I saw the "Manage Attachments" section and uploaded the file. Let me know if it's visible to you.

  14. #14
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Average of specific cells with numbers determined by multiple criteria

    No excel file as attachment seen in your threats.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  15. #15
    Registered User
    Join Date
    10-20-2015
    Location
    Las Vegas, NV
    MS-Off Ver
    2013
    Posts
    11

    Re: Average of specific cells with numbers determined by multiple criteria

    samplefileattachment.JPG

    Here is a screenshot of me having attached the file. If it's not showing up then I'm not sure what I'm doing wrong.
    Attached Files Attached Files

  16. #16
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Average of specific cells with numbers determined by multiple criteria

    Try this in C6 of the report card sheet

    =IFERROR(AVERAGEIF(HVL!$C$1:$AG$1,$A6,INDEX(HVL!$C$3:$AG$202,MATCH($C$1,HVL!$A$3:$A$202,0),0)),"")

  17. #17
    Registered User
    Join Date
    10-20-2015
    Location
    Las Vegas, NV
    MS-Off Ver
    2013
    Posts
    11

    Re: Average of specific cells with numbers determined by multiple criteria

    You... you are so awesome. It worked! Thank you so much, I really appreciate the help and glad I didn't end up going bald in the process.

  18. #18
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Average of specific cells with numbers determined by multiple criteria

    You're welcome.

  19. #19
    Registered User
    Join Date
    10-20-2015
    Location
    Las Vegas, NV
    MS-Off Ver
    2013
    Posts
    11

    Re: Average of specific cells with numbers determined by multiple criteria

    Just wondering, what is the logic behind this? Is it because the Index/Match combination doesn't work properly for the column headers and only finds one match? Because I see that you had pretty much reversed the way it was doing the lookup from what I initially had. I would like to understand to better help me make these in the future if you don't mind.

  20. #20
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Average of specific cells with numbers determined by multiple criteria

    Averageif requires similarly dimensioned ranges,
    All ranges 1 row like A1:Z1 and A2:Z2
    or All ranges 1 column like A1:A10 and B1:B10

    You were trying to mix those dimensions, with 1 Row range like A1:Z1, and 1 column Range like A1:A10.
    Averageif can't do that (same for countif/sumif)

    So the trick is to find the row where the designated name is and make that into a 1 row range to mach the 1 row range of your number criteria

    INDEX(HVL!$C$3:$AG$202,MATCH($C$1,HVL!$A$3:$A$202,0),0)

    So say Match finds C1 in A10 for example
    The match returns 8 (A10 is the 8th cell in A3:A202)

    INDEX(HVL!$C$3:$AG$202,8,0)
    The 0 in the column argument of Index makes it return ALL cells in the 8th row.
    So Index then returns a range that is the entire 8th row of C3:AG202, which is C10:AG10

    Now the overall formula becomes
    =IFERROR(AVERAGEIF(HVL!$C$1:$AG$1,$A6,HVL!$C$10:$A$G10),"")

    Now averageif is happy with 2 ranges of similar dimensions.

  21. #21
    Registered User
    Join Date
    10-20-2015
    Location
    Las Vegas, NV
    MS-Off Ver
    2013
    Posts
    11

    Re: Average of specific cells with numbers determined by multiple criteria

    Alright! Thank you for the explanation! That makes a lot of sense!

  22. #22
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Average of specific cells with numbers determined by multiple criteria

    You're welcome.

+ 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. Replies: 2
    Last Post: 08-07-2015, 12:42 AM
  2. Replies: 0
    Last Post: 03-01-2015, 11:34 PM
  3. Average numbers from multiple columns based on multiple criteria
    By abambi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-23-2015, 01:52 PM
  4. average of cells with multiple criteria
    By Pexxoo in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-07-2014, 10:56 PM
  5. [SOLVED] Construct a formula on a pre-determined cell for a pre-determined duration of cells
    By Shaun Gemiver in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-03-2013, 11:14 AM
  6. Replies: 3
    Last Post: 02-13-2012, 04:43 AM
  7. Trying to take an average of numbers in specific cells...
    By corty_d in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-13-2008, 12:57 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