Calculating a weighted average in Access

[jkgreen]

I use the SUMPRODUCT function in Excel to calculated weighted averages. How can I do the same in Access?

Thanks,
Jason

 

[SydneyGeek]

Hi Jason, I don't know how you would reproduce that functionality using native Access features, but one option is to use code to call the Excel function and use it in Access.
Here's a link that shows how: http://support.microsoft.com/kb/198571/EN-US/

Denis

 

[jkgreen]

What about using VBA code to get results? I know I've seen it done. I just don't know VBA or how to do it.

 

[SydneyGeek]

How do you do the weighting? Is it a reproducible logic?

Denis

 

[Fazza]

Hi Jason.

Can you post some sample date - inputs & outputs? In Access, as in Excel, some SQL can do the work. Basically,

SELECT SUM(Value*Quantity)/SUM(Quantity) AS [Weighted Average]
FROM source

HTH, Fazza

 

[velu130486]

Hi HTH,

With help of this forum, I have created my Document Tracking Database for my project use. Now everything is working fine except the Progress Calculation. I would like to do the progress calculcation in Access.

Progress calculation can be performed with Sumproduct function. Could you please help me to understand how we can perform Sumproduct in Access since there is no built in funtion for the same.

I would like to display the results in a form, when I click the calculate button.

I understood that We can use excel function in access by Application.Worksheet function however I don't know how to get the Query field names in the same.

Herewith I had enclosed my Database for your review.

My Formula will be like this

Sumproduct ([MHRS NEW INT],[InternalProgress])/sum(([MHRS NEW INT])

Thanks and Regards
R. Vadivelan

 

[Fazza]

SUM([MHRS NEW INT]*[InternalProgress])/SUM([MHRS NEW INT])