# Conditional Formatting working when it shouldn't!

1. ## Conditional Formatting working when it shouldn't!

Hello,

I created a spreadsheet which helps us at work with the cashing up. It compares money in the tills and the print outs from the card machines (Z-reads) to what our software says. This can all be done with a calculator and pencil, but it just saves time at the end of the night.

At the bottom, (A24:J29) is where it compares the figures to see if it matches. I have set it to highlight the row, if it doesn't balance, using conditional formatting. Also, in H24:H29, I have put in some simple IF formulas to say which way round it is out - i.e. if it is £10 out, then does the card machine have £10 too much or our software. If it balances it stays blank.

All is working, except that one of the rows is applying the conditional formatting, and the formula in column H to say that the till is down, even though it balances. One other thing I found is that in J18, where it says if there are any till discrepancies, it shows the amount as -£0.00. This should not have a negative as far as i can see.

I can easily resolve this by starting again - it wouldn't take very long, but I would love to know what is causing the issue!

Thank you.

2. ## Re: Conditional Formatting working when it shouldn't!

Here's why:

Excel 2016 (Windows) 32 bit
F
24
£0.00
25
-£0.000000000000045297099404706
26
£0.00
27
£0.00
28
£0.00
29
£0.00
 Sheet: Sheet1

It's a rounding issue.

3. ## Re: Conditional Formatting working when it shouldn't!

Try multiplying the two relevent cells by 100000000000000

Both return 4.53

You need to use round() or Int() to get rid of the small differences.

4. ## Re: Conditional Formatting working when it shouldn't!

Wow!

Thank you to both of you for your super-quick answers. I can use rounding to solve the issue (I haven't used it for ages but I will remember how - a challenge for me!).

However, I don't understand why this tiny number exists when there is only addition and subtraction on the sheet. I can see where it first starts to appear, in C18, where the formula is =SUM(C4:C15)-350, the answer is coming out at 6.54999999995. I have looked at the column it is adding up and they all seem to be to a maximum of 2 decimal places.

Sorry, I am not trying to create more work by asking the question, it is just that I don't understand and would really like to.

Thanks,

Jon
Ps, sorry, I just realised that I attached the version of the sheet that is protected.

EDIT - I have done the Round function and it is all working, thank you to you both!

5. ## Re: Conditional Formatting working when it shouldn't!

Check out the links on this post for information on Excel's floating point arithmetic, and why there are times it seems really screwy.

6. ## Re: Conditional Formatting working when it shouldn't!

Great, thank you for the link, I will take a look.
As always, those on this website comes to the aid of us 'Excel novices' brilliantly.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1