1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

IF and negative numbers

Discussion in 'Business Applications' started by BettyAtKitchen, Apr 22, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. BettyAtKitchen

    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.
     
  2. Zack Barresse

    Zack Barresse

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

    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.
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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? ;)
     
  5. BettyAtKitchen

    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?
     
  6. The Villan

    The Villan

    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
     

    Attached Files:

  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Plus you can sub total the Income and Expenses
     
  9. BettyAtKitchen

    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.
     

    Attached Files:

  10. The Villan

    The Villan

    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.
     
  11. slurpee55

    slurpee55

    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.
     
  12. BettyAtKitchen

    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.
     
  13. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
  14. BettyAtKitchen

    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.
     
  15. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    You are very welcome Betty. :)
     
  16. Sponsor

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 733,556 other people just like you!

Loading...
Similar Threads - negative numbers
  1. bavers
    Replies:
    3
    Views:
    189
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/706190

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice