+ Reply to Thread
Results 1 to 4 of 4

Formula to calculate an average based on conditional values

  1. 03-23-2017, 12:09 AM #1
    flashiel
    flashiel is offline
    Registered User
    Join Date
    08-31-2015
    Location
    montreal,canada
    MS-Off Ver
    MS office 2007
    Posts
    11

    Formula to calculate an average based on conditional values

    Hi, im trying to find the formula that would allow me to calculate an average which includes only a partial set of data based on a conditional value of a different set of data.

    72559650acbc3b4afbb06c0ba85f840fafc91500667a3541939b2cfbc0c1b1d9922ce96b.jpg

    so, here i would like to compute the average value of Column D but while only including a particular value if it is associated with a positive (and greater than zero) value with Column E.

    so, for instance in this example, i would like a formula to compute the average of those 5 values: 1 // 0.5 // 0 // 1.11 and 2.61

    Please help
    Reply With QuoteRegister To Reply
  2. 03-23-2017, 12:15 AM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Formula to calculate an average based on conditional values

    The function would be AVERAGEIF since there is only one criteria:

    =AVERAGEIF($E$2:$E$13, ">0", $D$2:$D$13)
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 03-23-2017, 12:30 AM #3
    flashiel
    flashiel is offline
    Registered User
    Join Date
    08-31-2015
    Location
    montreal,canada
    MS-Off Ver
    MS office 2007
    Posts
    11

    Re: Formula to calculate an average based on conditional values

    That was fast, and on point!

    67878914e051a4cfa7dbac87a0d7f52f3c11e7fa83360055db800f318ef450adc6ecaef2.jpg
    Reply With QuoteRegister To Reply
  4. 03-23-2017, 12:37 AM #4
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Formula to calculate an average based on conditional values

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to calculate sum/average based on number of unique dates in range
    By JennOlsen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-19-2014, 06:47 PM
  2. How to create a formula in conditional formatting based on average of cells.
    By rjassal in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-25-2013, 01:35 PM
  3. Need to calculate the average for a column, based on values in two other columns
    By mattr86 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-12-2012, 11:26 AM
  4. Calculate average based on matching values from two worksheets
    By FM1 in forum Excel General
    Replies: 10
    Last Post: 01-22-2010, 01:38 PM
  5. Attempting to calculate an average based on multiple criteria using an array formula
    By RichardL in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-21-2008, 12:09 PM
  6. create a formula to look to calculate average payment based on rep and date
    By EddieMaher in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-21-2008, 03:55 PM
  7. I need a formula to calculate average values by day date
    By tdraa in forum Excel - New Users/Basics
    Replies: 16
    Last Post: 12-04-2006, 04:56 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1