+ Reply to Thread
Results 1 to 11 of 11

Display formula value as a negative number

  1. #1
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Display formula value as a negative number

    In the false part of the If statement below (F3-E3)/E3), I would like the result to display as a negative number regardless if the result is a positive number. Can someone help me do this?


    =IF(F3-E3/E3<"1",ABS(F3-E3)/E3,(F3-E3)/E3)
    Last edited by rhudgins; 03-04-2011 at 03:31 PM.

  2. #2
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Display formula value as a negative number

    =if(f3-e3/e3<"1",abs(f3-e3)/e3,((f3-e3)/e3)*-1)

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Display formula value as a negative number

    How about

    = -ABS((F3-E3)/E3)
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Display formula value as a negative number

    Neither of those solutions work. They both display positive percentages. The formula is in H3
    Attached Files Attached Files

  5. #5
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Display formula value as a negative number

    First: You shouldn't have <"1" it should be <1
    "1" is text

    Second: You are missing the brackets for your test: F3-E3/E3 instead of (F3-E3)/E3

    Third: You missed the minus sign and brackets from ChemistB's suggestion

    Your formula should be: =IF((F3-E3)/E3<1,-ABS((F3-E3)/E3),(F3-E3)/E3)

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Display formula value as a negative number

    Actually, with my formula, you don't need the IF statement at all

    = - ABS((F3-E3)/E3)

  7. #7
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Display formula value as a negative number

    Are you sure? OP is testing for less than 1, not less than 0.

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Display formula value as a negative number

    I tried a few examples and seems to be consistant. Can you give an example where it doesn't work? I may be wrong. It happens.

  9. #9
    Forum Contributor
    Join Date
    01-05-2010
    Location
    New York
    MS-Off Ver
    Excel 2016
    Posts
    747

    Re: Display formula value as a negative number

    Thanks for all of your help!!

  10. #10
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Display formula value as a negative number

    ChemistB

    I guess it's moot now (because it's SOLVED) but with your formula you always get a negative result. The IF() version gives a positive result when F3>=E3. I interpreted OP's request to account for that.

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Display formula value as a negative number

    You're right Cutter. I was looking at it wrong. :-/ Nice call.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1