How to create a formula for weighing and summing an undefined number of rows?

[bergen]

Hello,

I am looking for help to crack this one: I want to calculate the total discount to customers based on discounts on individual products. However the number of products vary from each customer. How can I do this fast and automatic in excel? (And I am stuck with a column in between the two useful columns)

Below I will show you a sample of what information I have:[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Product Name[/TD]
[TD]Discount %[/TD]
[TD]N/A[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD]47%[/TD]
[TD]X[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD]46%[/TD]
[TD]X[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Product C[/TD]
[TD]59%[/TD]
[TD]X[/TD]
[TD]224[/TD]
[/TR]
</tbody>[/TABLE]

I was thinking something like this: =(B3*D3)+(B4*D4)+(B5*D5)/(D3+D4+D5)/3
But clearly I am bad at math since this doesn't give me anything close to 50.5%. And even if it did this formula does not allow for adding or subtracting products.

Can anyone help?

 

[njimack]

I calculate the discount to be 58.2% using the below:
=SUMPRODUCT(B3:B5,D3:D5)/SUM(D3:D5)

To handle varying number of rows, consider using dynamic ranges: Excel Names -- Excel Named Ranges

 

[bergen]

Thank you Neil, works like a peach!