0

In the worksheet below:

  • Previous Count is 630 (B4).
  • I then received 100 items (E4).
  • New stock count is 729 (C4), because I sold 1 item.

Laid out like this:

       B             C         D            E          F
Previous Count  Stock Take  Shipped  Stock Received  Total
630                 729       -99          100        729
Column A is the product ID/item name.  "Stock take" just means my current/live count (inventory) of the item.

I know I have shipped 1 item, but I don't have records (data) to support this; therefore, I'm trying to compute it. I have =SUM(B4-C4) in cell D4, but Excel shows this as -99. How do I fix this formula to show 1?

I've probably had too little sleep because I just can't figure out what the formula or format of the cell should be.

enter image description here

Scott - Слава Україні
  • 21,399
  • 46
  • 64
  • 121
  • 1
    Please provide a sample of your data, preferably as a screenshot, and the formula you're currently using. – Atzmon Oct 27 '16 at 12:30
  • Hi Atzmon... thanks for the response... The current formula I have is: =SUM(B4-C4) buts thats because I'm still trying to work out the correct formula. Will add a screen shot in a second... –  Oct 27 '16 at 12:32
  • it seems until I get 10 points, I wont be able to add a picture... There is a link... but I'm not sure that will work... Anything else I can do instead? –  Oct 27 '16 at 12:45
  • if you only calculate a simple difference of two numbers (here its 630-729) then you will surely get -99 as result. So that formula looks not right for me – Fraggles Oct 27 '16 at 12:56
  • This is unclear. What is "Stock Take"? Is it different than "shipped"? – CharlieRB Oct 27 '16 at 13:06
  • I know the formula is wrong. I suppose it is the formula that I am looking for that will calculate the difference between C4 and B4. But, I want the result in D4 to show that I shipped 1 and not -99. –  Oct 27 '16 at 14:27
  • 'Stock take' is my current/live count of the item. 'Previous Count' was the count at the end of the month. Between the 'Previous Count and the Stock take' I have received 100 items of A4. I know I have only sold/shipped 1 item of A4, but I cant figure out the formula for D4 to show 1. Can it even be done? I dont want to update D4 manually, because I have over 1500 line items and it would take me hours to do this. –  Oct 27 '16 at 14:27
  • @Atzmon: Please don’t *encourage* people to post screenshots. There is a [SE] [policy](http://meta.unix.stackexchange.com/q/4086/23408) that says, “Please don’t post images of text” (on technical/coding sites). Instead, suggest that the user type in a textual representation of his sheet, as was done [here](http://superuser.com/q/889201/150988) and [here](http://superuser.com/q/892744/150988). And when he *does* post text, ***don’t edit it out!*** – Scott - Слава Україні Oct 27 '16 at 22:09

2 Answers2

0

It seems that you are forgetting to add the "Stock Received" cell into your sum. This can easily be fixed by adding +E4 into the function.

Also, SUM is repetitive since it's goal it to add cells or ranges of cells.

From the Official Office Documentation, the definition of SUM is as follows:

The SUM function, one of the math and trig functions, adds values. You can add individual values, cell references or ranges or a mix of all three.

So in your function, you are evaluating B4-C4 and then adding it to nothing.

In conclusion, your function should be: =B4-C4+E4.

Jackie Chen
  • 1
  • 1
-1

You can try this: 

=SUM(B4-C4+E4)

?

marx
  • 22
  • 4
  • This works although I'd have it as =SUM((B4+E4)-C4) but I'm a bit picky about my formulas. You're trying to show your previous stock (B4) adding how many you've received (E4) then taking away how many you've found at your stock take (C4). This leads to ((630+100)-729) giving a total of 1. – Stephen Oct 27 '16 at 15:10
  • 1
    Welcome to superuser: While this may answer the question, it would be a better answer if you could provide some explanation on why and how it will work with as easy to follow instructions. Instead of somebody else providing the detail. If you feel your answer is correct you can do these things and reedit. Please take a couple of minutes and read:- http://superuser.com/help .Answering: http://superuser.com/help/how-to-answer, again welcome to superuser.Thankyou – mic84 Oct 27 '16 at 15:13
  • 2
    marx and @Stephen, why are you including SUM? The proper use of that function is to work with ranges and lists, not to wrap around a self-contained arithmetic expression. Stephen, addition and subtraction can be done in any order. What function do the parentheses around the addition serve? – fixer1234 Oct 27 '16 at 17:57