+ Reply to Thread
Results 1 to 7 of 7

IF function gives unexpected result with different cell numbers

  1. #1
    Registered User
    Join Date
    07-30-2008
    Location
    UK
    Posts
    4

    IF function gives unexpected result with different cell numbers

    I’ve converted a paper staff timesheet to an electronic version in Excel, one month to a page, which tracks rostered hours (Col D), hours actually worked (Col F) and automatically calculates Time Over, using =IF($F20>$D20,$F20-$D20) in column M, and Time Back (using =IF($F20="",$D20-$D20,IF($D20>$F20,$D20-$F20)) in column N, with conditional formatting to hide function results I don’t want to be visible.

    Never having used the IF function before, I was pleased with myself until a number of us discovered that, having worked 10 hours, instead of this being displayed as 3 hours Time Over the rostered 7 hours, it was displayed in the Time Back column as -3 hours. Since 10 is clearly greater than 7, I can’t understand this.

    On checking, any number 10 or over does this – up to 70 ! 70 then switches back properly to the Time Over column as 63 hours over, up to 99. At 100, it switches to the wrong column again as -93 hours Taken Back. It’s OK then between 700 and 999 but switches again at 1000. It looks like it’s something to do with the first digit compared to the 7 but I haven’t got a clue what.

    It’s more infuriating because, if I enter exactly the same formulae I’m using from scratch on a new sheet, it works perfectly and if I copy it back into my monthly timesheet, it now works perfectly on that line - but drag copying or copy and paste with that now working formula doesn’t correct the other cells – they still produce the error!



    As this timesheet is used by a group of staff, I really need to understand why this is happening and come up with a solution.

    Thanks in anticipation.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    You might be better uploading you workbook, or at least a representative sample of it, so we can take a look. I note that your two formulae don't have the three elements usually required in each IF() statement if you don't want a default 'FALSE' to apply.

    i.e. IF(This=That,This,That)

    Difficult to say if this is affecting your result without seeing the workbook. Maybe not, but something clearly is.

    Rgds

  3. #3
    Registered User
    Join Date
    07-30-2008
    Location
    UK
    Posts
    4
    Thanks Richard - worksheets as suggested - a month page showing the errors and the new worksheet where entering the same formulae worked (some dummy data in both sheets to show the pattern referred to in the original post).

    Thanks.
    Attached Files Attached Files

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    Your problems stem from a couple of misunderstandings. First of all if you check the 'numbers' in columns D & F you'll find they are Text, not Numbers. Use the formula =ISNUMBER(D20) etc. to confirm this.

    The fact that they are text doesn't stop Excel being clever enough to recognise that because you are including arithmetic operators you intend them to be numbers, and it treats them as such. However the IF() test is something different.

    Note what happens for instance if in F19 you enter "=+1+1.5" instead of just "2.5"

    When you use =IF(D19>F19... since D19 & F19 are strings you're comparing the first character of D19, i.e. 7, with the 1st char. of F, i.e. 2. In this case the test is true hence you get 4.5 in N19. Now it just so happens that here D>F and hence you get the 'right' answer of 3. On Row 20, you are still getting the right answer -3 since the 1st char. of D20 is > the 1st char. of F20 (i.e. 1), it's just that you're 'thinking' it's the wrong answer. As far as XL is concerned it is the right answer.

    If you format columns D, F, M, N & O to be numbers rather than text, and then re-enter the numbers you'll be OK.

    As I mentioned earlier, your IF() formula don't have a third default element, which in some case can lead to odd results. You'd be better changing the formulae in M & N to

    Please Login or Register  to view this content.
    If you don't want the zeros to show it would be best to switch them off in the Tools Options View menu. Otherwise if you replace the last zero in the column N formulae above with "" , you'll find that you get errors in col O, and will have to modify that to say

    Please Login or Register  to view this content.
    HTH

  5. #5
    Registered User
    Join Date
    07-30-2008
    Location
    UK
    Posts
    4
    Thanks for this Richard.

    Your explanation re having the numbers formatted as text is really helpful (don’t actually know why they were but I had noticed that before, had changed them to General (like mu new sheet which had worked first time and then tried Number but it had made no difference, so I hadn’t mentioned it, being worried, as a first time poster, that, if my post was too long, no-one would read it – sorry if that was wrong).

    And when I selected all the offending cells and formatted them as numbers this time, it made no difference here either – until, after a lot of checking out on my part, I re-read your post and realised I had glossed over your next instruction to ‘then re-enter the numbers’ - this worked perfectly!!

    I would never have thought of that one as I had assumed that changing the format changed the format if you see what I mean! as when you change date formats for example and immediately see the result. (In passing that must mean more generally that a cell could look like it’s formatted as one thing but could actually be something else if the format has been changed but the cell not re-entered?)

    I’ve inserted the third element as you suggested just in case and kept my nested IF in column N to cover for having rostered hours but no hours worked (annual leave), which works perfectly, and used the Tools Options re the zeros, which I’d never registered before as an option.

    Many thanks for your help – I genuinely think it’s wonderful that people like yourself take the time to help others in this way, in my case having been driven to desperation at getting so close to achieving something useful but falling at the last hurdle.

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    Glad to be of help, and thanks for the feedback.
    Excel is an extremely powerful tool, but sometimes, as in this case where text characters look like numbers, it can be quite confusing. You can always test the cells with either the ISNUMBER() or ISTEXT() functions, which return either True or False. Usually the first sign that you're dealing with a text 'number' is that it left aligns in the cell, although if you happen to have right aligned the cell you still won't notice.

    As you've discovered it isn't sufficient just to change the formatting since this doesn't affect the underlying value. You have to first either re-enter the number, or if there are several cells that need to be changed to numbers, the following is a useful technique. Enter a proper number 1 in a spare cell and then Copy it. Now select the range of cells that you want to change and from the menu choose Edit Paste Special Multiply. This multiplies all the text number values by 1 and forces them to become proper numbers, and in the process changes the formatting from Text to General. Multiplying by 1 of course doesn't alter the value. The same technique would also work if you copied a zero and used Paste Special Add.

    Regards,

    Richard

  7. #7
    Registered User
    Join Date
    07-30-2008
    Location
    UK
    Posts
    4
    The good stuff just keeps coming - a fantastic way to change a lot of numbers stored as text in one go - worked beautifully!

    Sorted.

    Many thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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