One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
=AVERAGE(IF((MOD(COLUMN(N13:IJ13),10)=4)*(N13:IJ13 0),N13:IJ13))
will only average values 0.
If you may have zero or negative values that should be averaged in, try
(also array-entered):
=AVERAGE(IF((MOD(COLUMN(N13:IJ13),10)=4)*(N13:IJ13 <""),N13:IJ13))
In article ,
Renee - California wrote:
I have a summarizing section of a worksheet in which I need to get an average
for a range of
cells:(N13+X13+AH13+AR13+BB13+BL13+BV13+CF13+CP13+ CZ13+DJ13+DT13+ED13+EN13+EX1
3+FH13+FR13+GB13+GL13+GV13+HF13+HP13+HZ13+IJ13)
I need to put a condition on it that the average is based on only the cells
with a value though. So I was thinking that I'd add on a countif condition
to get the number it divides by.
(N13+X13+AH13+AR13+BB13+BL13+BV13+CF13+CP13+CZ13+D J13+DT13+ED13+EN13+EX13+FH13
+FR13+GB13+GL13+GV13+HF13+HP13+HZ13+IJ13)/COUNTIF(N13,"0")
This only says if N13 is greater than zero, but I have all the other cells
that need be spedified as greater than zero as well. How do I do this
function??
|