Reply
Wed 22 Jan, 2003 10:46 pm
Ok, I usually figure puter stuff out on my own but now I need to know some things sooner that I might be able to learn them myself.
I need to know how to make this happen:
A B C
1
2
3
4
I need A1 and B1 to put its sum in C1, this will continue in each row but I want the C colum to give a running total.
E.g.
Product added (A1) Product Subtracted (A2)
Nevermind, I just realized I was being daft (I can just do both in one column), now my question is this:
To those who track inventory in Excell, would you send me an example of one of your files? PM me if you can. I need to write some complex web applications but first need to know more about the basics of accounting and inventory tracking.
I'm sure you'll get answers tonight, Craven......I'm too tired to post anymore. You have my # at work ~ call me tomorrow (or I'll call you back).....sorry ~ gotta sleep.
Aha, I'll do that. I'm only gonna work a half day tomorrow but will try to give ya a call.
Okey dokey.....We do have a toll free #......I'll try to remember to send it to ya tomorrow.....along with the other half a million things I have to do.
LOL, we just got a toll free one too. tee hee.
To answer the question you no longer want:
In cell c1 enter this formula =sum(a1:b1)
In excel, the = sign indicates that a formula follows. In practice, I will type in =sum( from this point, you can highlight any range of contiguous cells, hit the enter key, and the formula will complete itself. Having the formula in c1, highlight c1, right click and select copy. Highlight the range of cells into which you want to place the formula and again, hit the return key. The formula references will always be relative unless you specify otherwise.
I keep my depreciation schedule in excel, but if I were maintaining inventory of goods for sale, I would keep it in Peachtree. It will do your calculations, and they aren't much fun, depending on which inventory method your company is using. You will get radically different figures for inventory valuation and net income depending on whether you are using fifo or lifo.
roger,
I had managed to do the a1-b1 sums but couldn't get the C column to do a running total.
you reminded me of a question I forgot to ask.
What's fifo?
Hi Craven.... if you want C to equal A plus B..... then you'll need a column D for the running total..... the formula will be
=sum(D1+C2)
=sum(D2+C3)
etc...
:-)
Hi Craven...
FIFO.... First In First Out
LIFO.... Last In First Out
...different ways to track your inventory...
:-)
geesh glad I'm late getting here! LOL
even have excel on the Ol'pocketpc.....
P & L is exactly right, on excel and fifo/lifo
Thanks PAL and Roger,
But is there any way to get the column to do that without entering the formula cell by cell? Because if I have to enter a formula for each cell it'd be easier just to do the calculations myslef and enter them in the d column.
Husker,
My handheld came with excel but I deleted it. Back then i never thought I'd accept a job that required excel skills.
Hi Craven......
If you purchased 10 reams of paper in January for $1.50 each, and
you purchased 10 reams of paper in May for $2.00 each, and
you purchased 10 reams of paper in August for $2.50 each, and
at the end of the year you have 15 reams of paper in your inventory.........
FIFO.... your cost of inventory would be 5 @ $2.00 and 10@ $2.50
because you used up (out) the First reams you purchased
LIFO.... your cost of inventory would be 10 @ $1.50 and 5 @ $2.00
because you used up (out) the Last reams you purchased.
Sometimes you want your inventory valued higher, and sometimes you want your inventory valued lower...... usually for tax purposes...
:-)
Aha! That makes sense. I think we are doing FIFO (I'm supposed to write the process manuals when I figure this all out) but since we don't deal with perishables I hadn't thought it'd be important.
The tax angle makes sense.
Sure glad I don't deal with merchandise inventory, and would sure let Peachtree or whatever deal with the issues. The nice part about the system is that your choice can lower taxable income without changing the money you put in the bank. Once established, you cannot arbitrarily change inventory methods, so you are more or less stuck with what you inherit.
Hi Craven.....
While you're in D2, type the formula: =sum(D1+C2)
Then, while you're still in D2, do a copy (Ctrl C)
Then, highlight D3, D4, D5, D6, etc, all at the same time (you can use your mouse or you can use shift-arrow to hightlight).
Then, do a paste (Ctrl V)
Excel will automatically change the row numbers, so that you'll get the running total.
If you insert a row in the middle of your spreadsheet, you'll need to redo the copy/paste, so that the row numbers are correct in the formulas.
:-)
Or, just put your cursor in the lower right corner of the cel where the formula is. Make sure the cursor becomes a black cross. Now, holding down the left mouse button, drag down. The formula will copy down and Excel will automatically change your row numbers to what you need.
Thanks everyone. I'm at the tail end of an all nighter and will go home soon but this is gonna come in handy.