Error returned (but not 100% of the time) when using IF with MATCH and INDEX

  • Hi,


    I have a list of start times in A1:A24 and I have an associated list of how long someone can work dependant upon what time they start in B1:B24. The input cells are D6 and E6 (and D10 and E10) and what I would like to achieve is, in cell F6 I would get an "Over" or "Under" based on the calculation and in cell G6 I would see by how much the times entered in D6 and E6 are over or under the allowance in B1:B24. The +1/24 section is because someone who starts work at 12:00 actually starts at 11:00 (I know, I know but that's the way it is). If this is causing an issue I know I could use a hidden helper cell but was trying to keep it as simple as possible. I have "broken" the formula into smaller parts and entered them seperately into other cells and they perform as I expect them to; it's just when I amalgamate them the error appears.


    What makes this all the more frustrating is that I have the same data in a copy of Excel 2013 and the formulae work if cell F6 is "Over" but returns FALSE in cell G6 if F6 is "Under".


    Thanks for looking
    Tim

  • Re: Error returned (but not 100% of the time) when using IF with MATCH and INDEX


    When you are manipulating time, sometimes Excel's precision is off.. so you need to reduce the precision level...


    Try:


    =IF(D6="","0",IF(INDEX($B$1:$B$24,MATCH(ROUND((D6-1/24),15),$A$1:$A$24,0))<((E6-(D6-1/24))+(D6>E6)),"Over","Under"))


    Also, in the buffer formula, you don't have an IF False argument, that is why you get FALSE when "Under" is result of Status...


    you need to also update the MATCH function there too..


    =IF(F6="Over",((E6-(D6-1/24))-INDEX($B$1:$B$24,MATCH(ROUND((D6-1/24),15),$A$1:$A$24,0))),"Need a what-if-false results here")

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Error returned (but not 100% of the time) when using IF with MATCH and INDEX


    Hi NBVC,


    Many, many thanks for the reply. The first section is perfect (might I ask what the ROUND helps with?).


    Now, with the buffer formula, I was trying to simplify it until I could work out why the formulas didn't work. I thought the ,"0" at the end was the "if it's not over then put 0". However, it appears I had missed the qualifier for the MATCH function out and, I think the ,"0" was being used for that. So, my new formula is


    =IF(F6="Over",((E6-(D6-1/24))+(D6>E6))-INDEX($B$1:$B$24,MATCH(ROUND(((D6-1/24)+(D6>E6)),15),$A$1:$A$24,0))),IF(F6="Under",INDEX($B$1:$B$24,MATCH(ROUND(((D6-1/24)+(D6>E6)),15),$A$1:$A$24,0)),"0")


    but it's returning #N/A


    I'm sure the IF statement is right ie if "Over" perform this, if "Under" perform that, if anything else display 0. I know I've now incorporated D5>E5 to cover timings crossing midnight but that shouldn't be a problem, should it.


    Thanks very much for what you've provided so far and, if my problem is syntax or not counting ( correctly just tell me to re-check my formula. If it's something more complicated, any help would be appreciated.


    Tim

  • Re: Error returned (but not 100% of the time) when using IF with MATCH and INDEX


    The ROUND() function is just rounding the result to 15 decimals (Excel only has a precision to 15 decimals), when you manipulate time by adding or subtracting it to other numbers, it gets messy, so we need to "control" it with other functions like ROUND().


    For the second formula, you placed the FALSE prematurely i.e. inside the wrong brackets.


    Try:


    =IF(F6="Over",((E6-(D6-1/24))-INDEX($B$1:$B$24,MATCH(ROUND((D6-1/24),15),$A$1:$A$24,0))),0)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Error returned (but not 100% of the time) when using IF with MATCH and INDEX


    Hi NBVC,


    Your bloody brilliant suggestion works if I add the bold section (which accounts for negative times)


    =IF(F6="Over",((E6-(D6-1/24))+(D6>E6)-INDEX($B$1:$B$24,MATCH(ROUND((D6-1/24),15),$A$1:$A$24,0))),0)


    I've now added (correctly) the second IF statement for "Under" and it works.
    Thanks again
    Tim

  • Re: Error returned (but not 100% of the time) when using IF with MATCH and INDEX


    Hello again,


    I've updated my formula to cover a wider time range and it works for most times. Certain times though return #NA for no reason I can see from a formula point of view. Is it another issue with how Excel deals with time?


    I've attached the file for reference


    Thanks
    Tim

  • Re: Error returned (but not 100% of the time) when using IF with MATCH and INDEX


    Try this confirm Control+Shift+Enter (in F5,F9...)
    =IF(D5="","0",IF(INDEX($B$5:$B$292,MATCH(ROUND((D5-1/24),10),ROUND($A$5:$A$292,10),0))<((E5-(D5-1/24))+(D5>E5)),"Over","Under"))


    With 15 digits after comma even ROUND/up will fail is applied to your criteria only.
    Apply this to your second formula

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!