Results 1 to 4 of 4

Thread: If cell has a currency with cents, show that value otherwise show the #
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2016
    Posts
    216
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation If cell has a currency with cents, show that value otherwise show the #

    Hello -looking for some help. Thanks in advance!


    A1 = $12.55

    B1 - If A1 has any cents (.01 - .99), show that full # or $12.55. If there are no cents (i.e. 12.00), then show only the $, not the cents or $12

    Example 1
    A1 = $12.55
    B1 = $12.55

    Example 2
    A1 = $12.00
    B1 = $12

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,659
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: If cell has a currency with cents, show that value otherwise show the #

    There are a few ways to do this. Assuming you have A1 and A2 formatted as currency, you can use this formula in A2:

    =IF(INT(A1)<>A1,A1,TEXT(A1,"$#"))

    The downside is that when it is an even dollar amount, what comes out is a text string, not a numeric value. But that might work for you.

    Another way is to use Conditional formatting. Select A2, click Conditional Formatting > New Rule > Use a formula > and enter:

    =INT(A1)=A1

    click Format... > Number > Currency > and scroll the decimal places to 0. Or choose whatever format you want. Click OK. That will leave the value as a number, but display it as you desire.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    Board Regular
    Join Date
    Apr 2016
    Posts
    216
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If cell has a currency with cents, show that value otherwise show the #

    This works fantastic! Thanks so much!

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,659
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: If cell has a currency with cents, show that value otherwise show the #

    Happy to help!

Some videos you may like

User Tag List

Tags for this Thread

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
  •