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

1. ## Sum Product formulae to ignore zeros and #DIV0! error

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
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.

john  Reply With Quote

2. 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 ?  Reply With Quote

#### Posting Permissions

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