I Need Help with Average Formula I Need Assistance!!!

[Mike]

This is what i am trying to use but get Value or False errors & also tried as
an array

=IF(AR6="L",OR(AW6>""),AVERAGE(IF(AU$6:AU$66>=0,AU$6:AU$66),AU6,""))
This is what i need if AR6=L or AW6>"" then have to Average AU6:AU66 IF
AR6="" OR AW6="" I need to use value in AU6 then if cells are blank use ""

 

[rzink]

Mike,

I'm not 100% sure of what you are asking for, but I will point out
corrections that I see:

1) your OR argument is out of order. Try something like this:
=IF(OR(AR6="L",AW6>"")...

Also, what are you trying to do with [ AW6>"" ] ? Do you want your OR
argument to be TRUE if AW6 is greater than zero (0), then use [ AW6>0 ].

2) If any cell in the range of numbers that you are averaging is blank, it
will be exluded from the average calculation. If the cell is zero (0), it
will be included. So... if the purpose of the following: [
AVERAGE(IF(AU$6:AU$66>=0,AU$6:AU$66),AU6,"") ] is to average all of the
non-blank cells in your range AU6:AU66, the you can go with a much simpler
formula.

Try this: =IF(OR(AR6="L",AW6>0),AVERAGE(AU6:AU66),0)

If AR6 = "L" OR AW6>0, then AVERAGE range AU6:AU66, otherwise return zero (0)

If this doesn't work, please post back with more information and maybe a
simple example of your data set.

Ryan

 

[Joe User]

This is what i need if AR6=L or AW6>"" then have
to Average AU6:AU66 IF AR6="" OR AW6="" I
need to use value in AU6 then if cells are blank use ""

Your requirements do not make sense. But the following array formula [*]
might implement what you had in mind:

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

That will result in "" if AW6="" and AR6 is neither "L" nor "". It will
result in AU6 if AW6="" __and__ AR6=""; AW6="" is implied by failing the OR()
condition in the first part.

Alternatively, perhaps you meant AND(AR6="L",AW6<>"") in the first part, and
the second/third parts should be IF(OR(AR6="","AW6=""),AU6,""), as you wrote
in your English description.

[*] An array formula is entered by pressing ctrl+shift+Enter instead of just
Enter. You see curly braces around the entire formula in the Formula Bar,
i.e. {=formula}. Note that you cannot type the curly braces yourself. If
you make a mistake, select the cell, press F2 and edit as needed, then press
ctrl+shift+Enter.


----- original message -----