Results 1 to 2 of 2

Thread: Sum Product formulae to ignore zeros and #DIV0! error

  1. #1
    Seeker Kihoro's Avatar
    Join Date
    Sep 2012
    Location
    Nairobi, Kenya, Kenya
    Posts
    6
    Articles
    0

    Sum Product formulae to ignore zeros and #DIV0! error



    Register for a FREE account, and/
    or Log in to avoid these ads!

    hi guys
    i have a formulae that gets the sums the sum of the last ANY three numbers in row and divides by three.
    however that row also contains a zero and #DIV/0! error referenced from calculation from other cells in the worksheets.
    i want the formula to ignore the zero and the #DIV/0! and just look at the last 3 numbers, get their sum and divide by 3
    please help.....!
    below is the formulae am using

    =SUMPRODUCT((H18:CF18*((MAX(COLUMN(H18:CF18)*(H18:CF18<>""))-COLUMN(H18:CF23))<3))/3)
    it looks at the numbers in the row h18:cf18 and picks out the last 3 and calculates their average. but returns an error if their a #DIV/0! error in the row.

    thank you in advance
    john

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Have you tried eliminating the #DIV/0! by adding an IFERROR( or IF(ISERROR( (if your Excel is pre 2007) function to the effected formulae ?

Posting Permissions

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