# Attempt to find a conditional average with an array formula.

1. ## 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!  Register To Reply

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

Try:

=AVERAGEIF(A1:A5,"ACTIVE",B1:B5)  Register To Reply

3. ## 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!!  Register To Reply

4. ## 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.  Register To Reply

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

You're a genius!  Register To Reply