Averaging every 5th cell while omitting zeros
in [Functions]
|
From: Erika on 10 Mar 2010 16:09 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.
From: Mike H on 10 Mar 2010 16:31 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,22,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,167,172,177,182,187,192,197,202,207,212,217,222,227,232,237,242,247,252,257},0))*(A7:A272>0),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.
From: Bernard Liengme on 10 Mar 2010 18:02 An alternative to Mike's solution =SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A272<>0),A7:A272)/SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A272<>0)) 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:A272<>0) will exclude zeros but include negative values Change it to --(A7:A272>0) to include only positive non-zero numbers best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Erika" <Erika(a)discussions.microsoft.com> wrote in message news:EED627C5-A1E4-41FC-B813-AECCDF436BCB(a)microsoft.com... > 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.
From: T. Valko on 10 Mar 2010 18:50 Here's another one... Array entered** : =AVERAGE(IF(MOD(ROW(A7:A272)-ROW(A7),5)=0,IF(A7:A272<>0,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" <Erika(a)discussions.microsoft.com> wrote in message news:EED627C5-A1E4-41FC-B813-AECCDF436BCB(a)microsoft.com... > 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.
From: Mike H on 10 Mar 2010 19:03 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:A272<>0),A7:A272)/SUMPRODUCT(--(MOD(ROW(A7:A272),5)=2),--(A7:A272<>0)) > 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:A272<>0) will exclude zeros but include negative values > Change it to --(A7:A272>0) to include only positive non-zero numbers > best wishes > -- > Bernard Liengme > Microsoft Excel MVP > http://people.stfx.ca/bliengme > > "Erika" <Erika(a)discussions.microsoft.com> wrote in message > news:EED627C5-A1E4-41FC-B813-AECCDF436BCB(a)microsoft.com... > > 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. > > . >
|
Next
|
Last
Pages: 1 2 Prev: SUMPRODUCT and Dates Next: Formating cell by Comparing two cells for value |