Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    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. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Try:

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




    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    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. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Yep.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    ...ps: forgot to ask, but what is the significance of the - - in the formulae?

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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)
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    ..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
  •