+ Reply to Thread
Results 1 to 15 of 15

Skipping results from IF within AVERAGE

  1. #1
    Registered User
    Join Date
    03-04-2020
    Location
    Cologne
    MS-Off Ver
    Office 365 (Win 10 - Work)
    Posts
    12

    Skipping results from IF within AVERAGE

    Hey Folks,

    I'm struggling with the calculation of a dependent AVERAGE-Function.

    Here is my case, as provided in the attachment:

    I want to include only cells within the AVERAGE function where is no "X" underneath. When I return "" or 0 as false output from IF, AVERAGE treats that as value 0.

    Any ideas how to solve this?

    Best regards
    idlebert
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,409

    Re: Skipping results from IF within AVERAGE

    Please update your profile - there are at least three versions of Excel that might be considered the "latest"!

    If you really do have the latest version, then this:

    =AVERAGEIFS($A$2:$A$11,$A$3:$A$12,"<>X")

    A European locale will require semi-colons instead of commas, of course.
    Attached Files Attached Files
    Last edited by AliGW; 05-28-2020 at 04:42 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,409

    Re: Skipping results from IF within AVERAGE

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  4. #4
    Registered User
    Join Date
    03-04-2020
    Location
    Cologne
    MS-Off Ver
    Office 365 (Win 10 - Work)
    Posts
    12

    Re: Skipping results from IF within AVERAGE

    Hey Ali,

    thanks for your assistance! It seems that I over simplified my case a bit, so that your approach doesn't work for me.
    There are few number values between the values to consider, which are not meant to be included at all.

    Here my updated example.


    Best wishes
    idlebert
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,409

    Re: Skipping results from IF within AVERAGE

    Oh, ye of little faith!!!

    =AVERAGEIFS($B$2:$B$33,$B$3:$B$34,"<>X",$A$2:$A$33,"")

    That is, until you shift the goalposts again ...

  6. #6
    Registered User
    Join Date
    03-04-2020
    Location
    Cologne
    MS-Off Ver
    Office 365 (Win 10 - Work)
    Posts
    12

    Re: Skipping results from IF within AVERAGE

    So true, I ought clearly work on my example skills

    Here is a almost full representation of my sheet. Your last attempt doesn't work cause in the column left of the average area could also be an X (when December is filtered out).
    Attached Files Attached Files
    Last edited by AliGW; 05-28-2020 at 07:36 AM. Reason: Please don't quote unnecessarily!

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,409

    Re: Skipping results from IF within AVERAGE

    Right, now this is starting to move in a different direction!

    I really HATE it when members drip feed information in this way - it is SO frustrating. Sorry!

    What you need to do now is add some worked examples to your workbook (manually calculated). Put them where you want them and explain how they should change given all possible scenarios.

    Then I'll have another look.

    Don't keep telling me that my solution doesn't work - it worked perfectly for each scenario as YOU PRESENTED IT.

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

  8. #8
    Registered User
    Join Date
    03-04-2020
    Location
    Cologne
    MS-Off Ver
    Office 365 (Win 10 - Work)
    Posts
    12

    Re: Skipping results from IF within AVERAGE

    Hey Ali,

    it was not my intent to hold back important information which were necessary to solve my issue efficiently. I just wanted to condense my problem with as many information as needed - but I failed indeed
    I'm truly thankful for every second you spend on my problem!

    Just for the record: In my Excel there was still a slight difference between the target value and your formular as shown in the attachment.

    In order to give you full information (which I thought was not necessary in the beginning) I gladly provide a less reduced version of my sheet. I'm incredibly excited about your next approach, as I guess it's probably a no brainer for you
    Attached Images Attached Images
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,409

    Re: Skipping results from IF within AVERAGE

    Firstly, explain WHY the correct result should be what you have suggested. What is the calculation you are making and which cells should be included?

  10. #10
    Registered User
    Join Date
    03-04-2020
    Location
    Cologne
    MS-Off Ver
    Office 365 (Win 10 - Work)
    Posts
    12

    Re: Skipping results from IF within AVERAGE

    7445,7 = (7028,48+7810,04+7498,6)/3. The 2nd and 3rd bold values are filtered out as the "X" indicates. I want to calculate the average of all bold values without an X underneath. Just have a look into my new sheet and you will understand easily!

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,409

    Re: Skipping results from IF within AVERAGE

    So in the real workbook, what will appear where you have written Attribut5? Will it be rather same for all total rows. We need an Identifying feature - emboldened text isnít recognised by formulae.

    My second formula will work with a bit of array tweaking.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,409

    Re: Skipping results from IF within AVERAGE

    This produces the result you say you want:

    =AVERAGEIFS($Q$8:$Q$40,$Q$9:$Q$41,"<>X",$C$8:$C$40,"Attribut5")

    Please note that as before, the SECOND array needs offsetting by one row (start AND end) - it will not work if you donít set this correctly.
    Last edited by AliGW; 05-28-2020 at 11:44 AM.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,409

    Re: Skipping results from IF within AVERAGE

    I've added the formula to your workbook - hopefully this will now be indisputable!
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    03-04-2020
    Location
    Cologne
    MS-Off Ver
    Office 365 (Win 10 - Work)
    Posts
    12

    Re: Skipping results from IF within AVERAGE

    Aww maaan, I could've smelled the rat by myself after your last hint

    Many thanks and wonderful day!

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,409

    Re: Skipping results from IF within AVERAGE

    Kein Problem - schŲnen Abend noch!

+ 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. Average a row and skipping columns
    By MLGosselin in forum Excel General
    Replies: 1
    Last Post: 10-21-2015, 03:50 PM
  2. IF formula skipping FALSE cells in results
    By BDTupp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-08-2015, 12:24 AM
  3. Replies: 5
    Last Post: 10-21-2013, 10:56 AM
  4. Vlookup skipping previous results
    By J_Le4 in forum Excel General
    Replies: 1
    Last Post: 11-22-2011, 10:34 PM
  5. return results without skipping lines.
    By mjhopler in forum Excel General
    Replies: 2
    Last Post: 07-13-2010, 04:01 AM
  6. how to write an average function when skipping rows?
    By novice2430 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-21-2008, 04:26 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