I'm working on a spreadsheet that will automatically plot data that will
continue to grow. The way we have it set up right now is:

- there is a table where the data (hours) are manually entered
- the next table is week-to-date percentages using the following formula:
=IF(ISERROR(AVERAGE(IF(B20:E20<0,B20:E20,""))),"0 .00%",(AVERAGE(IF(B20:E20<0,B20:E20,""))))
- the following table will calculate the month-to-date, quarter-to-date, and
year-to-date information
- all of the cells have a formula in them expecting to automatically return
a value once the weekly data has been entered

My problem is if one of the consultants go on holiday, a 0.00% is listed for
them in that week and is not averaged in to their growing totals on a
month-to-date basis.

How do I make it so that the 0.00%'s are calculated only from the weeks of
06/01/08, 13/01/08, 20/01/08, etc. without it calculating the 0.00% in the
weeks to come?

Additionally, how do I plot the 0.00% on a graph without the others being
used?

For the graph, we have named the XValues and YValues as such:
XValues =OFFSET('Utilization Prototype 020808.xls'!YValues,-1,0)
YValues =OFFSET(Data!$B$16,0,0,1, COUNTIF(Data!$16:$16,"0"))

We entered the Values in the Source Data as ='Utilization Prototype
020808.xls'!YValues, and the Category (x) Axis Labels as ='Utilization
Prototype 020808.xls'!XValues

This works, in theory, but it is not plotting the 0.00%'s from the
subsequent weeks. Any suggestions?