averaging of unique data

  • Thread starter Thread starter James
  • Start date Start date
J

James

how could i do this, I want to be able total the qty(cell B2) for the same
number (A1), then average the cost (C1)?
so the end result will be to have 4470=9, at an average cost of 530.00.

any ideas? i was thinking maybe pivot tables?

number QTY Price
4470 5 $560.00
0185 10 $1,500.00
t125 4 $10,000.00
4470 4 $500.00
0185 5 $1,000.00
0157 6 $400.00
 
Hi James,

I believe this is what you're looking for:

=SUMIF(A2:A7,4470,C2:C7)/COUNTIF(A2:A7,4470)

But wouldn't this be more representative of an average cost:

=SUMIF(A2:A7,4470,C2:C7)/SUMIF(A2:A7,4470,B2:B7)

Hope this helps!
 
Thanks for replying!
the problem i have is that i need to have it list all the items in A1, you
fomula will work, but i have hundreds of different items to list out.
 
James said:
Thanks for replying!
the problem i have is that i need to have it list all the items in A1, you
fomula will work, but i have hundreds of different items to list out.
Click to expand...

Okay, then assuming that your list is on Sheet1, try the following:

1) Enter the following column headings in Row 1 of Sheet2, in Columns A,
B, and C:

number QTY Average Cost

2) Enter the following formula in A2 of Sheet2, using CTRL+SHIFT+ENTER,
and copy down until you get a #N/A error:

=INDEX(Sheet1!$A$2:$A$100,MATCH(0,COUNTIF(Sheet2!$A$1:A1,Sheet1!$A$2:$A$1
00),0))

3) Enter the following formula in B2 of Sheet2, and copy down as far as
you need to:

=SUMIF(Sheet1!$A$2:$A$100,A2,Sheet1!$B$2:$B$100)

4) Enter the following formula in C2 of Sheet2, and copy down as far as
you need to:

=SUMIF(Sheet1!$A$2:$A$100,A2,Sheet1!$C$2:$C$100)/B2

Hope this helps!
 
i think this might work!
however when i copy down the first formula it only returns the first item in
A1 of Sheet 1. am i doing something wrong?
 
James said:
i think this might work!
however when i copy down the first formula it only returns the first item in
A1 of Sheet 1. am i doing something wrong?
Click to expand...

Make sure that the COUNTIF statement is entered correctly. Note that
the second A1 reference is relative and not absolute.

So that part should read COUNTIF(Sheet2!$A$1:A1,Sheet1!$A$2:$A$100).

Does that help?
 
...
...
But wouldn't this be more representative of an average cost:

=SUMIF(A2:A7,4470,C2:C7)/SUMIF(A2:A7,4470,B2:B7)
Click to expand...
...

No, not if col C were price PER UNIT. Consider

abc 1 10
abc 9 1

If col C were price per unit, the the average price per unit for all abc would
be (1*10+9*1)/(1+9) = 1.9. Your formula would give 1.1.
 
how could i do this, I want to be able total the qty(cell B2) for the same
number (A1), then average the cost (C1)?
so the end result will be to have 4470=9, at an average cost of 530.00.

any ideas? i was thinking maybe pivot tables?

number QTY Price
4470 5 $560.00
0185 10 $1,500.00
t125 4 $10,000.00
4470 4 $500.00
0185 5 $1,000.00
0157 6 $400.00
Click to expand...

If the table above, *excluding* the column headings in the top row, were named
Tbl, and if the top-left cell of your results were cell A2, possibly on a
different worksheet, then you could try these formulas.

A2:
=INDEX(Tbl,1,1)

B2:
=SUMIF(INDEX(Tbl,0,1),A2,INDEX(Tbl,0,2))

C2:
=SUMPRODUCT(--(INDEX(Tbl,0,1)=A2),INDEX(Tbl,0,2),INDEX(Tbl,0,3))/B2

A3 <array formula - hold down [Ctrl] and [Shift] keys before pressing [Enter]>:
=INDEX(Tbl,MATCH(0,COUNTIF(A$2:A2,INDEX(Tbl,0,1)),0),1)

Fill A3 down as far as needed. The formula will evaluate to #N/A when the
distinct entries in the first column of Tbl have been exhausted. Then select
B2:C2 and fill down to match the formulas in column A.
 
Harlan Grove said:
...
..
..

No, not if col C were price PER UNIT. Consider

abc 1 10
abc 9 1

If col C were price per unit, the the average price per unit for all abc would
be (1*10+9*1)/(1+9) = 1.9. Your formula would give 1.1.
Click to expand...

Looking at this again, Column C is most likely the price per unit.
Which would explain why James came up with his average cost in the first
place.
 
Perfect!! thank you so much guys!
Harlan Grove said:
how could i do this, I want to be able total the qty(cell B2) for the same
number (A1), then average the cost (C1)?
so the end result will be to have 4470=9, at an average cost of 530.00.

any ideas? i was thinking maybe pivot tables?

number QTY Price
4470 5 $560.00
0185 10 $1,500.00
t125 4 $10,000.00
4470 4 $500.00
0185 5 $1,000.00
0157 6 $400.00
Click to expand...

If the table above, *excluding* the column headings in the top row, were named
Tbl, and if the top-left cell of your results were cell A2, possibly on a
different worksheet, then you could try these formulas.

A2:
=INDEX(Tbl,1,1)

B2:
=SUMIF(INDEX(Tbl,0,1),A2,INDEX(Tbl,0,2))

C2:
=SUMPRODUCT(--(INDEX(Tbl,0,1)=A2),INDEX(Tbl,0,2),INDEX(Tbl,0,3))/B2

A3 <array formula - hold down [Ctrl] and [Shift] keys before pressing [Enter]>:
=INDEX(Tbl,MATCH(0,COUNTIF(A$2:A2,INDEX(Tbl,0,1)),0),1)

Fill A3 down as far as needed. The formula will evaluate to #N/A when the
distinct entries in the first column of Tbl have been exhausted. Then select
B2:C2 and fill down to match the formulas in column A.
Click to expand...
 
Back
Top