+ Reply to Thread
Results 1 to 12 of 12

AverageIFS statement

  1. #1
    Registered User
    Join Date
    05-22-2014
    Posts
    13

    AverageIFS statement

    I currently have an excel sheet in which I am working on and want to find the average of Chats and True.

    Column A = Type ( which can be either Chat or Offline)

    Column S = Chat Response Time (needs to be averaged)

    Column V= TRUE or FALSE

    I have been using the following formula :
    =AVERAGEIFS(CHATLOGS2!S:S,CHATLOGS2!A:A,"Chat",CHATLOGS2!V:V,TRUE)


    The formula is working but it is counting the FALSE too when it should not. I know this because I deleted the False columns and the average was correct. Thank you for any help.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: AverageIFS statement

    Sure it's really TRUE and not "TRUE"? Try putting True in Quotes.

    Other than that, hard to say without seeing your spreadsheet (Go Advanced>Manage Attachments)
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    05-22-2014
    Posts
    13

    Re: AverageIFS statement

    Thank you for the response. I have tried both ways of TRUE. I also tried to add the attachment as you suggested but it was too large to be uploaded. The file size was 1.8 mb.

    Please advise

  4. #4
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: AverageIFS statement

    Try zipping the file and upload it
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang

  5. #5
    Registered User
    Join Date
    05-22-2014
    Posts
    13

    Re: AverageIFS statement

    Chat Stat test 2.0.zip here is the attachment. I would like to thank you in advance for any help.

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

    Re: AverageIFS statement

    Seems to work for me

    =AVERAGEIFS(CHATLOGS2!S:S,CHATLOGS2!A:A,"Chat",CHATLOGS2!V:V,TRUE)
    It returns 68.33193717

    And I did a manual average by using the autofilter on the CHATLOGS2 sheet.
    Filtered A for Chat and V for True
    Then highlighted column S, and the statusbar displayed the same number 68.33193717

  7. #7
    Registered User
    Join Date
    05-22-2014
    Posts
    13

    Re: AverageIFS statement

    So I have added another file because I am not receiving the same average. I don't know what went wrong in formula. In chat logs I highlighted the total with filter in yellow. In the Summary it still reflects 68. I know average Ifs reads everything even in the background. I just want make sure that it recognizes not to read "False" or "Offline"
    Attached Files Attached Files

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

    Re: AverageIFS statement

    Please attach 'Small' example files.
    Keep the samples to 20 or so rows of data (50 tops)

  9. #9
    Registered User
    Join Date
    05-22-2014
    Posts
    13

    Re: AverageIFS statement

    My apologies. I have added an excel with lesser rows. In this excel I only focused on the AverageIfs the numbers still don't match. I even included False to see maybe if that is counting but it still does not match. I know in the formula I did S:S to which makes me believe it is reading the first cell even though the first cell does not include TRUE and Chat
    Attached Files Attached Files

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

    Re: AverageIFS statement

    I'm assuing you're talking about the averageifs formula on the Chat Stats Sheet, in B9
    =AVERAGEIFS(CHATLOGS2!S:S,CHATLOGS2!A:A,"Chat",CHATLOGS2!V:V,TRUE)

    And on the Chatlogs sheet S31 you have a plain old average
    =AVERAGE(S2:S31)

    And they don't match, this is what you percieve as incorrect.
    But that's just it, that IS correct.

    Because the plain old average =AVERAGE(S2:S31) is averaging ALL the values in S2:S31
    But the AverageIFS is NOT, it's only averaging the ones that meet the criteria.
    S31 does not meet the criteria, because A31 does NOT = Chat and V31 = False
    So the averageifs formula is really only averaging S2:S30, while the plain old average is averaging S2:S31

    If you change that to =AVERAGE(S2:S30), then it returns the same value as the Averageifs in the ChatStats sheet.



    Also, FYI. Though it's not 'wrong' persay, you don't have to enter the averageifs formula with CTRL + SHIFT + ENTER

  11. #11
    Registered User
    Join Date
    05-22-2014
    Posts
    13

    Re: AverageIFS statement

    Thank you so much for the help!

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

    Re: AverageIFS statement

    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. How do you write an AND or OR statement in an AVERAGEIFS formula?
    By ndrobinson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2013, 04:50 PM
  2. [SOLVED] Averageifs with an or statement
    By kerrold1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-01-2013, 10:41 AM
  3. AverageIfs Statement Counting Zeros
    By Anthem12 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-08-2013, 12:24 AM
  4. creating dynamic conditional statement for averageifs
    By olsoniv in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-16-2011, 11:33 AM
  5. Averageifs
    By rwtrader99 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-10-2010, 03:26 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