This depends on how your data is laid out.

I'm assuming you have the two sets of records on different worksheets. I'm
also assuming from what you said that there is a data field in column 'X'
that contains the value you want to average and that the other records have a
null or text value in that field.

If you have worksheets WS1 and WS2 with 200,000 and 1,000,000 respectively,
then you can take a direct average of both ranges :

=average('WS1'!X1:X200000','WS2'!X1:X1000000)

This will exclude empty cells and text.


HTH
-Rayo

"rgl" wrote:

I'm having issues with weighted averages for some data I'm working with. Here
is an example:

I have 200,000 records
Of which 8,000 have a value ranging from 3-15
The sum of those values is 40,000
The straight average is 5, based on the 8,000 records with values

Another sample
1,000,000 records
Of which 150,000 have a value ranging from 3-15
The sum of those values is 675,000
The straight average is 4.5, based on the 150,000 records with values

My question is how to make this averages weighted? The one with 200,000
records has a higher average than the 1,000,000 record sample, but I need to
reflect that based on the volume differences.

Any ideas?

Thanks.