•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Average cells ignoring both 0s and #VALUE!
I am trying to create a formula to ignore both 0s and #VALUE!. My G4-G15 column has 0-10 and a #VALUE!. I have tried the below and any help is appreciated.
=AGGREGATE (1,6,(G4:G15*(G4:G15<>0)))
=AGGREGATE (1,6,(G4:G15/(G4:G15<>0)))
=IFERROR(AVERAGE(G4:G15<>0),G4:G15)
=IFERROR(AVERAGE(G4:G15<>0),"")
=AGGREGATE (1,7,(G4:G15*(G4:G15<>0)))
=AGGREGATE (1,7,(G4:G15/(G4:G15<>0)))
[link] [comments]
Want to read more?
Check out the full article on the original site