Always Be Negative

[Justinian]

I have a spread with 40 + tenants, filled with rent amounts. Columns B, D, and F always need to be negative. I have to import numbers into these columns each week and I hate changing each cell to negative - it takes too long. I tried custom formatting and that worked except for one thing: The sum formula adding the rows in column G thinks these numbers are positive and it adds them as such.

How can I make this spread so that any number entered into columns B, D, and F are always negative AND have the sum formula adding these rows interpret them as negative?

 

[njimack]

A few options...
-You could amend your sum formula, e.g. =-SUM(A1:A10)
-You could multiply all cells by -1. Enter -1 into any empty cell, copy that cell, select the range with the rent amounts, use Paste Special>Multiply
- Use the custom formatting you have already applied, but also use it on the SUM function (I wouldn't recommend this approach).

 

[danzon]

I would just adapt the Sum formula ....

=SUM(-A1,B1)

something like this so that the contents of A1 are converted to negative before being added to B1

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 Or Target.Column = 4 Or Target.Column = 6 Then
    Application.EnableEvents = False
    Target.Value = -Target.Value
    Application.EnableEvents = True
End If
End Sub

 

[Justinian]

Thank you for the repsonses. I'm trying to avoid VBA since I just tried that code and something went wrong. I also tried multiplying by -1 last week and that just takes too long. I guess i just want Excel to recognize and properly sum my custom neg formats.

 

[xenou]

As you discovered, formats only change the appearance of cells, not their actual values.

Adding to the formula solutions proposed to handle this in your calculations, maybe more robust would be:
=SumProduct(ABS(A1:A10))
This way you don't change negative numbers to positive numbers (if it is possible that numbers in the column could be either positive or negative).

Another spreadsheet design might be a helper column to first change the values to absolute, then sum them. Perhaps this seems awkward to you but it has the advantage of making all the steps very clear in your worksheet, and is easily maintainable, as well as easy to pass on to others even if their Excel skills aren't very strong.

Excel Workbook
	A	B
1	Orig	Helper
2	1	1
3	2	2
4	-3	3
5	4	4
6	5	5
7	-6	6
8	7	7
9	8	8
10	9	9
11	10	10
12	37	55
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B2	=ABS(A2)
B12	=SUM(B2:B11)
A12	=SUM(A2:A11)

 

[Justinian]

Is the ABS formula the best way to go about if if those three columns ALWAYS need to be negative but also to sum as negative?

 

[rsulliva]

A simple (10 second) way to change all selected numbers to a negative is to:

1) find a blank cell
2) enter a -1 in this cell
3) right click and hit copy
4) hightlight all cells that you want to be a negative
5) right click select paste special
6) check values
7) check multiply
8) hit enter.
Done

And if you are always using the same columns you could even record a macro and assign this to a button for a simple one click operation.

 

[pbornemeier]

If there was a negative number in a cell then Post #8 would return a positive value in that cell.

To ensure the total was always correct, you could use

=-SUM(ABS(A2:A11))

and finish with Ctrl+Shift+Enter

This will sum the absolute value of each number in A2:A11 then negate it.

This will have no effect on the individual numbers. The only way to do that would be with a helper column or through VBA.

 

[Armando Montes]

One more
=SUM(A2:A11)*-1