Sep 26 2022 07:24 AM
I noticed when using VLookup I'm getting incorrect returns. The answer the formula provides is actually from the column before the correct ansewer. This has recently started happening, and has been working correctly in many years of using. Even in older workbook from 3 years ago its started doing this.
Sep 26 2022 07:33 AM
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Sep 26 2022 07:34 AM
Sep 26 2022 09:28 AM
@ere...on sheet 2023 i'm looking under "Take Home" and 2023rates is referanced
Sep 26 2022 09:29 AM
@Hans Vogelaar Under sheet 2023, "Take Home Pay", referances the table on 2023rates sheet.
Sep 26 2022 09:42 AM
It is a rounding problem. The times in column E are stored as fractions, and Excel cannot represent most fractions completely accurately. Hence the sum can deviate from the 'true' value by a tiny amount.
Since the first column on the 2023rates sheet increased in steps of 0.5, you can avoid the problem by rounding the sum in column I to 1 decimal place:
=ROUND(SUM(E2:E14)*24,1)
Alternatively:
=SUM(24*TEXT(E2:E14,"h:mm"))
Sep 26 2022 10:13 AM
Sep 26 2022 10:50 AM
Sep 26 2022 12:47 PM
Column E contains values in time format h:mm, but column I contains decimal hours.