+ 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
    9,409

    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.
    Quang PT

  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
    35

    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
    9,409

    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
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

+ 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. 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