Calculating neg and pos in same formula

[Jstdv8]

I would like to make a formula that would allow me to take a series of cells and add them up AND subtract them from another cell.

So say A1 is my original number and then each time someone takes something out of stock I enter it into A2, then another customer comes and takes more and that comes out of A3 and so on through A7
I don't want to have to enter the values in A2-A7 as negative numbers, and I dont want the value of A8 (which is the total cell that the formula is in) to be a negative.
Right now I'm using =sum(A2:A7)-A1, but the total in A8 showes up as a negative number when really its a possitive number I need to show.

Thanks in advance,
Justin

 

[boblarson]

Have you tried:

=Abs(sum(A2:A7)-A1)

 

[Jstdv8]

genius!
I never would have gotten there by myself. thank you so much!
Justin

 

[boblarson]

Not a problem. Glad we could help.

 

[Jstdv8]

Bob,
Ok so I was using my new spreadsheet and I came up with a problem I need resolved with the same formula. Everything works as i had hoped. But I need it to show negative values when when the quantity I'm taking out of stock (A2-A7) exceeds the quantity i input in A1.
Since we used absolute numbers to make the the formula it shows only positives in both situations.
So, say I have A1 as a value of 10 and A2 is 4, Then A8 (which is the final value with the formula in it) would show 6 but if i were to put a value of say 8 into A3 it needs to show up as -4 and right now it shows up as just 4.
even if they showed up in red lettering when they turn negative that would be fine too. i just need it to throw up a flag for my inventory.
Thanks in advance for all your help
Justin

 

[andreas_udby]

I guess I'm not fully understanding the situation, but it seems like

=A1-(sum(A2:A7))

...should accomplish what you're trying to do, in both cases. Then you can add conditional formatting to A8 if you like to make it pop out in red-bold text if the inventory goes negative.

 

[Jstdv8]

thats exactly it. I was making it way more complicated than it had to be.
Thank you gaain for your timely response, this site is a lifesaver