+ Reply to Thread
Results 1 to 6 of 6

averageif + average

  1. #1
    Registered User
    Join Date
    05-12-2017
    Location
    Vienna
    MS-Off Ver
    2017
    Posts
    2

    averageif + average

    Hey there,

    so I got an Excel Problem.
    I have a list of suppliers, which are evaluated (1 = Excellent, 5 = Bad) by 7 different criteria. The problem here is, that there are loads of people, who will evaluate the same supplier, so I will receive different evaluations for one supplier. Now, I want to calculate the average evaluation for each supplier.
    I've tried averageif, but it won't calculate the average of all seven criterias in the columns and add it up with each line, but only the average of criteria1 between the lines.

    excelprob.png

    Is there a possibility to receive my result with only one formula?
    It's pretty essential to get it in one formula, since my data will update regularly and I need the formula to keep up automatically.

    So what I am looking for is something that will work like this:
    Artweger = ((2 + 3 + 2 + 3 + 3 + 3 + 3)/7 + (1 + 1+ 2 + 2+ 2+ 1 + 1)/7)/2

    Hope it's somewhat understandable!

    All the best,
    Jasmin

  2. #2
    Forum Contributor
    Join Date
    10-28-2015
    Location
    Lincolnshire, England
    MS-Off Ver
    2016
    Posts
    188

    Re: averageif + average

    Do you need an average per criteria or just an average of each supplier?

    If it's for each supplier - see attached
    Attached Files Attached Files
    Last edited by LukeGilfoyle; 05-12-2017 at 08:09 AM.
    If myself or others have helped, please add to our reputation by pressing the 'Star' icon below this.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: averageif + average

    Try

    =SUMPRODUCT(($B$2:$H$100))/(SUMPRODUCT(--($A$2:$A$100="Artweger")*7))

  4. #4
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,861

    Re: averageif + average

    I think you need average of supplier.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Supplier range : "A2:A8"
    Criteria in "J1"
    your data : "B2:H8"


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,462

    Re: averageif + average

    Try this ...

    =AVERAGE(IF($A$2:$A$7=A10,$B$2:$H$7))

    Array formula, enter with Ctrl+Shift+Enter.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-12-2017
    Location
    Vienna
    MS-Off Ver
    2017
    Posts
    2

    Re: averageif + average

    Thank you so much guys!
    Thanks to your help, I found something that works fine for me!

    =SUMPRODUCT((A:A="Artweger)*B:H)/7/COUNTIF(A:A;"Artweger")

    I had to exclude the first row (non-numerical values), so I adapted the first part of the formula furthermore: =SUMPRODUCT((A2:A2000="Artweger")*B2:H2000)/7/COUNTIF(A:A;"Artweger")

    Thank you guys so much,
    have a pleasant day!

+ 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. Getting an average across columns with AverageIf
    By anthony1312002 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-31-2017, 08:53 AM
  2. [SOLVED] Averageif, but only average last X # of values
    By smatchymo in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-02-2016, 05:19 PM
  3. Trailing Average Using AverageIf in Excel
    By atomant2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-21-2014, 12:45 AM
  4. [SOLVED] AVERAGEIF Vs AVERAGE(IF()) not return the same result
    By Faridwahidi in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-01-2014, 02:07 PM
  5. AVERAGEIF - calculate monthly average
    By TheRobsterUK in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-05-2014, 11:53 AM
  6. AVERAGEIF Only Perform the Average if a Filter is Met
    By RossThompson87 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-06-2013, 07:53 AM
  7. Another Average or AverageIf Issue
    By scarames in forum Excel General
    Replies: 6
    Last Post: 08-18-2009, 11:09 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