Making a cell red if the first cell and the second cell's formula result don't match

[falconm11]

I am currently working on a spread sheet for my work place where I need to check whether the database's information is accurate compared to the hard copies. I wanted to make it easy to check through the 700 rows of information.

I have managed to get an excel document of the database information, with the different fields in their own columns. However, the database's information is exclusive of tax, which is 10% here.

As such, what I would like to do it lock the cells of the database information and place columns next to each database column field, so that I can input the hardcopy data and have a third column next to it which goes red if the database column data doesn't match the hard copy column data less 10%.

I have been using the formula (Hardcopy/11*10) to get the excluded tax amount, but I don't know how to get the column to turn red when the numbers don't match, and turn green when they do match.

I am placing an example here to help explain what I need

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Database[/TD]
[TD]Hardcopy[/TD]
[TD]Match/No Match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5.50[/TD]
[TD]5.0[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6.60[/TD]
[TD]6.1[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If at all possible, I would also like to make it only tolerant to 2 decimal places as all the figures are either cash related or percentages only detailed to 2 decimal places. (eg. $1.20 or 8.88%)

Thank you very much in advance. Any information you provide will save me hours of pain in the near future.

VERY IMPORTANT
I am using Microsoft Excel 2010, however since the list needs to be shared with a work mate as we are sharing the load on doing this list, the formulas need to work for Microsoft Excel 1997.

 

[Cosmic Wizard]

I am currently working on a spread sheet for my work place where I need to check whether the database's information is accurate compared to the hard copies. I wanted to make it easy to check through the 700 rows of information.

I have managed to get an excel document of the database information, with the different fields in their own columns. However, the database's information is exclusive of tax, which is 10% here.

As such, what I would like to do it lock the cells of the database information and place columns next to each database column field, so that I can input the hardcopy data and have a third column next to it which goes red if the database column data doesn't match the hard copy column data less 10%.

I have been using the formula (Hardcopy/11*10) to get the excluded tax amount, but I don't know how to get the column to turn red when the numbers don't match, and turn green when they do match.

I am placing an example here to help explain what I need

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Database[/TD]
[TD]Hardcopy[/TD]
[TD]Match/No Match[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5.50[/TD]
[TD]5.0[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6.60[/TD]
[TD]6.1[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If at all possible, I would also like to make it only tolerant to 2 decimal places as all the figures are either cash related or percentages only detailed to 2 decimal places. (eg. $1.20 or 8.88%)

Thank you very much in advance. Any information you provide will save me hours of pain in the near future.

VERY IMPORTANT
I am using Microsoft Excel 2010, however since the list needs to be shared with a work mate as we are sharing the load on doing this list, the formulas need to work for Microsoft Excel 1997.

Stop me if I am on the wrong track, but what may help is the If function
The formula is =IF(B2=C2,TRUE).
You can then use the Conditional Formatting tab in the Home section, Highlight Cell Rule, Text That Containing, type in True and then chose the font and fill colors that you want: repeat steps for False.

Database Hardcopy Formula Match/No Match
5.50 5.00 5.00 TRUE
6.60 6.10 6.00 FALSE

If still need to say Match or No match, let me know, will sort out a formula for you!

 

[falconm11]

Stop me if I am on the wrong track, but what may help is the If function
The formula is =IF(B2=C2,TRUE).
You can then use the Conditional Formatting tab in the Home section, Highlight Cell Rule, Text That Containing, type in True and then chose the font and fill colors that you want: repeat steps for False.

Database Hardcopy Formula Match/No Match
5.50 5.00 5.00 TRUE
6.60 6.10 6.00 FALSE

If still need to say Match or No match, let me know, will sort out a formula for you!

Thank you for your reply!

Your method does work, but I assume I would need to have the result in a column and then a column next to that which states whether it is true or false. I was hoping to limit the amount of new columns I have to make as I have an older screen, so the amount of space I can fit into my sheet without scrolling is limited by my lack of wide screen abilities. With the quick colour change, I was hoping to have a very small column for match or no match, something I can quickly check without needing to have a lot of width.

Also, I am not sure if it will stop numbers with three decimal places popping up.

 

[Cosmic Wizard]

Don't really understand your "wide screen abilities", it doesn't seem like that many columns???
You will have to have more columns if you want to compare data.
You can always you the hide button to not show a column.
Also if you want just two decimal points, just format it too two decimal places.
And if you want to quickly check the results, just put the filter on and screen by True or False, of the colors you have chosen, hope that helps.

 

[falconm11]

Don't really understand your "wide screen abilities", it doesn't seem like that many columns???
You will have to have more columns if you want to compare data.
You can always you the hide button to not show a column.
Also if you want just two decimal points, just format it too two decimal places.
And if you want to quickly check the results, just put the filter on and screen by True or False, of the colors you have chosen, hope that helps.

Sorry, what I showed was only a single database field example. In the real document I have 8 columns of data of a similar format. That is why I wanted to minimize adding additional columns to the spreadsheet.

I totally forgot about hiding columns. Feeling pretty stupid now.

I didn't know formula derived answers could be formatted like that, very helpful!

You have been a great help!

Thank you!