•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
Tagged with
#rows.com
#AI formula generation techniques
#Excel compatibility
#Excel alternatives for data analysis
#formula generator
#Excel alternatives
#AGGREGATE
#AVERAGE
##VALUE!
#IFERROR
#ignore
#G4:G15
#0s
#formula
#Excel
#column
#error handling
#spreadsheet
#range
#cells