countif
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+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+D J13+DT13+ED13+EN13+EX13+FH13+FR13+GB13+GL13+GV13+H F13+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?? |
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?? |
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" wrote: 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?? |
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,. In article , Renee - California wrote: 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" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((MOD(COLUMN(N13:IJ13),10)=4)*(N13:IJ13 0),N13:IJ13)) |
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! "JE McGimpsey" wrote: 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,. In article , Renee - California wrote: 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" wrote: One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF((MOD(COLUMN(N13:IJ13),10)=4)*(N13:IJ13 0),N13:IJ13)) |
All times are GMT +1. The time now is 02:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com