Finding the diferenced between two numbers in Excel 2

[Pandab2002]

I have a spreadsheed that tracks a weekly inventory. I am trying to find a formula that I can put in another cell that shows the difference between the number from one week to the next. Currently, I simply use something like =SUM(B2-A2). It works okay, but I have 100 rows of data, some data will increase from one week to the next, resulting in a positive number and some data will decrease resulting in a negative number. When I total all of the results the positive and negatives do not give me an actual answer. For example if the result of =SUM(B1-A1) is -5 and the result of =SUM(B2-A2) is 5, I want to show that the total difference is 10 by adding 5+5, instead of it being 0 by adding -5+5. Any suggestions are greatly appreciated.

 

[jimr381]

Try using the absolute value function which is =abs() This shows -5 as 5 because it is a 5 unit shift from the original value

I used to be over by the window, and I could see the squirrels, and they were married..
________
28192

 

[yogia]

Hi Pandab2002:

Let me see if I understand you correctly ...

Does it help?

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[AnotherHiggins]

First, a note about the SUM function. In your examples, there is no reason to use SUM, it is a waste of time and only serves to confuse things.

SUM means to add together. "The sum of 1, 2 and 3" means 1+2+3, which equals 6.

When using SUM in Excel, the numbers you want added are separated by commas. So you could represent the above problem as =SUM(1,2,3). But this can more simply be entered into Excel by using [COLOR=blue white]=1+2+3[/color]

What you are doing is asking for a sum of a single number - the result of B1-A1. So what you are asking excel to do is SUM(5), just give you the sum of 5.

Generally, SUM is used with ranges, not individual cells. For example, =SUM(A1:A1000) will sum all cells in that range.

Now that that's out of the way....

Do you want column C (I'm assuming that's where you are putting your formulas) to show whether the change was positive or negative? If you always want it to display a positive number, then you can use
[COLOR=blue white]=ABS(B1-A1)[/color]
which returns the absolute value of the answer. (See Excel's help file for more info on Absolute Power)

Or, if you want column C to read both positive and negative values, but want to sum the absolute value of each cell within column C, then you could use an array formula like this:
[tab][COLOR=blue white]=SUM(ABS(C1:C2))[/color]
[!]entered with [Ctrl]+[Shift]+[Enter] instead of just enter[/!]! If entered correctly, curly brackets ({}) will appear on either side of the formula.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.