+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting working when it shouldn't!

  1. #1
    Registered User
    Join Date
    10-09-2013
    Location
    Dorset, United Kingdom
    MS-Off Ver
    Office 365 on Windows 7 Pro
    Posts
    17

    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.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,669

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


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,950

    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.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  4. #4
    Registered User
    Join Date
    10-09-2013
    Location
    Dorset, United Kingdom
    MS-Off Ver
    Office 365 on Windows 7 Pro
    Posts
    17

    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!
    Last edited by BigJonF; 04-01-2019 at 02:51 PM. Reason: Update

  5. #5
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    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. #6
    Registered User
    Join Date
    10-09-2013
    Location
    Dorset, United Kingdom
    MS-Off Ver
    Office 365 on Windows 7 Pro
    Posts
    17

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Conditional formatting not working
    By archimaitreya in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-08-2019, 05:47 AM
  2. [SOLVED] Why is my conditional formatting not working?
    By liamfrancis2013 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-29-2017, 11:16 AM
  3. [SOLVED] Conditional Formatting not working
    By timmtamm in forum Excel General
    Replies: 5
    Last Post: 03-15-2017, 09:45 PM
  4. [SOLVED] Conditional formatting not working
    By Jamidd1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-20-2016, 04:10 PM
  5. [SOLVED] Conditional formatting not working
    By boomboomblock in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-22-2013, 06:27 AM
  6. Conditional Formatting Not Working
    By mycon73 in forum Excel General
    Replies: 14
    Last Post: 07-16-2011, 01:39 PM
  7. VBA conditional formatting not working
    By LarryC in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-26-2008, 09:43 AM

Bookmarks

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