Trying To Average cells Need Help!!!

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

This is what i have & works well
{=IF(AR6="L",AVERAGE(IF(AT$6:AT$66>=0,AT$6:AT$66)),"")}
Now what im trying to do is add AW6 IF name is entered
I tried these but not working
{=IF(AR6="L",IF(AW6>"",AVERAGE(IF(AT$6:AT$66>=0,AT$6:AT$66)),"")}
{=IF(AR6="L",OR(IF(AW6>"",AVERAGE(IF(AT$6:AT$66>=0,AT$6:AT$66)),"")}
I get a Name or False error( CAN SOMEONE PLEASE HELP)
 
This is what i have & works well
{=IF(AR6="L",AVERAGE(IF(AT$6:AT$66>=0,AT$6:AT$66)),"")}
Now what im trying to do is add AW6 IF name is entered
I tried these but not working
{=IF(AR6="L",IF(AW6>"",AVERAGE(IF(AT$6:AT$66>=0,AT$6:AT$66)),"")}
{=IF(AR6="L",OR(IF(AW6>"",AVERAGE(IF(AT$6:AT$66>=0,AT$6:AT$66)),"")}
I get a Name or False error( CAN SOMEONE PLEASE HELP)
Click to expand...


Try this array formula:

=IF(OR(AR6="L",AW6<>""),AVERAGE(IF(AT$6:AT$66>=0,AT$6:AT$66)),"")

Hope this helps / Lars-Åke
 
Your formula
{=IF(AR6="L",IF(AW6>"",AVERAGE(IF(AT$6:AT$66>=0,AT$6:AT$66)),"")}
should do the average when BOTH conditions are true AR6="L" and AW6<>""
I put some numbers in A1:A7, "L" in C1 and "x" in D1
Then I used =IF(C1="L",IF(D1>"",AVERAGE(IF(A1:A7>=0,A1:A7)),""))
and it worked just fine

Normally to use two conditions in an IF we use the AND function:
=IF(AND(AR6="L",AW6<>""), do_this, do_that)
Bet Boolean function (AND, OR . NOT) are not allowed in array functions
The alternative to AND is to multiply the two test, while the alternative to
OR is to add then
So this should work for you
{=IF((AR6="L")*(AW6>"")),AVERAGE(IF(AT$6:AT$66>=0,AT$6:AT$66)),"")}
if you want BOTH conditions to be true (AND)
or
{=IF((AR6="L")+(AW6>"")),AVERAGE(IF(AT$6:AT$66>=0,AT$6:AT$66)),"")}
if you want EITHER conditions to be true (OR)

When I look at your second formula, I wonder if you mean "do the average if
EITHER condition is true

best wishes
 
I erred: the non-use of AND/OF does not apply the in this case
{=IF(OR(AR6="L", AW6>""),AVERAGE(IF(AT$6:AT$66>=0,AT$6:AT$66)),"")}
is fine
You could not use AND/OR in the IF within the AVERAGE
Sorry!
Bernard
 
Back
Top