MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### weighted average profit margin

• Follow

```Hi. I'm looking for help with how to calculate weighted average profit
margin.  Basically, in an Excel spreadsheet, column A contains Sales
numbers and column B contains Profit Margin % for each of those sales
numbers. I need the weighted average profit margin for all sales
combined.

Sales    profit
\$100     25%
\$500     15%
\$300     10%
```
 0

```In column C (on another unused column) calculate the profit for each sales.

=A2*B2
etc.

Then use this formula to calculate average profit margin:

=SUM(C2:C4)/SUM(A2:A4)

or without using column C use this formula:

=SUMPRODUCT(A2:A4,B2:B4)/SUM(A2:A4)

Regards,
Per

"mar" <marzracing@gmail.com> skrev i meddelelsen
> Hi. I'm looking for help with how to calculate weighted average profit
> margin.  Basically, in an Excel spreadsheet, column A contains Sales
> numbers and column B contains Profit Margin % for each of those sales
> numbers. I need the weighted average profit margin for all sales
> combined.
>
> Sales    profit
> \$100     25%
> \$500     15%
> \$300     10%

```
 0

```Your total profit was: 100*25%  + 500*15%  + 300*10% = 25 + 75 + 30 = 130
Your total sales where 900, so %profit is 130/900 = 14.4%

If you data starts with "Sales" in A1, the sales values in A2:A4 and the
margins in B2"B4
then this formula gives you the correct result
=SUMPRODUCT(A2:A4,B2:B4)/SUM(A2:A4)

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"mar" <marzracing@gmail.com> wrote in message
> Hi. I'm looking for help with how to calculate weighted average profit
> margin.  Basically, in an Excel spreadsheet, column A contains Sales
> numbers and column B contains Profit Margin % for each of those sales
> numbers. I need the weighted average profit margin for all sales
> combined.
>
> Sales    profit
> \$100     25%
> \$500     15%
> \$300     10%

```
 0

```ok got it.
thanks all!
```
 0

3 Replies
1512 Views

Similiar Articles:

7/24/2012 2:44:27 AM