Excel/calculating

Advertisement


Question
QUESTION: Hello

How do I have a running total of my stock after each sale?  
For example          1000
     sold          100
Automatically showing          900

How do I get this automatically calculated please?
This is Excel 2010

Thank you,   Gabe

ANSWER: Hi Gabe,

How to do this depends on your sheet setup and the answers to these questions:

Are the sold numbers coming from a feed (e.g., an rss feed) directly from your brokerage website?  Or are you entering them manually or perhaps pasting them from somewhere?

Are you wanting to have the data automatically put in a table showing the history of all your transactions with the date, shares sole, and new total shares?  If so, do you want the latest transaction at the top?

Follow up with this additional information and I will try to answer your question.

Damon

---------- FOLLOW-UP ----------

QUESTION: Hello again

First thank you again.

I insert the starting number of supplies by hand, manually, say 1000/   

Than, again insert number of sold items,  say  100

And than, all I want is to see how many are left, now 900.

Once in while I add more items to the existing list

Say I have now 900 and I added  another 500 so now I have a total of 1400 that I want to continue the same way I started...  

         1400
sold         100
Have on hand 1300

How exactly do I do this too?
And, the latest transaction, better on the bottom...above the have on hand now.
 



Thank you very much.    Gabe

Answer
Starting point
Starting point  

Auto-filled down two rows
Auto-filled down two r  
Hi Gabe,

Okay, that helps.  There are lots of ways of approaching this, including "bells and whistles" such as automatically entering the date that each transaction occurs.  What I'm providing is pretty simple, minimal frills.

I suggest you type into B2:C4 as shown in image Clip 1.


Now replace 900 in C4 with the formula

=C2+IF(B3="sold",-C3,+C3)

This will cause the number in C3 to be subtracted from the total if the word "sold" is in B3.  You now have your basic template for adding more transactions.

Now select cells B3:C4, then click on the fill handle (the tiny square in the lower right corner of C4) and drag it down to row 6 (you can drag down much further if you want to add many transactions at one time).  You should now see as shown in image Clip 2.

Just edit the sold quantity in C5 (now 101) to the amount of the new sold transaction.  If it is a buy transaction simply delete the "sold" in cell B5.  Each time you want to create a new transaction and "on hand" amount simply select the last two entries in columns B and C and fill them down as you did with B3:C4.

I hope this is what you were looking for.

Keep Excelling.

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.