1. Hi loungers....I am trying to calculate the average of non-adjacent cells (eg: D9,D17,D25,D33,D41,D49,D57,D65,D73,D81,D89,D97)... ..some of these cells will be empty or have a 0 value from time to time....I don't want to include those in the 'average'....I am trying to use this formula: =IF(AVERAGE(D9,D17,D25,D33,D41,D49,D57,D65,D73,D81 ,D89,D97)=0,0,IF(AVERAGE(D9,D17,D25,D33,D41,D49,D5 7,D65,D73,D81,D89,D97)<>0,AVERAGE(D9,D17,D25,D33,D 41,D49,D57,D65,D73,D81,D89,D97),"")) as an array formula (ctrl-shift-enter) but it won't work...it doesn't exclude cells that are empty or have a zero value....any ideas? Thanks.

2. Try:

=SUMPRODUCT(--(MOD(ROW(D997)-9,8)=0),D997)/SUMPRODUCT(--(MOD(ROW(D997)-9,8)=0),--(D997<>0))

3. This works great Rory...thank you....I think that I understand the formula, altho I don't know the significance of the 8...is it the number of rows b/w the 'non-adjacent' rows ??.??...thank you again

4. Yep.

5. ...ps: forgot to ask, but what is the significance of the - - in the formulae?

6. It coerces the TRUE/FALSE values to 1 and 0 for summing. You can use any mathematical operation like *1, or +0 but -- is slightly more efficient. (If there were multiple conditions, you could just multiply the two sets of booleans)

7. ..OK...thanks for that....and for your help.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•