Vlookup function returns some #n/a

As soon as you use MROUND or any other Rounding function with Times. You are Screwed.


Formula for A4 then fill down =ROUND(A$3+(0.5/60/24)*(ROW()-3),5)

Formula for E3 Entered using Ctrl Shift Enter then fill down. =INDEX($B$3:$B$215,MATCH(TEXT(D3,"hh:mm"),TEXT($A$3:$A$214,"hh:mm"))-1,1)

[.... deleted by me; see replacement below ....]

[Reposted with major changes]

In general, do not use a decimal fraction in the second parameter of MROUND. Often, the result is not a binary match for the equivalent constant.

And use some a form of --TEXT(...,"h:m") or --TEXT(...,"h:m:s") to round time calculations to the correct binary representation.

(Use "[h]" instead of "h" if the result of the time calculation is less than 10,000 hours. That is not the case in your calculations.)


First, change the formula in A4 to:

=--TEXT(A3+"0:0:30","m/d/yyyy h:m:s")

and copy down the column and format as desired.

Note that the TEXT format is sensitive to the way that dates are represented in your region (e.g, m/d/yyyy, d/m/yyyy or yyyy/m/d). Let me know if that is unacceptable.


Similarly, change the formula in D4 to:

=--TEXT(D3+"0:15","m/d/yyyy h:m")

and copy down the column and format as desired.


Finally, change the formula in E3 to:

=VLOOKUP(D3,$A$3:$B$215,2,FALSE)

and copy down the column.


-----
In a previous posting, I offered formulas of the form =INT(A3+"0:0:30")+TEXT(A3+"0:0:30","h:m:s") as an alternative that is not sensitive to the way that dates are represented in your region.

But I expressed some doubts about the use of INT in this context because of concerns for binary arithmetic anomalies.

Although such potential anomalies do not apply if you follow the steps that I outline above, they might indeed arise in other time calculations. I do not want to suggest a method that works only sometimes, at least not without explaining the special circumstances.

And as it turns, the potential problem arises in even simpler situations.

For example, suppose that a time calculation results in 2/27/2020 23:59:59.500 in A6.

Rounded to the minute, the correct result is 2/28/2020 0:00 (midnight), because Excel rounds time to the second when formatting "h", "h:m" and "h:m:s".

And that is indeed the result of the formula =--TEXT(A6,"m/d/yyyy h:m:s").

However, the formula =INT(A6)+TEXT(A6,"h:m:s") results in 2/27/2020 0:00. Obviously, that is incorrect.

The reason for the latter result is: the decimal value of A6 is 43888.9999999999, when rounded to 15 significant decimal digits, as the INT function does. So INT(A6) correctly (but unintentionally) returns 43888, the date serial number for 2/27/2020. TEXT(A6,"h:m:s") correctly returns "0:0:0" because, again, Excel rounds time to the second in that context.

In A3 use ROUND(formula,10)

Please Login or Register  to view this content.

Drag down
same in D4:

Please Login or Register  to view this content.

Drag down
Now it should works.