Sum Product formulae to ignore zeros and #DIV0! error

Kihoro

New member
Joined
Sep 19, 2012
Messages
6
Reaction score
0
Points
0
Location
Nairobi, Kenya, Kenya
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
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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 ?
 
Top