# Weighted Average with Criteria Using VBA

• Hi,

I'm trying to calculate the weighted average with some criteria (period, store and product) of the Data file and for each average of each product of each store in a certain period, I wanted to paste it into another Workbook (Report) in the worksheets of the respective products.

The problem is that the code I am writing, is not calculating based on these criteria (returns 0.00), if someone can tell me where I am wrong (I am a beginner in VBA), I am grateful.

I already used PivotTables and today I do this calculation through formulas (sumproduct), both take a long time to calculate, because the data file is very large (from 400,000 to 600,000 lines), the computer screen arrives To be dimmed.

For example:
The weighted average for product "101" of store "A" in Jan / 17 is:

(each quantity * each unit price) / quantity sum

((13 * 6) + (12 * 5)) / (13 + 12) which will be equal to 5.52

I'm trying to do for each product per store per month / year.

And do for each product with all the stores together per month / year.

My intention is to automate in VBA because the time gain would be better. And since I'm starting in VBA, it would be a learning experience for me.

Sorry for my english, I'm using google translator.

Follow the files for better understanding.

## Files

• Re: Weighted Average with Criteria Using VBA

## Files

• Re: Weighted Average with Criteria Using VBA

Hi Bryce,

Very perfect.

I just have to thank you, for me, that I am a beginner in VBA was an great learning.

I took the liberty of adjusting the lines: ws.Cells (x, y) = 0 for ws.Cells (x, y) = "-" and ws.Cells (x, 7) = 0 for ws.Cells (x, 7) = "-".

Sorry for my english, I use google translator.

Thank you one more time!

• Re: Weighted Average with Criteria Using VBA

Hey Squat. I am glad it worked for you and no worries on your English! To get the dash "-" instead of 0.00 use formatting. Select your tables and click the comma button on the Home tab. PS I think most of us are still apprentices...myself included:) Happy coding friend!

## Images

• Re: Weighted Average with Criteria Using VBA

Hi Bryce,

Thanks again.

I'll mark the thread as solved.

Health and peace for you.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!