averging prices real problem

[amrezzat]

heloo
if i have 5 columns like these

suppose that this is a sell and buy stocks forum
the first column is company number
the second column is company name
the third column is for quantity
the forth is for buy price
the fifth column is for sell price
as shwon belo

1 microsoft 100 50
2 sakhr
3 intel
1 microsoft 100 60
1 microsoft 100 (this is blank cell) 40
1 microsoft 100 40

and i want to average the price of the stock (for only the numer one
company (microsoft) )
so the result will be
((100*50+100*60)/(100+100))=55 (is the average price of the 200
stock)(downto the forth row)

then i sell 100 (in the fifth row)
so the result is
100 stock with average price 55
then i buy 100 with price 40
so the final result will be (100*55+100*40)/(100+100)=47.5
how can i do that in excel???

 

[Guest]

Amrezzat --

One way of approaching the problem is to add a sixth column, which will
ultimately be set to either a 1 or a 0 depending on whether the company is
"microsoft". I will give the relative expression for the first row:
@IF(B2="microsoft",1,0). The result is a 1 if the column B entry for the
given row is "microsoft". Now enter in column G the expression "=F1*C1*D1".
This column will now contain only values associated with "microsoft".
Finally, at the bottom of column G, enter the expression
@sum(G1:G5)/@sum(F1:F5). In this cell, you now have the average.

Depending upon your need to preserve the data in the original table, I can
think of a couple of ways to obtain a new average for the sold shares. I
would probably enter a 1 in column H if I own the shares, and a 0 if I don't
(and I would change column H as the status of the stock changed). Use the
same logic as above to get the new average.

 

[amrezzat]

you seemed not read what i posted carefully

you talked about how to figure which company i dealt with
and i successfully can do that........ thanks

the problem is
i cant make the excel feel that i sold some shares as a result th
average will be wrong

what you posted will give not correct average
reconsider and come agai

 

[Roger Govier]

Hi

One way
=SUMPRODUCT(--($A$1:$A$5="Microsoft"),$B$1:$B$5,$C$1:$C$5)/SUMIF($A$1:$A$5,"Microsoft",$B$1:$B$5)

It is probably best to Microsoft in another cell, e.g. F1 and then use the
cell reference rather than the name in the formula. That way, just changing
the entry if F1 would give the results for another company without having to
adjust the formula.

=SUMPRODUCT(--($A$1:$A$5=F1),$B$1:$B$5,$C$1:$C$5)/SUMIF($A$1:$A$5,F1,$B$1:$B$5)

The answer incidentally, is 50 not 47.5.
You have 200*55 + 100*40, not 100*55 and 100*40

Regards

Roger Govier

 

[amrezzat]

hi please read the first post i wrote again

and read what i wrote in the following


((100*50+100*60)/(100+100))=55 (is the average price of the 20
stock)(downto the forth row)

then i *sell* 100 (in the fifth row)
so the result is
100 stock with average price 55
then *i buy 100 with price 40*
so the final result will be (100*55+100*40)/(100+100)=47.5
how can i do that in excel??

 

[amrezzat]

*the problem is *i* cant make the excel feel that i sold some shares as
a result the average will be wrong*what you posted will give not
correct average
reconsider and come again

 

[Roger Govier]

Hi

I had not seen clearly from your original message that you had sold any shares.
If you use Negative numbers for the Sales, then with Microsoft held in F1,
change the formula I gave you to

=(SUMPRODUCT(--(A1:A5=F1),B1:B5,C1:C5)+SUMPRODUCT(--(A1:A5=F1),B1:B5,D15))/SUMIF($A$1:$A$5,F1,$B$1:$B$5)

and you will get the answer of 47.5

Regards

Roger Govier