Excel/Numbers formula that says if two cells are the same?

Discussion in 'Mac Apps and Mac App Store' started by jent, Mar 1, 2011.

  1. jent macrumors 6502a

    jent

    Joined:
    Mar 31, 2010
    #1
    I have a Numbers spreadsheet that lists, side-by-side, two percentage levels. So column A lists the item, column B lists a percentage, and column C lists a percentage. There are a few thousand rows of items overall, and the large majority of the items themselves have the same percentage in columns B and C. What I'd like to do is create a column D, and have a formula that writes "SAME" if the two percentages in any given row are the same, and "DIFFERENT" if they're different. It would be an added plus if it could color them green and red, respectively. Is there a formula that will do this? It would be a great way to pinpoint the minority of items that have different percentages. The cherry on top would be if in addition to writing "DIFFERENT" in the D column for rows that don't share the same percentage, it could also list the difference in that cell so I could thus sort alphabetically/numerically and tackle the biggest differences first. Thanks for your help, and feel free to let me know if I can clarify!
     
  2. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
  3. jent thread starter macrumors 6502a

    jent

    Joined:
    Mar 31, 2010
    #3
    Yes, thank you! I have a few follow-up questions. How can I get it to include the text "Different" in addition to the difference itself? And secondly, is there a way to introduce a third variable and say "If column B or column C is empty for a given row, write 'Incomplete'"? I ask this because I have a number of rows for which I only have a percentage for column B or column C, and don't want to incorrectly flag them as different simply because some data is missing (they may very well be the same). Thanks again and I look forward to any additional help!
     
  4. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #4
    You can have one or the other, but not both in the same cell. Why would you even need to? The fact that it has an amount and is a different color will highlight it well enough. You can also sort by that column, so all the non-matching ones with values sort together. If you really need the word "Different", you can always put the difference value in another column.
    Yes, just add that criteria to your IF statements. You do know how to build formulas, right?
     
  5. jent thread starter macrumors 6502a

    jent

    Joined:
    Mar 31, 2010
    #5
    Great point. I'll leave it as it is.
    I'm just getting started. You've been a huge help so far, and I'd love it if you wouldn't mind sharing this tidbit too. Thanks!
     
  6. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #6
    Here:
    ScreenCap 7.PNG
     
  7. jent thread starter macrumors 6502a

    jent

    Joined:
    Mar 31, 2010
    #7
    Thanks very much! It makes great sense to me, and I'm wondering if perhaps Numbers uses formulas a bit differently. Here is my attempt to recreate what you did (keep in mind that my cells are C4 and I4 instead of B1 and C1 in my example). It tells me there's a syntax error with the way it's written.
     

    Attached Files:

  8. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #8
    I don't use Numbers, so I can't help you with that.
     
  9. Mal macrumors 603

    Mal

    Joined:
    Jan 6, 2002
    Location:
    Orlando
    #9
    You're missing the second and third sets of parentheses. After each IF, put a starting (, and at the end of each statement (which in this case just means the end of the formula), a closing ). The correct formula would be this:

    =IF(C4="","Incomplete",IF(I4="","Incomplete",IF(C4=I4,"Same",C4-I4)))

    I put the parts you're missing in bold.

    jW
     
  10. tjb1 macrumors 68000

    Joined:
    Aug 26, 2010
    Location:
    Pennsylvania, USA
    #10
    Try this

    =if(or(C4="",I4=""),"Incomplete",if(C4=I4,"Same",C4-I4))
     
  11. jent thread starter macrumors 6502a

    jent

    Joined:
    Mar 31, 2010
  12. tjb1 macrumors 68000

    Joined:
    Aug 26, 2010
    Location:
    Pennsylvania, USA
    #12
    Which one?
     

Share This Page