# 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!

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

Try:

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

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!!

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.

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!

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

#### 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