Access/Excel discrepancy

[Guest]

Hi,

I have got an Excel Spreadsheet used to calculate percentages of work
requests and completed requests.

It also uses the AVERAGE() function to calculate the average over a period
of time (After and including July 04) for forecasting potential
requests/completions.

The data actually comes from an Acess database and is manually input to the
s/sheet.

I am attempting to use access queries (Avg() function) to calculate the
average but cannot get them to match.

Is there any differences between Excel's AVERAGE() and Access's Avg()
functions?

Any help is greatly appreciated.

Cheers,
Steve.

 

[Tom Lake]

Is there any differences between Excel's AVERAGE() and Access's Avg()

functions?

If there are null values in the data (not 0), perhaps Access calculates the
average differently than Excel.

Tom Lake

 

[Guest]

I have made sure there are no 'blanks' using the nz() function.

Could there be a difference in the way they round numbers?

Steve.

 

[John Spencer (MVP)]

Using NZ will make a big difference in an average in Access.

The average of 0,1,2 is 1
The average of Null, 1, 2 is 1.5
The average of Nz(Null,0),1,2 is 1.5

The average function ignores nulls completely when doing a calculation.

As far as I know, the Excel Average and the Access Avg yield the same results,
given the same set of data.

Perhaps you can post the results you are getting that make you think things
differ. AND more importantly post the code you are using.