AverageIfs Complexity
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: AverageIfs Complexity
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default AverageIfs Complexity

    Good afternoon,

    I have a problem... I am hoping someone can help.

    I have a spread-sheet which contains various codes in Column C (30,40,90,95,99) and value of percentage in Column G.

    I want to take the values 30, 40 and average all amounts including 0% and I also want to include values 90, 95, 99 but with a value greater than 0% (not include 0%)

    Currently I have it calculating everything greater than 0%

    =AVERAGEIFS($G$2:$G$109,$C$2:$C$109,"<>30",$G$2:$G$109,">0") <
    A B
    WLKITL WLDOCO WASRST WADRQJ WLUORG WLSOQS PERCENTAGE
    Part 1434671 99 07/31/18 630 630 100.00%
    1434672 99 08/01/18 900 900 100.00%
    1434673 90 08/02/18 630 630 100.00%

    Basically when I average this I filter anything containing a 90, 95, 99 and delete all 0% then average like normal... want something faster.

    Thank you

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: AverageIfs Complexity

    Care to state clearly what must column C and column must meet?
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    New Member
    Join Date
    Sep 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AverageIfs Complexity

    It's all about the average Column C is the stage in which the part is...

    30 is beginning, all 0% is counted
    40 is work started, all % greater than 0 is counted
    90, 95, 99 is finished work but only % greater than 0 is counted, anything at 0% is not averaged.

    Column G is the tally at the end. a total of all rows is what I show for attainment.

    WO Beginning and WO End = WOE/WOB = % of completion =

    Hope this helps

  4. #4
    New Member
    Join Date
    Sep 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AverageIfs Complexity

    Quote Originally Posted by Aladin Akyurek View Post
    Care to state clearly what must column C and column must meet?
    I need the average for the entire column G excluding 0% of 90,95,99 (from column c) - maybe this is better
    Last edited by mrwiley; Aug 15th, 2018 at 05:54 PM.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: AverageIfs Complexity

    Quote Originally Posted by mrwiley View Post
    I need the average for the entire column G excluding 0% of 90,95,99 (from column c) - maybe this is better
    That is, column C must meet either 90 or 95 or 99; Column G must meet > 0%. Let's try:

    =SUM(SUMIFS(G:G,G:G,">0",C:C,{90,95,99}))/SUM(COUNTIFS(G:G,">0",C:C,{90,95,99}))
    Last edited by Aladin Akyurek; Aug 15th, 2018 at 11:11 PM.
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #6
    New Member
    Join Date
    Sep 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AverageIfs Complexity

    Quote Originally Posted by Aladin Akyurek View Post
    That is, column C must meet either 90 or 95 or 99; Column G must meet > 0%. Let's try:

    =SUM(SUMIFS(G:G,G:G,">0",C:C,{90,95,99}))/SUM(COUNTIFS(G:G,">0",C:C,{90,95,99}))
    Thank you but this caused a circular reference.

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: AverageIfs Complexity

    Quote Originally Posted by mrwiley View Post
    Thank you but this caused a circular reference.
    Where did you enter the formula?
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #8
    New Member
    Join Date
    Sep 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AverageIfs Complexity

    Good Morning, I entered in Column G

    Quote Originally Posted by Aladin Akyurek View Post
    Where did you enter the formula?

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: AverageIfs Complexity

    Quote Originally Posted by mrwiley View Post
    Good Morning, I entered in Column G
    That's also the range whose values you want to average. The formula should not be entered in column G or C.
    Assuming too much and qualifying too much are two faces of the same problem.

  10. #10
    New Member
    Join Date
    Sep 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AverageIfs Complexity

    Quote Originally Posted by Aladin Akyurek View Post
    That's also the range whose values you want to average. The formula should not be entered in column G or C.
    Today is report day, I inputted your formula, it provides a % but its not correct.

    I need to calculate all percentages while excluding 0% of ONLY 90,95,99's.

    Yous calculated 96% but mine was 76% - wish I could attached a spreadsheet example. Thank you for your feedback and formula.
    Last edited by mrwiley; Aug 22nd, 2018 at 03:32 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •