![]() |
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 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=485842 |
averging prices real problem
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" 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=485842 |
averging prices real problem
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 the average will be wrong what you posted will give not correct average reconsider and come again -- amrezzat ------------------------------------------------------------------------ amrezzat's Profile: http://www.excelforum.com/member.php...o&userid=28766 View this thread: http://www.excelforum.com/showthread...hreadid=485842 |
averging prices real problem
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 wrote: 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 the average will be wrong what you posted will give not correct average reconsider and come again |
averging prices real problem
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 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=485842 |
averging prices real problem
*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 -- amrezzat ------------------------------------------------------------------------ amrezzat's Profile: http://www.excelforum.com/member.php...o&userid=28766 View this thread: http://www.excelforum.com/showthread...hreadid=485842 |
averging prices real problem
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,D1:D5))/SUMIF($A$1:$A$5,F1,$B$1:$B$5) and you will get the answer of 47.5 Regards Roger Govier amrezzat wrote: *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 |
All times are GMT +1. The time now is 11:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com