If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Averaging every 5th cell while omitting zeros
Greetings! Thank you for your interest in my question, I have been bashing my
head in trying to figure it out. In a column, I want to add every FIFTH cell starting with row 7 and ending with row 272. Meanwhile, I need to exclude all the cells with zero so the averaging only divides by the number of cells with a numeral. I have tried entering each 5th row individually in various formulas and I have tried defining a name and using that in the formulas but nothing has worked, I keep getting an error each time. I would really appreciate any help! Thank you. |
#2
|
|||
|
|||
Averaging every 5th cell while omitting zeros
Erica,
I don't like this formula but it works until someone comes up with something better. It's an ARRAY see below on how to enter it =AVERAGE(IF(ISNUMBER(MATCH(ROW(A7:A272),{7,12,17,2 2,27,32,37,42,47,52,57,62,67,72,77,82,87,92,97,102 ,107,112,117,122,127,132,137,142,147,152,157,162,1 67,172,177,182,187,192,197,202,207,212,217,222,227 ,232,237,242,247,252,257},0))*(A7:A2720),A7:A272) ) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Erika" wrote: Greetings! Thank you for your interest in my question, I have been bashing my head in trying to figure it out. In a column, I want to add every FIFTH cell starting with row 7 and ending with row 272. Meanwhile, I need to exclude all the cells with zero so the averaging only divides by the number of cells with a numeral. I have tried entering each 5th row individually in various formulas and I have tried defining a name and using that in the formulas but nothing has worked, I keep getting an error each time. I would really appreciate any help! Thank you. |
#3
|
|||
|
|||
Averaging every 5th cell while omitting zeros
An alternative to Mike's solution
=SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A2720),A7:A272)/SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A2720)) This is NOT an array formula I tested it with some data. I used this formula and some helper columns- got the same answer so I have faith init Note the test --(A7:A2720) will exclude zeros but include negative values Change it to --(A7:A2720) to include only positive non-zero numbers best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Erika" wrote in message ... Greetings! Thank you for your interest in my question, I have been bashing my head in trying to figure it out. In a column, I want to add every FIFTH cell starting with row 7 and ending with row 272. Meanwhile, I need to exclude all the cells with zero so the averaging only divides by the number of cells with a numeral. I have tried entering each 5th row individually in various formulas and I have tried defining a name and using that in the formulas but nothing has worked, I keep getting an error each time. I would really appreciate any help! Thank you. |
#4
|
|||
|
|||
Averaging every 5th cell while omitting zeros
Here's another one...
Array entered** : =AVERAGE(IF(MOD(ROW(A7:A272)-ROW(A7),5)=0,IF(A7:A2720,A7:A272))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note that this formula is robust against row insertions *before* the referenced range. However, if you insert new rows *within* the referenced range then that will change the interval and the formula may return an incorrect result. -- Biff Microsoft Excel MVP "Erika" wrote in message ... Greetings! Thank you for your interest in my question, I have been bashing my head in trying to figure it out. In a column, I want to add every FIFTH cell starting with row 7 and ending with row 272. Meanwhile, I need to exclude all the cells with zero so the averaging only divides by the number of cells with a numeral. I have tried entering each 5th row individually in various formulas and I have tried defining a name and using that in the formulas but nothing has worked, I keep getting an error each time. I would really appreciate any help! Thank you. |
#5
|
|||
|
|||
Averaging every 5th cell while omitting zeros
Bernard,
While that works I think the problem is it will treat text as a numeric value of zero and include it in the average and I was striving for a more bullet proof answer. Now given the OP's data is probably numeric my criticism is probably not valid but I still think there's a better solution but I'm going to bed. Regards, -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Bernard Liengme" wrote: An alternative to Mike's solution =SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A2720),A7:A272)/SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A2720)) This is NOT an array formula I tested it with some data. I used this formula and some helper columns- got the same answer so I have faith init Note the test --(A7:A2720) will exclude zeros but include negative values Change it to --(A7:A2720) to include only positive non-zero numbers best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Erika" wrote in message ... Greetings! Thank you for your interest in my question, I have been bashing my head in trying to figure it out. In a column, I want to add every FIFTH cell starting with row 7 and ending with row 272. Meanwhile, I need to exclude all the cells with zero so the averaging only divides by the number of cells with a numeral. I have tried entering each 5th row individually in various formulas and I have tried defining a name and using that in the formulas but nothing has worked, I keep getting an error each time. I would really appreciate any help! Thank you. . |
#6
|
|||
|
|||
Averaging every 5th cell while omitting zeros
How about making the numerator
=SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A2720),--(ISNUMBER(A7:A272))) Seems to work on my test data Bernard "Mike H" wrote in message ... Bernard, While that works I think the problem is it will treat text as a numeric value of zero and include it in the average and I was striving for a more bullet proof answer. Now given the OP's data is probably numeric my criticism is probably not valid but I still think there's a better solution but I'm going to bed. Regards, -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Bernard Liengme" wrote: An alternative to Mike's solution =SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A2720),A7:A272)/SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A2720)) This is NOT an array formula I tested it with some data. I used this formula and some helper columns- got the same answer so I have faith init Note the test --(A7:A2720) will exclude zeros but include negative values Change it to --(A7:A2720) to include only positive non-zero numbers best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Erika" wrote in message ... Greetings! Thank you for your interest in my question, I have been bashing my head in trying to figure it out. In a column, I want to add every FIFTH cell starting with row 7 and ending with row 272. Meanwhile, I need to exclude all the cells with zero so the averaging only divides by the number of cells with a numeral. I have tried entering each 5th row individually in various formulas and I have tried defining a name and using that in the formulas but nothing has worked, I keep getting an error each time. I would really appreciate any help! Thank you. . |
Thread Tools | |
Display Modes | |
|
|