# Trouble with weighted average formula

This is a discussion on Trouble with weighted average formula within the Excel Questions forums, part of the Question Forums category; I am trying to get a formula for a weighted average for prices for a large number of products sold ...

1. ## Trouble with weighted average formula

I am trying to get a formula for a weighted average for prices for a large number of products sold in various quantities over many months. A small sample of the data goes like this (sorry, the add-in didn't work and I can't post the HTML)

column A - product name
Column B - Jan price
Column C - Jan qty
Column D - Feb price
Column E - Feb qty
Column F - Mar price
Column G - Mar qty

I have come up with the following formula, which works: for row 10 -
{=((b10*c10)+(d10*e10))+(f10*g10)/SUM(IF(b5:g5="kg",b10:g10))}

(row 5 has either "kg" or "qty", and row 6 has the month)

As I said, this works but it is very inelegant and impractical, since I have to get the average over a large number of months. I'd appreciate it if someone could help me with this, as I can't seem to quite get how to improve this. Sorry if this is a no-brainer.

2. ## Re: Trouble with weighted average formula

SORRY! I meant:

column A - product name
Column B - Jan price
Column C - Jan qty
Column D - Feb price
Column E - Feb qty
Column F - Mar price
Column G - Mar qty

I have come up with the following formula, which works: for row 10 -
{=((b10*c10)+(d10*e10))+(f10*g10)/SUM(IF(b5:g5="qty",b10:g10))}

(row 5 has either "price" or "qty", and row 6 has the month)

3. Moved to Excel Questions.

4. you'd be much better off with the following set-up:

Product | Date | Price | Quantity

...any chance of changing the design?

5. ## Re: Trouble with weighted average formula

I hear you, but the boss wants it so he can visualize a year at a time per customer. This is the standard way they set up their spreadsheets. Any ideas that incorporate this design?

6. ## Re: Trouble with weighted average formula

What is "kg"? It doesn't show up as column heading in your description.

7. ## Re: Trouble with weighted average formula

Sorry, it was an error in my original post. I corrected it in the second post, should be qty (kg is kilograms, I didn't want to mystify the metrically challenged).

8. ## Re: Trouble with weighted average formula

Originally Posted by hjsinger
Sorry, it was an error in my original post. I corrected it in the second post, should be qty (kg is kilograms, I didn't want to mystify the metrically challenged).

******** ******************** ************************************************************************>
 Microsoft Excel - Book5 ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 A5 =

A
B
C
D
E
F
G
5
ProdNamePriceQtyPriceQtyPriceQty
6
*Jan*Feb*Mar*
7
P15.882311.142710.1922
8
P28.57267.07218.3826
9
P34.35307256.3322
10
P49.112712.842210.4526
 Sheet1 *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

9. ## Re: Trouble with weighted average formula

Yes, exactly.

10. ## Re: Trouble with weighted average formula

Originally Posted by hjsinger
Yes, exactly.
Fine.

Why is it...

=((B10*C10)+(D10*E10))+(F10*G10)/SUM(IF(B5:G5="Qty",B10:G10))

=((B10*C10)+(D10*E10)+(F10*G10))/SUM(IF(B5:G5="Qty",B10:G10))

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•