Microsoft Excel Question | FerrariChat
Buy

Microsoft Excel Question

Discussion in 'Other Off Topic Forum' started by Anthony_Ferrari, Nov 11, 2005.

This site may earn a commission from merchant affiliate links, including eBay, Amazon, Skimlinks, and others.

  1. Anthony_Ferrari

    Anthony_Ferrari Formula 3

    Nov 3, 2003
    2,365
    Sheffield, UK
    Full Name:
    Anthony Currie
    #1 Anthony_Ferrari, Nov 11, 2005
    I have a formula in a cell. If the value of the formula is zero I want the cell to display a tick instead of a zero. Is this possible?
     
  2. glennm19

    glennm19 Karting

    Nov 6, 2004
    204
    NJ
    Full Name:
    Glenn
    #2 glennm19, Nov 11, 2005
    This should work:

    =IF(SUM(A1,B1)<>0,SUM(A1,B1),"`")

    Just replace SUM(A1,B1) with the formula you want. If this example, if the formula SUM(A1,B1) does not equal 0, then the sum of those 2 cells is display. Otherwise, the tick symbol is displayed.

    Hope that helps

    Glenn
     
  3. ashsimmonds

    ashsimmonds F1 World Champ

    Feb 14, 2004
    14,385
    adelaide, australia
    Full Name:
    Humble Narrator
    #3 ashsimmonds, Nov 11, 2005
    problem is there's no font with a tick AND numbers in it, but you could possibly use the &#8730; which is a mathematical operator.
     
  4. Anthony_Ferrari

    Anthony_Ferrari Formula 3

    Nov 3, 2003
    2,365
    Sheffield, UK
    Full Name:
    Anthony Currie
    #4 Anthony_Ferrari, Nov 11, 2005
    I gave up and made it say "ok" if the value was zero instead.

    Thanks anyway guys. :)
     
  5. speedy_sam

    speedy_sam F1 Veteran

    Jul 13, 2004
    5,559
    TX
    Full Name:
    Sameer
    #5 speedy_sam, Nov 11, 2005
    Sorry I am late to the party but this is how you do the tick...

    Using glenn's formula

    =IF(SUM(A1,B1)<>0,SUM(A1,B1),char(252))

    if the sum =0 then you will see a u with two dots.

    Change the font of the cell to wingdings and you will see the tick mark
     
  6. glennm19

    glennm19 Karting

    Nov 6, 2004
    204
    NJ
    Full Name:
    Glenn
    #6 glennm19, Nov 11, 2005
    Thanks Speedy. I wasn't sure which character on the keyboard is officially called a 'tick'.
     
  7. ashsimmonds

    ashsimmonds F1 World Champ

    Feb 14, 2004
    14,385
    adelaide, australia
    Full Name:
    Humble Narrator
    #7 ashsimmonds, Nov 11, 2005
    you'd have to use a macro to do that though unless you were patient enough to do it manually everytime you see it, which kinda defeats the purpose of a formula.
     
  8. speedy_sam

    speedy_sam F1 Veteran

    Jul 13, 2004
    5,559
    TX
    Full Name:
    Sameer
    #8 speedy_sam, Nov 12, 2005
    There are easy ways of doing this. Anyway our key user has signed off and is onto better things :)
     
  9. arnaget

    arnaget Formula Junior

    Nov 4, 2003
    553
    New York
    Full Name:
    Jason
    #9 arnaget, Nov 12, 2005
    well, if you want to just have a "-" in place of any of the 0s, which seems to be convention (or what i would use as a "tick"), highlight the column where your sums are, right click on it, and go to "format cells." then, select "Accounting" as your number type. under the symbol field, you can remove the $-sign. and then adjust # of decimal points you want above. that should do the trick pretty simply.
     
  10. Anthony_Ferrari

    Anthony_Ferrari Formula 3

    Nov 3, 2003
    2,365
    Sheffield, UK
    Full Name:
    Anthony Currie
    #10 Anthony_Ferrari, Nov 14, 2005
    But I still have to change the font. I want it to work this way: If A1+B1=250 it displays 250, if A1+B1=0 it displays a tick instead of the zero. I don't think it's possible, so I have made do with it saying 'ok' if the total is zero.

    Thanks for the input guys.
     
  11. Challenge

    Challenge Formula 3

    Sep 27, 2002
    2,015
    PA
    Full Name:
    Kevin
    #11 Challenge, Nov 14, 2005
    What's wrong with a hyphen (-) ?

    But you're right...conditional formatting is only availble with font style (italic, bold, etc.) not the actual font.
     

Share This Page