My Vlookup returns the wrong info

I am attaching the file I use everyday. I create and hourly report that summarizes the previous intervals, calls. The hourly report is tedious and I thought I could use Excel to speed it up but I am running into some problems.

MyStatsCALC.xlsx
I am trying to tell StatsCalc!G5, StatsCAlc!G15, and StatsCalc!G25 to return a forecasted number of calls based on a vlookup using now() rounded to the last hour as the value. The formulas seems to work fine after noon, but the AM the values are always a half hour behind (10 am is displaying the 9:30 number)...this gives me an incorrect forecast percentage.

All the data on STAT.DATA is pasted from other sources, I report at the top of the hour for previous intervals, which is why I have totals in STAT.DATA! coulmns P,R,T.

What is the purpose of this?
FLOOR(MINUTE(NOW()),60)

That is always going to just be 0 right? So why not just put 0..

Don't know if that is the issue, but just an FYI..will improve performance a bit..


I think the issue is likely a "Floating Point Precision" issue.

Making it 1 second instead of 0, seems to pull the correct info.

B5: =IF(B15>0,VALUE(B15),VLOOKUP(TIME(HOUR(NOW()),0,1),STAT.DATA!$O$2:$U$50,2))

Hi,
You use the function "Floor" with date/time is not correct!
Pls find attached file for your reference,

Hope this helps,

Originally Posted by duanzhuanming

Hi,
You use the function "Floor" with date/time is not correct!
Pls find attached file for your reference,

Hope this helps,

I believe the OP 'Wants' to retrieve data based on the time rounded down to the hour, not half hour.

Jonmo1, you're the best. I was afraid that I was explaining poorly but this works perfectly. I'm currently taking Excel courses and using it on my job, my basic knowledge doesn't always get me where I'm trying to go (I was useing Floor to round down the hour, this was the only method I knew to force a time that I wanted). I appreciate that you included something for me to read up on too, that helps me understand where I went wrong.

Originally Posted by Jonmo1

What is the purpose of this?
FLOOR(MINUTE(NOW()),60)

That is always going to just be 0 right? So why not just put 0..

Don't know if that is the issue, but just an FYI..will improve performance a bit..


I think the issue is likely a "Floating Point Precision" issue.

Making it 1 second instead of 0, seems to pull the correct info.

B5: =IF(B15>0,VALUE(B15),VLOOKUP(TIME(HOUR(NOW()),0,1),STAT.DATA!$O$2:$U$50,2))

Glad to help, thanks for the feedback.