# Posts by Tim Weipers

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

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

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

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

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

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

• ## 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