# Average if two conditons are met

1. ## Average if two conditons are met

Hi

Can someone help me with the below query.

I have created two columns in excel (2003)

Columns 1: Has 4 options in a drop down list(Ex : option1, option 2, option 3, option 4)
Column 2: random Numbers including numbers less than 0

I would like to find the average ONLY if it satisfies both the below conditions
condition 1: If column 1 = "option1"
condition 2: if column 2 > 0

Can someone help me with a formula please?

2. ## Re: Average if two conditons are met

Hi,

Something along these lines?

=SUMPRODUCT((B1:B22)*(A1:A22="option 1")*(B1:B22>0))/SUMPRODUCT((A1:A22="option 1")*(B1:B22>0))

3. ## Re: Average if two conditons are met

Hi

I tried the formula in the excel , but it shows a msg that the formula contains an error. Can u pls help?

Thanks !

Originally Posted by sweep
Hi,

Something along these lines?

=SUMPRODUCT((B1:B22)*(A1:A22="option 1")*(B1:B22>0))/SUMPRODUCT((A1:A22="option 1")*(B1:B22>0))

4. ## Re: Average if two conditons are met

sweep's formula works OK for me, are you using exactly that formula or have you modified it?

5. ## Re: Average if two conditons are met

Hello

Yes I modified with my requirements and tried. The excel sheet where i need this formula has some problem maybe thats why it didnt work. I tried it in a separate excel sheet now , it works..thanks for it. But unfortunately the answer didnt turn out to be the way i want as it finds average including the negative numbers.

The input given were 1,2,3,4,-1,-2,-3

The answer i expect is 2.5 adding 1,2,3,4 = 10 by 4

But here it adds all the numbers and divides only by the count of the positive numbers

4/4 =1

can u clarify this pls?

Thanks for ur quick response!

sweep's formula works OK for me, are you using exactly that formula or have you modified it?

6. ## Re: Average if two conditons are met

Again, sweep's formula works OK for me - for that example I get a result of 2.5

7. ## Re: Average if two conditons are met

Again, sweep's formula works OK for me - for that example I get a result of 2.5
Hi

sorry to ask you again but I have attached the sheet i worked on. I still get the same answer. Can u pls take a look at it

Thanks!

8. ## Re: Average if two conditons are met

Originally Posted by shilloh
Hi

sorry to ask you again but I have attached the sheet i worked on. I still get the same answer. Can u pls take a look at it

Thanks!
Hello

I tried it works fine when i give option 1 only for input : 1,2,3,4 (the ans is 2.5)but when i give option 1 for input -1,-2,-3 the answer is 1. When option 1 is given with a corresponding negative value , it shd ignore the negative number even though it has "option 1". This is my actual query. Can you help me please?

9. ## Re: Average if two conditons are met

You haven't exactly replicated sweep's formula. Both SUMPRODUCTs need to check theat B1:B7 > 0 (and you have a misplaced parenthesis so the first SUMPRODUCT isn't properly finished). Formula should be like this

=SUMPRODUCT((B1:B7)*(A1:A7="option 1")*(B1:B7>0))/SUMPRODUCT((A1:A7="option 1")*(B1:B7>0))

You could also get the same result with an "array formula" using AVERAGE, i.e.

=AVERAGE(IF(A1:A7="option 1",IF(B1:B7>0,B1:B7)))

but this formula needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

10. ## Re: Average if two conditons are met

You haven't exactly replicated sweep's formula. Both SUMPRODUCTs need to check theat B1:B7 > 0 (and you have a misplaced parenthesis so the first SUMPRODUCT isn't properly finished). Formula should be like this

=SUMPRODUCT((B1:B7)*(A1:A7="option 1")*(B1:B7>0))/SUMPRODUCT((A1:A7="option 1")*(B1:B7>0))

You could also get the same result with an "array formula" using AVERAGE, i.e.

=AVERAGE(IF(A1:A7="option 1",IF(B1:B7>0,B1:B7)))

but this formula needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar
Wow Great! super !Thank you so much!!

It works. :-)

Your quick response is so much appreciated!!

11. ## Re: Average if two conditons are met

Hello

I have actually attached the file where im trying the same formula, I end up getting a #Value error. Can you tell me why it occurs please?

Average is computed in line number 54

Originally Posted by shilloh
Wow Great! super !Thank you so much!!

It works. :-)

Your quick response is so much appreciated!!

12. ## Re: Average if two conditons are met

You need to confirm the formula with CTRL+SHIFT+ENTER not just ENTER.

So, hit F2 while in that cell, and hold the CTRL and SHIFT keys down, then press ENTER. You will see { } brackets appear around the formula and you should see a numeric result.

13. ## Re: Average if two conditons are met

Originally Posted by NBVC
You need to confirm the formula with CTRL+SHIFT+ENTER not just ENTER.

So, hit F2 while in that cell, and hold the CTRL and SHIFT keys down, then press ENTER. You will see { } brackets appear around the formula and you should see a numeric result.
Oops i missed that part!

Great, it works fine for me. Thankyou again!! :-)

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