IF and negative numbers

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

BettyAtKitchen

Thread Starter
Joined
Apr 22, 2008
Messages
116
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.
 
Joined
Jul 25, 2004
Messages
5,456
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.
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
 

BettyAtKitchen

Thread Starter
Joined
Apr 22, 2008
Messages
116
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.
 
Joined
Jul 25, 2004
Messages
5,456
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? ;)
 

BettyAtKitchen

Thread Starter
Joined
Apr 22, 2008
Messages
116
If Excel can do it, then why not?

Would you know how to make Excel automatically make the font RED, when the number is negative, like in columnB? Can this be done?
 
Joined
Feb 20, 2006
Messages
2,255
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

Joined
Jul 25, 2004
Messages
5,456
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.
 

BettyAtKitchen

Thread Starter
Joined
Apr 22, 2008
Messages
116
Zack: Thanks for the Ctrl+1.

The Villan: Thanks for the idea on running balance.

Zack and The Villan: I created a sample sheet. Maybe that will help me explain how I would like to be done.
 

Attachments

Joined
Feb 20, 2006
Messages
2,255
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.
 
Joined
Oct 20, 2004
Messages
7,837
Per Zack and The Vilan's suggestions, I would enter this in the adjacent cells in column D
=IF(B12="d",C12*-1,C12)
and then format those cells in D as numbers which turn red when the number is negative.
 

BettyAtKitchen

Thread Starter
Joined
Apr 22, 2008
Messages
116
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.
 

BettyAtKitchen

Thread Starter
Joined
Apr 22, 2008
Messages
116
Zack,

I tried it, and got the result I wanted... for Excel to do the computation for me. Once again, learned something new from you. Thank you.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top