When you play the stock market you need an efficient way to calculate your profits – and losses. Some simple Excel formulas can do the trick and give you a quick overview over your current status. In a new Excel tutorial in the kalmstrom.com Tips section I show how to create the formulas.
Excel calculations
The formulas are probably the most useful of all Excel features. An Excel formula can be as simple as adding the numbers in two cells, but Excel formulas are also used for very complex calculations. Once the formula is in place, Excel does all the calculations and refreshes the result each time you change a value in one of the cells included in the formula.
However, the Excel result will only be correct if the formula defining how Excel should calculate is the correct one. I discussed this in a blog post about one of my first Excel formula demos, and I also gave an example on a miscalculation that had a huge impact.
Three formulas needed
When you buy stocks you know the cost for one stock, but normally buy more that one. Therefore, the first step in a stocks calculation is to create a formula that multiplies the cost for one stock with the number of stocks bought.
Next step is to do the same multiplication for the number of stocks and the current value. And finally you must create a formula that calculates the difference between the two multiplication results to learn how much you have gained or lost.
Drag down relative formulas
Say that you have bought one hundred different kinds of stocks. To write in these three formulas one hundred times each will make you wonder if it is really worth the effort.
But Excel gives you a much easier way, which I am showing in the demo below. As long as you use relative formulas – for example the second cell to the left minus the third cell to the left – you can just drag the first formula and make it valid for all the other 99 stock types too.
Excel tips
The demo above is just one of many in the kalmstrom.com Tips section. In addition to the Excel tutorials there are tips and pointers on Outlook, SharePoint and much more. You are very welcome to browse around!
By Peter Kalmstrom
CEO and Systems Designer
Excel calculations
The formulas are probably the most useful of all Excel features. An Excel formula can be as simple as adding the numbers in two cells, but Excel formulas are also used for very complex calculations. Once the formula is in place, Excel does all the calculations and refreshes the result each time you change a value in one of the cells included in the formula.However, the Excel result will only be correct if the formula defining how Excel should calculate is the correct one. I discussed this in a blog post about one of my first Excel formula demos, and I also gave an example on a miscalculation that had a huge impact.
When you buy stocks you know the cost for one stock, but normally buy more that one. Therefore, the first step in a stocks calculation is to create a formula that multiplies the cost for one stock with the number of stocks bought.
Next step is to do the same multiplication for the number of stocks and the current value. And finally you must create a formula that calculates the difference between the two multiplication results to learn how much you have gained or lost.
Drag down relative formulas
Say that you have bought one hundred different kinds of stocks. To write in these three formulas one hundred times each will make you wonder if it is really worth the effort.
Excel tips
The demo above is just one of many in the kalmstrom.com Tips section. In addition to the Excel tutorials there are tips and pointers on Outlook, SharePoint and much more. You are very welcome to browse around!
By Peter Kalmstrom
CEO and Systems Designer
No comments:
Post a Comment