+ Reply to Thread
Results 1 to 18 of 18

Averageifs formula used on more than 1 column to average

  1. #1
    Registered User
    Join Date
    11-07-2018
    Location
    Bulgaria
    MS-Off Ver
    2016
    Posts
    9

    Question Averageifs formula used on more than 1 column to average

    Hello, I am trying to calculate an average of 2 columns.
    I want the yellow colums averaged on 3 criteria ranges - pink, orange and red if in those ranges there is written concrete text.
    Here is a picture of what I'm trying to do. excel.jpg

    Thanks in advance.
    Last edited by paolina13; 11-08-2018 at 05:25 PM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,343

    Re: Averageifs formula used on more than 1 column to average

    You could use an array formula, entered with Ctrl-Shift-Enter, like



    =AVERAGE(IF(A3:A36="13.10.2017r",IF(B3:B36="Characters",IF(C3:C36="BAPHA",D3:F36))))

    (I don't have keys to enter your values for B3:B36 - this will work as long as the values in E3:E36 are string values.

    Otherwise, your could add two AVERAGEIFS functions together, and divide by 2:

    =(AVERAGEIFS(D3:D36,A3:A36,"13.10.2017r",B3:B36,"Characters",C3:C36,"BAPHA") + AVERAGEIFS(F3:F36,A3:A36,"13.10.2017r",B3:B36,"Characters",C3:C36,"BAPHA"))/2


    Note that you could also use entire columns with the AVERAGEIFS so you don't have to worry about expanded ranges.

    =(AVERAGEIFS(D:D,A:A,"13.10.2017r",B:B,"Characters",C:C,"BAPHA") + AVERAGEIFS(F:F,A:A,"13.10.2017r",B:B,"Characters",C:C,"BAPHA"))/2
    Last edited by Bernie Deitrick; 11-07-2018 at 06:03 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    11-07-2018
    Location
    Bulgaria
    MS-Off Ver
    2016
    Posts
    9

    Re: Averageifs formula used on more than 1 column to average

    Thanks for the fast reply, but still it doesn't work. The first formula gives me result of 0 which is impossible.(this is when the formula is entered just by pressing ENTER. If I use Ctr+Shif+Enter it gives me the same error Devision by 0 is impossible.(#DIV/0!)) And the second one is giving me an error which says it can't be devided by 0. Also what meand colum E to be "string value"? This could be the problem for the firs formula. The second.. I don't see mistake in it.
    Attached Images Attached Images
    Last edited by paolina13; 11-08-2018 at 04:59 AM.

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,007

    Re: Averageifs formula used on more than 1 column to average

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,343

    Re: Averageifs formula used on more than 1 column to average

    Try using a version of the formula where you use cell references instead of strings, like

    =AVERAGEIFS(D:D,A:A,A3,B:B,B3,C:C,C3)

    That should return at least the value from D3 - if it does not, try formatting column D for number, and reenter the number in D3 as a number.

  6. #6
    Registered User
    Join Date
    11-07-2018
    Location
    Bulgaria
    MS-Off Ver
    2016
    Posts
    9

    Re: Averageifs formula used on more than 1 column to average

    Yes, this formula returns the value from cell D3. So what's the point?

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,452

    Re: Averageifs formula used on more than 1 column to average

    Are you required to use an AVERAGE() function? I would probably recall that the definition of average (arithmetic mean) is sum of values/count of values and perform the average as a combination of SUMIFS() and COUNTIFS() Maybe something like: =(SUMIFS(first column and criteria)+SUMIFS(second column and criteria))/(COUNTIFS(first column and criteria)+COUNTIFS(second column and criteria)) [I am assuming you can put the SUMIFS() and COUNTIFS() functions together].
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  8. #8
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,343

    Re: Averageifs formula used on more than 1 column to average

    The point is that the formula works when you have the correct values and/or combination of values. You either are not matching the actual values in your columns when you type them into the function or you do not have the specific combination of values that you are looking for: AVERAGEIFS returns an error when the combination of values is not found.

  9. #9
    Registered User
    Join Date
    11-07-2018
    Location
    Bulgaria
    MS-Off Ver
    2016
    Posts
    9

    Re: Averageifs formula used on more than 1 column to average

    Good idea really, I'll try it now and tell if it works or not.

  10. #10
    Registered User
    Join Date
    11-07-2018
    Location
    Bulgaria
    MS-Off Ver
    2016
    Posts
    9

    Re: Averageifs formula used on more than 1 column to average

    I've been stuck in this situation and my brain doesn't get it how the hell a computer program couldn't do such an easy thing... Probably it's my mistake and I don't write it corectly so that the computer understands me?! I don't know. It isn't necessary to use the AVERAGE/AVERAGEIF/AVERAGEIFS function, but that's just what I thought I could use in my case.
    Well this happens now. It gives me the correct answer if the formula is
    =AVERAGEIFS(D:D;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА")
    but it's the average just on 1 of the colums I want. When I try to enter the other one it doesn't work as 1 full formula
    =AVERAGEIFS(D:D;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА")+AVERAGEIFS(F:F;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА")
    nor does this formula works (because I don't know if there sholud be more bracets like so
    =(AVERAGEIFS(D:D;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА"))+(AVERAGEIFS(F:F;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА"))
    I think it's because of the + in the formula, I don't know if I am right or not.

    P.S. I still don't get it how to hide the picture tho. Sorry.
    Attached Images Attached Images

  11. #11
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,343

    Re: Averageifs formula used on more than 1 column to average

    If this works:

    =AVERAGEIFS(D:D;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА")

    This will work:


    =AVERAGEIFS(F:F;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА")

    If that does, try

    =(AVERAGEIFS(D:D;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА") + AVERAGEIFS(F:F;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА"))/2

    I just noticed that you have an extra ( in your function call AVERAGEIFS((..... That will cause the error.
    Last edited by Bernie Deitrick; 11-08-2018 at 02:52 PM.

  12. #12
    Registered User
    Join Date
    11-07-2018
    Location
    Bulgaria
    MS-Off Ver
    2016
    Posts
    9

    Re: Averageifs formula used on more than 1 column to average

    Yes it worked! Thank you so much. But I had this formula entered before. The exact same.. why hadn't it worked earlier?

  13. #13
    Registered User
    Join Date
    11-07-2018
    Location
    Bulgaria
    MS-Off Ver
    2016
    Posts
    9

    Re: Averageifs formula used on more than 1 column to average

    And if I want to average the temperatures for a certain period how would the formula look?
    =IF("01.01.2017"<A:A<"30.03.2017";AVERAGE(D:D)+AVERAGE(F:F))

  14. #14
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,343

    Re: Averageifs formula used on more than 1 column to average

    This will work

    =(AVERAGEIFS(D:D;A:A;">=" & DATE(2017;1;1);A:A;"<="&DATE(2017;3;30)) + AVERAGEIFS(F:F;A:A;">=" & DATE(2017;1;1);A:A;"<="&DATE(2017;3;30)))/2

    Though this may work - dates as strings need to match the system setting format....

    =(AVERAGEIFS(D:D;A:A;">=1.1.2017";A:A;"<=30.3.2017") + AVERAGEIFS(F:F;A:A;">=1.1.2017";A:A;"<=30.3.2017"))/2

    You could also replace the DATE functions with cell references.

  15. #15
    Registered User
    Join Date
    11-07-2018
    Location
    Bulgaria
    MS-Off Ver
    2016
    Posts
    9

    Re: Averageifs formula used on more than 1 column to average

    Thank you so so much! I can't describe how thankful I am right now! I really appreciate your help!

  16. #16
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,343

    Re: Averageifs formula used on more than 1 column to average

    You're welcome - can you mark the thread as solved so no one else will attempt to solve it?

    Thanks,
    Bernie

  17. #17
    Registered User
    Join Date
    11-07-2018
    Location
    Bulgaria
    MS-Off Ver
    2016
    Posts
    9

    Re: Averageifs formula used on more than 1 column to average

    I hope I've done it right

  18. #18
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,343

    Re: Averageifs formula used on more than 1 column to average

    Beautiful

+ 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: 5
    Last Post: 10-30-2017, 10:33 AM
  2. [SOLVED] Averageifs function not returning correct average
    By CSherman in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-02-2017, 11:11 PM
  3. [SOLVED] Different results for =average & averageifs for same data range
    By ljbrown in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-20-2017, 05:49 PM
  4. [SOLVED] AverageIfs to average two ranges if match is met
    By bjnockle in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-20-2017, 04:25 AM
  5. [SOLVED] AVERAGEIFS to average times in a range if criteria met
    By bjnockle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-26-2016, 03:06 AM
  6. [SOLVED] How to get an AVERAGEIFS formula to not include cell in average, by triggering switch?
    By Carmstrong227 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-19-2013, 01:15 PM
  7. Using AVERAGEIFS function to average a smaller subset of criteria data
    By RJS5062 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 03-22-2012, 04:19 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