+ Reply to Thread
Results 1 to 9 of 9

Solving AVERAGEIFS where one column must equal another column, please help!

  1. #1
    Registered User
    Join Date
    01-22-2021
    Location
    London
    MS-Off Ver
    2019
    Posts
    5

    Exclamation Solving AVERAGEIFS where one column must equal another column, please help!

    I have a set of data like that shown below. I am trying to do an average ifs for this data to average the number of trips, the amount of vehicles must be bigger than 0 AND the number of vehicles must equal the amount of people. what is the formula in order to do this?

    I am able to write the formula for if greater than zero, but I am having difficulty finding out how to say that the value in one column (vehicles) must equal the one in the next (people)
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    Re: Solving AVERAGEIFS where one column must equal another column, please help!

    Does it work? Sum/Count

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-22-2021
    Location
    London
    MS-Off Ver
    2019
    Posts
    5

    Re: Solving AVERAGEIFS where one column must equal another column, please help!

    No I am doing a cross classification analysis so I need to be able to use averageifs

  4. #4
    Registered User
    Join Date
    12-01-2020
    Location
    singapore
    MS-Off Ver
    MS2016
    Posts
    24

    Re: Solving AVERAGEIFS where one column must equal another column, please help!

    Try

    {=average(if((b2:b12>0)*(b2:b12=c2:c12),a2:a12))}

  5. #5
    Registered User
    Join Date
    01-22-2021
    Location
    London
    MS-Off Ver
    2019
    Posts
    5

    Re: Solving AVERAGEIFS where one column must equal another column, please help!

    Unfortunately this didnt work either

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    Re: Solving AVERAGEIFS where one column must equal another column, please help!

    Quote Originally Posted by ventura7 View Post
    No I am doing a cross classification analysis so I need to be able to use averageifs
    SUMPRODUCT (Sum)/SUMPRODUCT(Count) actually works like average(ifs)
    Pls add my formula in #2 and manual outcome input, to see if it works.

  7. #7
    Registered User
    Join Date
    01-22-2021
    Location
    London
    MS-Off Ver
    2019
    Posts
    5

    Re: Solving AVERAGEIFS where one column must equal another column, please help!

    It is throwing out an error

  8. #8
    Registered User
    Join Date
    01-22-2021
    Location
    London
    MS-Off Ver
    2019
    Posts
    5

    Re: Solving AVERAGEIFS where one column must equal another column, please help!

    Sure this should be so hard? Why can I not just use averageifs(A1:A12, B1:B12, ">0", C1:C12, ">"&B1:B12)

    Can someone explain why this does not work?

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Solving AVERAGEIFS where one column must equal another column, please help!

    Your sample file isn't great. Why?

    1. There are NO rows where vehicles = people.

    2. There are NO manually calculated expected results.

    So, we have to play guessing games as to what you actually want. I don't much like guessing games. Please amend and update your smple file.
    Glenn



+ 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. AVERAGEIFS AGE & COUNTIFS Column C contains Y OR column D contains Y
    By Jatzbiscuits in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-10-2020, 01:44 AM
  2. Adding multiple values in one column to equal to one number in another column.
    By Accountant0607 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2014, 09:24 AM
  3. Replies: 1
    Last Post: 11-24-2013, 01:51 AM
  4. Replies: 4
    Last Post: 05-08-2013, 08:33 AM
  5. Replies: 11
    Last Post: 01-18-2013, 04:05 PM
  6. Average range of cells in column if values in adjacent column are equal
    By RyNye in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-09-2012, 10:12 AM
  7. [SOLVED] What formula to use?:Sum column W if column Q equals 390 & column L equal McL
    By Carolyn in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-04-2006, 03:00 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