+ Reply to Thread
Results 1 to 5 of 5

Attempt to find a conditional average with an array formula.

  1. #1
    Registered User
    Join Date
    11-15-2010
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2010
    Posts
    5

    Attempt to find a conditional average with an array formula.

    Hi guys, I'm at my wits end, and I just can't figure out how to make this work.

    Here's the problem example:

    Column A lists the ACTIVE / INACTIVE state.
    This will be the criteria that determines which values to use produce the average.

    Column B lists the values to be averaged.

    Example:
    A1: ACTIVE , B1: 10 -> A1: INACTIVE , B1: 10
    A2: ACTIVE , B2: 20 -> A2: ACTIVE , B2: 20
    A3: INACTIVE , B3: 30 -> A3: ACTIVE , B3: 30
    A4: ACTIVE , B4: 40 -> A4: ACTIVE , B4: 40
    A5: INACTIVE, B5: 50 -> A5: INACTIVE, B5: 50

    So for example, in this case, it would average 10, 20, 40 as 23.33.
    However, if i want it so that when I switch the active state, it would automatically calculate the new averages, in this scenario, it would then be, 20, 30, 40 as 30.

    If possible, I would like a formula that can be flexible enough so that I can add more to this list.

    I have tried to look up all over the place for answers but none of the places that I looked on had a problem similar to mine yet. I hope that I would be able to get some answers here.

    All help very much appreciated!

    P.S. Also if I can make the ACTIVE and INACTIVE into an easy toggle button that would be nice, too!
    Last edited by Illumina; 03-17-2011 at 09:05 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Attempt to find a conditional average with an array formula.

    Try:

    =AVERAGEIF(A1:A5,"ACTIVE",B1:B5)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    11-15-2010
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Attempt to find a conditional average with an array formula.

    Ah, thank you for the prompt reply.

    Yes, this does solve my problem very concisely.
    Thanks! Though I will mark this thread as [Solved], still, I had hoped for a more versatile solution. One that would work with other complicated functions other than just an average.

    Nonetheless, my immediate concern is resolved. Thank you!!

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Attempt to find a conditional average with an array formula.

    If you mean like with MIN, MAX, LARGE, SMALL, STDEV, etc...

    then something like:

    =MIN(IF(A1:A5="ACTIVE",B1:B5))

    confirmed with CTRL+SHIFT+ENTER

    replace the function as needed.. this works with many functions, but it is an array formula and therefore less efficient then the standard AVERAGEIF type functions.... so use liberally and with tight ranges.

  5. #5
    Registered User
    Join Date
    11-15-2010
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Attempt to find a conditional average with an array formula.

    Oh! Silly me!

    Haha, I was attempting to find the total probability of occurrence given by a particular state, and with your last advice, I finally concluded with:

    =PRODUCT(IF(L14:L60="Active",(H14:H60/100)+1,""))-1

    And with your advice, it works like a charm!
    You're a genius!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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