yogi_Setup A Sheet To View Project Status in Real Time

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #650   Jul 23, 2012     www.energyefficientbuild.com.


user tomgreeen said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Zn3CygQg9P4)
Using Googleclock() and Vlookup together
Hello

I'm trying to combine Googleclock() and Vlookup (or INDEX) to monitor a project in real-time by comparing the percent completed against how much should have been completed at this given point. I've done what I think should work but it doesn't yet, I was wondering if you have any ideas?

I've created a column with the date and time counting backwards by 1 minute at a time - as this is the unit that GoogleClock counts up with. (Incidentally if I could get GoogleClock to just show the time and not the date, that would simplify things a lot). Eg

TIME

23/07/2012 15:00:00

23/07/2012 14:59:00

23/07/2012 14:58:00

23/07/2012 14:57:00

23/07/2012 14:56:00

23/07/2012 14:55:00

In the next column I have the target percentages for each of those given times - calculated by taking the number of minutes in the entire project time (about 9 hours in this case) and dividing by 100:

% PROOF AIM

100%

100%

100%

99%

99%

99%

99%

99%

99%

98%

98%

I have other columns for other metrics I measure during the project but have left these out until I get this to work.

I want Vlookup to tell me what % we should be at the current time. This will then power a dynamic chart which shows whether we are ahead of or behind schedule in the various metrics.

So I have tried the formula vlookup(GoogleClock(),A:E,2)

This only seems to be returning the % associated with the bottom time on the list, rather than the time which matches the GoogleClock() time

I've also tried putting GoogleClock() in another cell and pointing the Lookup function at that, and also changing the formatting of the time column and the cell with the current time to Normal and Decimal formating but this doesn't seem to work either. It's weird as the numbers look exactly the same, but the function doesn't seem to be able to read them properly.

I've also tried it with the INDEX function:

=index(googleclock(),A:E,2)

...but I get the message Error: not a number: TIME with this formula. Though I may be doing something wrong as I don't know this function very well (not that I know Vlookup well either!)

Any ideas about what I'm doing wrong? Is there a way to turn the time-based columns into pure numbers so that these work?
Thanks
Tom

------------------------------------------------------------------------------------------------

following is a solution to the problem