Average even-numbered rows, ignoring #REF! errors

EDIT: even or odd numbered rows, it doesn't matter as I need to do both!

Hey guys,

I'd describe myself as slightly more advanced than beginner when it comes to Excel, but not much...so please bear with me!

I have just begun managing a bar and would like to analyse our sales a little better. Every night we complete a shift report on a spreadsheet with lots of info including the daily sales. We create a different workbook every day, and I have collated all of that information in one analysis workbook using external references to the daily spreadsheets. The analysis workbook runs on a 13 week basis so that we have 4 throughout the year. Obviously I'd like to analyse this data to some detail, including averages, but this is were the problem arises.

I have completed the external references for the whole 13 week period across the spreadsheet to save having to copy, paste and edit the external references every day to find the right information, but as there is no source workbook for a day that we have not completed a shift report for yet, these external references return a #REF! error. I have created a conditional format to colour the font of any #REF! error white, purely for visual reasons so this in itself isn't a problem, but I am struggling to find a formula to average the data that I'd like because some of the cells that the formula references, return #REF! errors.

I'm hoping there is a way of ignoring #REF! errors in a formula to find averages - the only other spanner in the works is that the data I am trying to average falls on every other row in a column. So for instance Sunday's sales are in columns D and E (morning shift and evening shift respectively). Week 1 is in rows 5 and 6 (5 being for morning and evening sales, and row 6 being for the total).

The data runs from cells D5:S30 including two columns at the end for the weekly total. So I would want a formula to find the average across a 13 week period for each AM, each PM, and each daily total.

If I enter =AVERAGE(D5,D7,D9,D11,D13,D15,D17,D19,D21,D23,D25,D27,D29) the result is #REF! as the shifts in the future have not been completed.

I have found formulas that work for a contigeous range but this is obviously not the case.

I have considered creating shift reports for all 13 weeks in advance, so that the external reference would find its source, and the formula would work, but if there's a formula out there that would save me the hassle, that'd be superb!

Any ideas and suggestions would be amazing!

Chris