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

[jent]

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!

 

[GGJstudios]

You mean like this?:

​

 

[jent]

You mean like this?:

View attachment 273792​

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!

 

[GGJstudios]

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?

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.

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).

Yes, just add that criteria to your IF statements. You do know how to build formulas, right?

 

[jent]

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.

Great point. I'll leave it as it is.

Yes, just add that criteria to your IF statements. You do know how to build formulas, right?

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!

 

[GGJstudios]

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!

Here:

​

 

[jent]

Here:

View attachment 273802​

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.

 

[GGJstudios]

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.

I don't use Numbers, so I can't help you with that.

 

[Mal]

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.

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

 

[tjb1]

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.

Try this

=if(or(C4="",I4=""),"Incomplete",if(C4=I4,"Same",C4-I4))

 

[jent]

That did it. Thanks everyone!

 

[tjb1]

That did it. Thanks everyone!

Which one?