Results 1 to 6 of 6

Limit Formula to 2 decimal places

This is a discussion on Limit Formula to 2 decimal places within the Excel Questions forums, part of the Question Forums category; Hi I am using this formula to return a value and a label =IF(NewGP-OldGP=0,"No Change",IF(NewGP-OldGP>0, NewGP-OldGP &" Increase",IF(NewGP-OldGP NewGP-OldGP &" ...

  1. #1
    Board Regular
    Join Date
    Oct 2006
    Posts
    1,147

    Default Limit Formula to 2 decimal places

    Hi I am using this formula to return a value and a label

    =IF(NewGP-OldGP=0,"No Change",IF(NewGP-OldGP>0,NewGP-OldGP&" Increase",IF(NewGP-OldGP<0,NewGP-OldGP&" Decrease")))

    value = NewGP-OldGP and where the formula is bolded I only need the standard 2 decimal format. I've tried, but failed.

    thank you for your time and help.
    Erock 24

    Use it up, wear it out, make it do, or do without.

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,541

    Default

    With a simpler example:

    =TEXT(A1,"0.00") & " label"
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Location
    Auckland, NZ
    Posts
    1,547

    Default

    Hi
    Try this
    =IF(NewGP-OldGP=0,"No Change",IF(NewGP-OldGP>0,Text(NewGP-OldGP,"#.00")&" Increase",IF(NewGP-OldGP<0,TEXT(NewGP-OldGP,"0.00")&" Decrease")))

  4. #4
    Board Regular
    Join Date
    Oct 2007
    Location
    Auckland, NZ
    Posts
    1,547

    Default

    Or you could format the cell to be:
    #,##0.00 Increase;-#,##0.00 Decrease
    Which has the advantage that it is still numeric and you can do further calculations from the result.

  5. #5
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,157

    Default

    Formula
    =NewGP-OldGP

    and format as
    #,##0.00 "Increase";-#,##0.00 "Decrease";"No Change"
    Office 2007/2010

  6. #6
    Board Regular
    Join Date
    Oct 2006
    Posts
    1,147

    Default

    thank you all for the help.

    I adjusted the formula with this concept because for my application it was the best fit.

    Text(NewGP-OldGP,"0.00")

    works great
    Erock 24

    Use it up, wear it out, make it do, or do without.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com