countif

[Guest]

I have a summarizing section of a worksheet in which I need to get an average
for a range of
cellsN13+X13+AH13+AR13+BB13+BL13+BV13+CF13+CP13+CZ13+DJ13+DT13+ED13+EN13+EX13+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+DJ13+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??

 

[JE McGimpsey]

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

 

[Guest]

Thanks for the feedback and help.

I've plugged this in and I'm getting #Value! return. Any ideas on why?
Also, what does the =4 signify is this formula?

Thanks again for your help.

 

[JE McGimpsey]

It's probably because you didn't hold down CTRL and SHIFT when you hit
ENTER.

Since your first column is the 14th ("N"), MOD(COLUMN(N),10) = 4.
Subsequent columns also have this property since you're averaging every
10th column,.

 

[Guest]

Thanks for the clarification. Yes, I originally entered instead of
Control/Shift and enter.

Now I'm getting #Div/0, and what's weird about that is, I put in a value at
every cell to test.

I'm baffled. Any other insight you could give would be much appreciated.

Thanks!