help with statements

[jmazorra]

Hello

I am having trouble with a formula, Basically it is supposed to solve the following statement:

If k1:k6 = "E" and if r1:r6>=1 then average b1:b6 and if the answer is 0 return a value of N/A

The other statement I am have trouble with is similar and it goes like this:

if k1:k6 = "E" and if and if m1:m6="f" and if r1:r6>=1.0 then average b1:b6 and return a value of N/a if the answer is 0

Any help will be appreciated

Thanks

 

[Robert Mika]

First question:

=IF(ISERROR(AVERAGE(IF($K$1:$K$6="E",IF($R$1:$R$6>=1,B1:B6)))=0),NA(),AVERAGE(IF($K$1:$K$6="E",IF($R$1:$R$6>=1,B1:B6))))

 

[T. Valko]

Hello

I am having trouble with a formula, Basically it is supposed to solve the following statement:

If k1:k6 = "E" and if r1:r6>=1 then average b1:b6 and if the answer is 0 return a value of N/A

The other statement I am have trouble with is similar and it goes like this:

if k1:k6 = "E" and if and if m1:m6="f" and if r1:r6>=1.0 then average b1:b6 and return a value of N/a if the answer is 0

Any help will be appreciated

Thanks

Try these...

If you're using Excel 2007 or later...

=IF(AVERAGEIFS(B1:B6,K1:K6,"E",R1:R6,">=1"),AVERAGEIFS(B1:B6,K1:K6,"E",R1:R6,">=1"),"N/A")

=IF(AVERAGEIFS(B1:B6,K1:K6,"E",M1:M6,"F",R1:R6,">=1"),AVERAGEIFS(B1:B6,K1:K6,"E",M1:M6,"F",R1:R6,">=1"),"N/A")

These array formulas** will work in ALL versions of Excel:

=IF(AVERAGE(IF(K1:K6="E",IF(R1:R6>=1,B1:B6))),AVERAGE(IF(K1:K6="E",IF(R1:R6>=1,B1:B6))),"N/A")

=IF(AVERAGE(IF(K1:K6="E",IF(M1:M6="F",IF(R1:R6>=1,B1:B6)))),AVERAGE(IF(K1:K6="E",IF(M1:M6="F",IF(R1:R6>=1,B1:B6)))),"N/A")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[shg]

Another way:

=IFERROR(1/(1/AVERAGEIFS(B1:B6,K1:K6,"E",R1:R6,">=1")), NA())

 

[T. Valko]

Another way:

=IFERROR(1/(1/AVERAGEIFS(B1:B6,K1:K6,"E",R1:R6,">=1")), NA())

I like that one if they're using Excel 2007 or later. I don't know if they want the TEXT string N/A or the logical error value #N/A, though.

Nice one!

 

[jmazorra]

Thank you all, that worked perfect

 

[T. Valko]

Thank you all, that worked perfect

You're welcome. We appreciate the feedback!