How do I do this in Excell 2007?

Hi all.
What I am trying to do is to create a running stock total in Excel.
I list the amount bought in and, when I sell some, it subtracts the amount sold, leaving the amount remaining.
I cannot see anywhere where it shows how to include a minus (-) subtraction calculation.
I am ok doing additions (=SUM) but I cannot fathom out how to do this subtraction.
Any help most welcome.
I bet it's sooo easy when you know how??
Regards
Ed

Post 1 of 1 | Back to top

 

Example:

=A2 - A1

Is that what you're after?

Or this should work for a range...

= - SUM(A2:A4)

The Equivalent being

= 0 - SUM(A2:A4)

 

Hi,
thanks for that.
I will give it a go.
The example I should have given was subtract C4 to C188 from the totals of B4 to B188.
Can I do this all in one go, or will I need to add your sum to each one individuallY?
say, in B4 I have 1 Kilo, I sell 350g, which goes in C4. I then hqave D4 where I want the remaining amounts to show automatically?
I have 188 different items to do this calculation for.
God! I hope that you inderstand all that, as I don't!!
regards
Ed

 

In CELL D4 for example

=B4 - C4

Then copy and paste (ie Highlight D4 -> Copy then Highlight all cells D5-D188-> Paste), Excell will adjust the formula appropriately for each individual row.

 

YOu can make numbers negative in Excel 2 popular ways:

(100) equals negative 100
-100 equals negative 100

The reason I mention this is that normal accounting methods would generally consider sales to be positive numbers, and costs to be negative numbers, and the difference would be either profit, or (loss).
This is helpful when working with values in one column where you want to use the SUM() function, but some values may be positive and some may be negative. Keeping track of the positives and negatives will allow the SUM() function to work accurately.

On the other hand, if sales (positives) are all in one column, and costs (negatives) are in a second column, the costs can still be shown as positive numbers, but you HAVE to remember to perform a subtraction in order to arrive at profit.

For example if both columns show positive numbers, to calculate profit where column B equals sales and column C equals costs, the formula in D4 would be =B4-C4.
You can sum the whole columns of B and C with the SUM(B4:B188) and SUM(C4:C188) in B189 and C189 respectively. HOWEVER, you then must remember to actually SUBTRACT C189 from B189 (=B189-C189) in D189 in order to calculate the total Profit or Loss.
HOWEVER, if you make a habit of entering costs as negative numbers in the beginning, then the SUM() function will work for EVERYTHING, =SUM(B4:C4) in D4, SUM(B4:B188) in B189.
As wildfire mentioned, you can then uniformly use copy/paste as follows:
for cell C189, copy B189 then select C189 and paste
for cells D5 through D189, copy D4 and then select D5 to D189 and paste

This will provide you with individual profit(loss) for each line item in column D, total sales in B189, total costs in C189, and total profit(loss) in D189.

Yes, you can make other ways work, but from an actual accounting standpoint, generally totals are not supposed to include individual additions and substractions. In general, the numbers themselves are supposed to be either positive, or negative, and then the only math required is to simply SUM the numbers.

 

very many thanks

Thank you very much for your detailed help, I shall use your method henceforth-safe in the knowledge that my Accountant will approve??? (That will be a first)
Just proves how little I really know about Excell (and accounting)
best wishes
Ed