Average if two conditons are met

Hi

Appreciate your response.

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

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!

Originally Posted by daddylonglegs

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

Originally Posted by daddylonglegs

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!

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?

Your help is greatly appreciated!

Originally Posted by daddylonglegs

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

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

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