+ Reply to Thread
Results 1 to 4 of 4

Formating with a formula

  1. #1
    loulou
    Guest

    Formating with a formula

    I have a formula and then a label concatenated onto the end which works fine
    but now I realize I have to format the numeric portion since it is working
    with too many decimals. I know how to do this in access but not excel.

    The formula is:
    =IF(F10-H10>0,(F10-H10) &" LOSS",(H10-F10) & " PROFIT")

    In msaccess, I would just add:
    =IF(F10-H10>0,format((F10-H10),"fixed") &" LOSS",format((F10-H10),"Fixed") &
    " PROFIT")

    How do I do this in excel??


  2. #2
    Kevin B
    Guest

    RE: Formating with a formula



    Convert the subtraction to a string and then format the string using a
    format mask.

    Your True/False statements currenlty look like this:

    (F10-H10) &" LOSS",(H10-F10) & " PROFIT"

    Change them to:

    TEXT((F10-H10),"$#,##0.00") &" LOSS",TEXT((H10-F10),"$#,##0.00") & " PROFIT")
    --
    Kevin Backmann


    "loulou" wrote:

    > I have a formula and then a label concatenated onto the end which works fine
    > but now I realize I have to format the numeric portion since it is working
    > with too many decimals. I know how to do this in access but not excel.
    >
    > The formula is:
    > =IF(F10-H10>0,(F10-H10) &" LOSS",(H10-F10) & " PROFIT")
    >
    > In msaccess, I would just add:
    > =IF(F10-H10>0,format((F10-H10),"fixed") &" LOSS",format((F10-H10),"Fixed") &
    > " PROFIT")
    >
    > How do I do this in excel??
    >


  3. #3
    Dave O
    Guest

    Re: Formating with a formula

    Try this one:
    =TEXT(F10-H10,"$0.00")&IF(F10-H10>0," Loss"," Profit")

    How likely is it that F10-H10 will equal zero? In this scenario, a
    result of zero will be labeled as "Profit", just fyi.


  4. #4
    Andrew Taylor
    Guest

    Re: Formating with a formula

    Loulou, try
    =TEXT(ABS(F10-H10),"0.00") & IF(F10-H10>0," LOSS"," PROFIT")

    (this gives 2 decimal places)

    Andrew


    loulou wrote:
    > I have a formula and then a label concatenated onto the end which works fine
    > but now I realize I have to format the numeric portion since it is working
    > with too many decimals. I know how to do this in access but not excel.
    >
    > The formula is:
    > =IF(F10-H10>0,(F10-H10) &" LOSS",(H10-F10) & " PROFIT")
    >
    > In msaccess, I would just add:
    > =IF(F10-H10>0,format((F10-H10),"fixed") &" LOSS",format((F10-H10),"Fixed") &
    > " PROFIT")
    >
    > How do I do this in excel??



+ 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