# Weighted Average with ReDim preserve

• Hi,

A friend here in the forum helped me with a weighted average code.
Now I'm trying to calculate the weighted average monthly with the following criteria:
When there are 3 or more suppliers for each product in each store per month.
For example:
The weighted average for StoreA Apple product on Jan / 17 when there are 3 or more suppliers:

(each quantity * each unit price) / sum quantity

((33 * 5,00) + (23 * 3,00) + (25 * 3,00) + (27 * 5,00)) / (5,00 + 3,00 + 3,00 + 5,00) must be equal to 4.111111

Then paste this price into the Report worksheet
It includes some lines to make this new criterion, however the code is not calculating, would like to know where I'm going wrong?
I am using the ReDim preserve statement.
Follow the files for better understanding

## Files

• Re: Weighted Average with ReDim preserve

I can't access the worksheets just now as I'm at work.

But for starters I'd be more inclined to use a combination of =sumproduct and =sum to do the calculation.

Assuming the data is actually on the sheet somewhere:

[TABLE="class: grid, width: 500"]

[tr]

[td]

Row/Columns

[/td]

[td]

A

[/td]

[td]

B

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td]

33

[/td]

[td]

5.00

[/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

23

[/td]

[td]

3.00

[/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

25

[/td]

[td]

3.00

[/td]

[/tr]

[tr]

[td]

4

[/td]

[td]

27

[/td]

[td]

5.00

[/td]

[/tr]

[/TABLE]

=SUMPRODUCT(A1:A4,B1:B4)/SUM(B1:B4)

Will return the same as:

=((33 * 5,00) + (23 * 3,00) + (25 * 3,00) + (27 * 5,00)) / (5,00 + 3,00 + 3,00 + 5,00)

Or in a parameterised format:

=((A1* B1) + (A2 * B2) + (A3 * B3) + (A4 * B4)) / (B1 + B2 + B3 + B4)

• Re: Weighted Average with ReDim preserve

Hi TheGlovner,

Thanks for the feedback,

So using "sumproduct" works, but I intend to do this calculation when the number of suppliers per store and per product is equal to or greater than 3.

As in the table below:

[TABLE="width: 434"]

[tr]

[td]

Period

[/td]

[td]

Supplier

[/td]

[td]

Store

[/td]

[td]

Region

[/td]

[td]

Product

[/td]

[td]

Quantity

[/td]

[td]

Price

[/td]

[/tr]

[tr]

[td]

jan/17

[/td]

[td]

105

[/td]

[td]

StoreA

[/td]

[td]

South

[/td]

[td]

Apple

[/td]

[td]

33

[/td]

[td]

5,00

[/td]

[/tr]

[tr]

[td]

jan/17

[/td]

[td]

103

[/td]

[td]

StoreA

[/td]

[td]

South

[/td]

[td]

Apple

[/td]

[td]

23

[/td]

[td]

3,00

[/td]

[/tr]

[tr]

[td]

jan/17

[/td]

[td]

103

[/td]

[td]

StoreA

[/td]

[td]

South

[/td]

[td]

Apple

[/td]

[td]

25

[/td]

[td]

3,00

[/td]

[/tr]

[tr]

[td]

jan/17

[/td]

[td]

101

[/td]

[td]

StoreA

[/td]

[td]

South

[/td]

[td]

Apple

[/td]

[td]

27

[/td]

[td]

5,00

[/td]

[/tr]

[/TABLE]

Condition: When the number of suppliers per store and per product is >=3
In this case we had 3 suppliers: 101, 103 and 105

Average the Product "Apple" in StoreA in Jan/17 equal 4,11111