Tech Support Guy banner
Status
Not open for further replies.

IF and negative numbers

2K views 14 replies 4 participants last post by  Zack Barresse 
#1 ·
Hello. Please let me know how to make Excel work so that

IF
A1=B, then the number I put in is positive
A2=D, then the number I put in becomes negative automatically, in red font

Thanks.
 
#2 ·
Hi there, welcome to the board!

You can use Conditional Formatting for this. I'm going to assume you're using 2003? I believe that is the majority of users these days, since you didn't specify. CF is dependent on what cell(s) you have selected and what cell is active (note the singularity of 'active', as you can only have 1 active cell, but can have many cells in the selection, a VERY important distinction, especially with CF). Please describe for us how your data is structured and the entire range you want CF setup on.

Basically, in 2003, it would be to select your cell(s), pay attention to the name box, which will show you the address of the active cell, then go to Format | Conditional Formatting, set the formula to whatever you wanted. So if you selected all of A and the activecell was A1 you could use the formula...

Code:
=$A1="B"
Then set the format for that. Do the same for all of your other conditions. There is a limit of 3 conditions you can use, but if you have (or upgrade to) 2007 that limit is taken away and you now have a Conditional Format Manager, which is really nice.

If, however, you are wanting to affect another cells result (displayed value, as opposed to format), then you'll need a formula.

Make sense? HTH
 
#3 ·
I am using Excel 2007. And yes, I probably would need a formula. My data looks like this:

A1=expense
A2=income

B1=600
B2=1000

If data on columnA is expense, I would like the amount on columnB to be negative, in red, when i put in the amount.

If data on columnA is income, I would like the amount on columnB to be positive, in black when I put in the amount.
 
#4 ·
But what is stopping you from must formatting the column B?? Can you post a sample file? I do not understand why you can't just input negative numbers for expense and positive numbers for income. Isn't that what they are? Or are you just being lazy and wanting the program to do it all for you? ;)
 
#6 ·
I would have one column headed Income and one headed Expenses and a third column that calculates the balance. Much clearer and less subject to errors.

Open attached file - yellowcells =input cells, Blue cells=formulas, balance column formatted to Number, 2 dec, ' on 000's, Red minus format
 

Attachments

#7 ·
Well, having each item out like their own record has its advantages, and would probably be the way I would go. This leaves better data structure (IMHO) to manipulate later on, especially for things like pivot tables.

To format the data, select the cell(s) in question (or perhaps an entire column) and press Ctrl + 1. There are number formats natively which will do what you're asking.
 
#8 ·
Plus you can sub total the Income and Expenses
 
#10 ·
First observation is that you cannot just type a value into a cell and expect Excel to understand that each record with d in column B, should have red text.
As Zack says, you need to type the value with a minus infront and then format the value cells.
As far as I am concerned unless you are prepared to put a minus in front of the value, then you need to go to two columns as I already suggested.

Its your call.
 
#12 ·
I attached a sample sheet. I tried Zack and The Villan's suggestions, formatted cells as currency to show as red font when negative, and also manually input the minus sign.

Based on the attached sheet, is there a way to get a running balance for EACH agent.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top