Hows the best way to exclude holidays, Yet still get an Average

[JamieLee2k]

So here is my issue, If I was to have a holiday say on Friday, whats the best way to exclude that day from the total average at the end of the week but still get a percentage like I would have if I did a full week.

Excel Workbook
	AC	AD	AE	AF	AG	AH
2	Total	Diag	MRB	Holiday	DMI	Passed
3						Overall
4	0	0	0	0	0	0%
5	0	0	0	0	0	0%
6	0	0	0	0	0	0%
7	0	0	0	0	0	0%
8	0	0	0	14	0	100%
9						20%
Overall Posting

Excel 2010

Cell Formulas
Range	Formula
AC4	=SUM(C4:Z4)
AC5	=SUM(C5:Z5)
AC6	=SUM(C6:Z6)
AC7	=SUM(C7:Z7)
AC8	=SUM(C8:Z8)
AD4	=SUM(Monday!I3)
AD5	=SUM(Tuesday!I3)
AD6	=SUM(Wednesday!I3)
AD7	=SUM(Thursday!I3)
AD8	=SUM(Friday!I3)
AE4	=SUM(Monday!J54)
AE5	=SUM(Tuesday!J54)
AE6	=SUM(Wednesday!J54)
AE7	=SUM(Thursday!J54)
AE8	=SUM(Friday!J54)
AF4	=IF(Monday!O2="H",15,0)
AF5	=IF(Tuesday!O2="H",15,0)
AF6	=IF(Wednesday!O2="H",15,0)
AF7	=IF(Thursday!O2="H",15,0)
AF8	=IF(Friday!O2="H",14,0)
AG4	=SUM(Monday!I54)
AG5	=SUM(Tuesday!I54)
AG6	=SUM(Wednesday!I54)
AG7	=SUM(Thursday!I54)
AG8	=SUM(Friday!I54)
AH4	=(AC4+AD4+AE4+AF4)/15*7.5/Monday!O3
AH5	=(AC5+AD5+AE5+AF5)/15*7.5/Tuesday!O3
AH6	=(AC6+AD6+AE6+AF6)/15*7.5/Wednesday!O3
AH7	=(AC7+AD7+AE7+AF7)/15*7.5/Thursday!O3
AH8	=(AC8+AD8+AE8+AF8)/15*7.5/Friday!O3
AH9	=AVERAGE(AH4:AH8)

 

[lokiluke]

try use the AVERAGEIF function

syntax is =AVERAGEIF(range to test for holidays here, method of test here, range to average here)

so if i get your data right it might be something like this:
=AVERAGEIF(AF4:AF8,"0",AC4:AC8)

this will look in AF4:AF8, if it has a zero value then it will average it, if it has any other value it will exclude it from the average

 

[JamieLee2k]

The way you have explained this is great but I am struggling to put this into practice, does it go into the total of each day or the total at the end of the week, if so how do I add this onto the end of the formula I currently have?

 

[lokiluke]

not sure exactly what youre after, do you want the average of the percentages as long as there value of the holiday column is zero?
if so try this

=AVERAGEIF(AF4:AF8,"0",AH4:AH8)

that formula should go in AH9, and AH9 should be formatted as a percentage.