averging prices real problem
 

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 on
company (microsoft) )
so the result will be
((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??

--
amrezza
-----------------------------------------------------------------------
amrezzat's Profile: http://www.excelforum.com/member.php...fo&userid=2876
View this thread: http://www.excelforum.com/showthread.php?threadid=48584

averging prices real problem
 

Surely, after the third Microsoft transaction, the average is still 50, as
you have had 300 transactions, 100 at 50, 100 at 60, 100 at 50.

But, this will get what you ask for.

In F1: =IF(A1=1,D1+E1,0)
F2: =(H1+IF(A2=1,D2+E2,0))/(IF(A2=1,1)+IF(H10,1))

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"amrezzat" wrote in
message ...

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???


--
amrezzat
------------------------------------------------------------------------
amrezzat's Profile:
http://www.excelforum.com/member.php...o&userid=28766
View this thread: http://www.excelforum.com/showthread...hreadid=485841

averging prices real problem
 

you seemed not read what i posted carefully

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

--
amrezza
-----------------------------------------------------------------------
amrezzat's Profile: http://www.excelforum.com/member.php...fo&userid=2876
View this thread: http://www.excelforum.com/showthread.php?threadid=48584

averging prices real problem
 

You changed your 200 @ 55 to 100 @ 55 to come up with your answer.
((100*50+100*60)/(100+100))=55 (is the average price of the 200
(100*55+100*40)/(100+100)=47.5
Had you used =(200*55+100*40)/300 the answer would be 50.
"amrezzat" wrote:


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???


--
amrezzat
------------------------------------------------------------------------
amrezzat's Profile: http://www.excelforum.com/member.php...o&userid=28766
View this thread: http://www.excelforum.com/showthread...hreadid=485841