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

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.

Zack Barresse

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

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.

Zack Barresse

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

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?

The Villan

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

• 14.5 KB Views: 191

Zack Barresse

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.

The Villan

Plus you can sub total the Income and Expenses

BettyAtKitchen

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

• 19 KB Views: 151

The Villan

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.

slurpee55

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

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

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.

Zack Barresse

You are very welcome Betty.

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.

As Seen On