Negative Number in formula adding instead of subtracting

[casexcel]

I received an answer from a previous post on how to make a row of numbers an automatic negative without having to type the - in front of each number. However, when I include that row in a formula for the Sum of, it counts it as a positive.

Is there a way to enter a number in a cell without having to enter the - before it AND the total sum recognizes it as a negative?

 

[Jon von der Heyden]

Hi

I assume that previous suggestion was only to change the format of the cell rather than change the actual values?

If you have a bunch of values that you need to flip then, in a spare cell enter -1, copy it, select the range for number and go to paste special > values > multiply > ok.

If however you want to flip the numbers as users type then you would need to go down a macro route although I don't see the point of this if it simply spares a user from entering a minus before the number.

Use Data Validation: Cell < 0 rule to prohibit a user from entering a positive value.

 

[Jon von der Heyden]

If you have a mixture of +ve and -ve numbers in a range then you could use a helper column to convert all to -ve. See example below:

Excel Workbook
	A	B
1	-672	-672
2	-467	-467
3	872	-872
4	161	-161
5	687	-687
6	491	-491
7	937	-937
8	-997	-997
9	759	-759
10	66	-66
11	934	-934
12	-14	-14
13	-154	-154
sheet

Excel 2003

Cell Formulas
Range	Formula
B1	=-ABS(A1)

 

[rallcorn]

in cell outside of your formula range enter -1.
highlight the numbers/cells you want to be negative NOT INCLUDING THE CELL WITH SUM FORMULA!!! while these are still highlighted: ALT+S+M (paste special, multiply)