+ Reply to Thread
Results 1 to 4 of 4

AVERAGEIFS Formula With A Range

  1. #1
    Registered User
    Join Date
    06-09-2014
    Posts
    4

    AVERAGEIFS Formula With A Range

    Hello,

    I've been trying to use the averageifs formula to determine the aveage FTE needed for 1st, 2nd, 3rd, 4th and years beyond for different client types. Column A displays the client type, Column B the number of years live and Column C is I've come up with the following formula but I can't seem to get it to work right. Was hoping someone might be able to point me in the right direction to get it correct? I also apologize in advance if a similar question has already been asked, but I searched through the posts and was unable to find anything that helped with my issue.

    =AVERAGEIFS(C2:C16,A2:A16,"C",B2:B16,">=0",B2:B16,"<1")

    Client Years FTE
    C 3.61 2.49
    P - 0.00
    P 4.61 0.16
    M 0.94 0.00
    M 3.28 0.57
    P 0.94 0.00
    C - 0.06
    C 3.61 0.51
    C 3.45 0.29
    P 3.12 4.18
    C 4.12 1.58
    C 5.37 1.25
    M 4.12 0.63
    P 7.29 0.36
    M 2.61 1.27

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: AVERAGEIFS Formula With A Range

    so that formula is saying

    =AVERAGEIFS(C2:C16,A2:A16,"C",B2:B16,">=0",B2:B16,"<1")

    Where C exists in column A
    AND
    Where B is between 0 and less than 1 (not =1)
    for the rows where those two conditions are TRUE
    work out the average for those rows only

    is that what you need ?

    C 3.61 2.49

    C - 0.06
    C 3.61 0.51
    C 3.45 0.29
    C 4.12 1.58
    C 5.37 1.25

    But None of column B are between 0 and 1
    unless - is formatting for 0
    in which case the result is just that 1 row

    so 0.06
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    06-09-2014
    Posts
    4

    Re: AVERAGEIFS Formula With A Range

    Stupid mistake on my part, thanks for pointing it out. Everything is working fine now!

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: AVERAGEIFS Formula With A Range

    excellent - glad you worked it out - often happens to me, i look at something for hours and then when i go over it with a colleague they spot something straight away

    thanks for the rep

+ 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 with multiple criteria on one range
    By ctsmith84 in forum Excel General
    Replies: 7
    Last Post: 04-23-2020, 11:31 PM
  2. AVERAGEIFS Formula with Date Range and Matching a Name
    By luvnwatts6 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-09-2014, 12:47 PM
  3. [SOLVED] Averageifs and rolling data range
    By Wolfgang17 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-24-2014, 07:40 AM
  4. [SOLVED] Averageifs using a named range?
    By fgruhlke in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-18-2014, 02:16 PM
  5. Using HLOOKUP to determine the range to AVERAGEIFS
    By wishkey in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-12-2013, 10:01 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