Posts by Tim Weipers

    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


    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

    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