Exclude Null Field in Formula

  • Thread starter Thread starter Annabelle
  • Start date Start date
A

Annabelle

Goal: Display accurate overall percentage complete.
Detail: I have a master field which should display an
overall percentage complete, collected from six different
sections. I'm using this formula which is accurate - -

=SUM(S5+X5+AC5,AH5,AM5,AR5)/6

Problem: Although most often I will be pulling data from
all six fields, there are occations when it is appropriate
for only three or four of these sub sections. How can I
exclude null fields?

Example: Using the formula above, if five fields are
entered as 100% but the sixth area is null (field is
blank), then my overall percentage is 83%; it needs to be
100%.
 
=IF(ISERROR(AVERAGE(S5,X5,AC5,AH5,AM5,AR5)),0,AVERAGE(S5,X5,AC5,AH5,AM5,AR5))
to give 0 when all cells empty

or

=IF(ISERROR(AVERAGE(S5,X5,AC5,AH5,AM5,AR5)),"",AVERAGE(S5,X5,AC5,AH5,AM5,AR5))
to give a blank if all cells empty

or if there will never be an instance where all cells are empty, you can have just:-

=AVERAGE(S5,X5,AC5,AH5,AM5,AR5)
 
Very helpful. Thank you.
-----Original Message-----
=IF(ISERROR(AVERAGE(S5,X5,AC5,AH5,AM5,AR5)),0,AVERAGE (S5,X5,AC5,AH5,AM5,AR5))
to give 0 when all cells empty

or

=IF(ISERROR(AVERAGE(S5,X5,AC5,AH5,AM5,AR5)),"",AVERAGE (S5,X5,AC5,AH5,AM5,AR5))
to give a blank if all cells empty

or if there will never be an instance where all cells are empty, you can have just:-

=AVERAGE(S5,X5,AC5,AH5,AM5,AR5)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

---------------------------------------------------------- ------------------
It's easier to beg forgiveness than ask permission
---------------------------------------------------------- ------------------






.
Click to expand...
 
Back
Top