Conditional Formatting and Rounding

[chef]

Hi all
apologies as must be simple solution

I have 2 tables and am just comparing each one from the other

eg e4-e17, e5-e18 etc and then showing the difference in another table
I put conditional formatting this new table on but on occasions, I assume because of rouding the 0.00 can show up in colour when meant to be blank

I can get around this but it means setting up yet another table and putting =round(e4,1) etc

is there a way I can use the formula in first table e4-e17 etc and also have =round to 1 decimal in same original difference table to save setting up so many additional tables as I have around 80 tables using comparison
Hope that makes sense.
regards

 

[oldbrewer]

give us an example of a pair of numbers that although different, colour as though they are the same

 

[chef]

It's a basic table taking one set of figures from one then from the other and showing difference.

I then put conditional formatting on that if >0 then red and if < 0 then green and show figures as appropriate
eg 8.49 in red or -3.45 in green.
what is happening on occasions is that the difference is 0.00 and sometimes shows a green 0.00 or a red 0.00 and is confusing users.

hope that makes sense

I can set up new tables and just put in =round to sort but was hoping a more simple way.

 

[oldbrewer]

you must not subtract - just see if A1 in sheet1 is identical to A1 in sheet 2 if it is, color green, if not color red

 

[chef]

sorry its probably me who has misworded issue.

I need to subtract to show difference and whether a positive or negative.
I can sort by adding additional table and =round works, but just a pain to do for so many tables.
many thanks anyways

 

[oldbrewer]

in that case, decide if zero is positive or negative..........

 

[chef]

in that case, decide if zero is positive or negative..........

ok thanks for trying to help.

I have obviouly misworded and was just hoping others may have come accross this situation.

I'll just sort by building additional tables and use = round as I have tested and works.

All I was looking for was to embed =round formula with a subtraction formula.

its fine and thanks for the replys..

 

[oldbrewer]

are you working to nearest whole number, one decimal place or what

if you round A1 and subtract rounded sheet2!A1 you should be there

=round(A1,1)-round(sheet2!A1,1)

 

[chef]

that worked perfect!

thank you so so much that and persevering with me.
I'm so grateful and you have no idea how much work you have saved me.

Cant thank you enough and have a great weekend
regards
ian

 

[oldbrewer]

of course you can change the 1 to 2 or 3 or zero - glad to help - hoping to see a flypast by a Lancaster, Hurricane and Spitfire.