LOOKUP & Formatting

[opsman]

Hi

I'm wondering if anybody can help out with this problem I'm having extracting data from a spreadsheet. I have attached a fairly detailed example of what is required....

The formula required in terms of the calculation is really quite simple, the problem is the locations of the data field are unpredictable (I have removed other surrounding data for ease of understanding of what calculation is required).

Any questions I'm all ears as this one has been hounding me for some time and I reckon it probably just needs a fresh set of eyes to look at it.

The expected results are in the columns highlighted in yellow. The spreadsheet is in excel 2003.

Thanks
p.s. You'll notice the formatting on what is a very simple formula doesn't work on one of the example calculations. Both cells within the calculation are formatted as hh:mm, I can't figure out why this doesn't work either??

[xenixman]

It appears that your mixing your Dates & Times.

Some of your cells contain Date & Times while some contain only "Time".

Excel records Dates & Time as the number of Days since 01/01/1900 with the time portion as a fraction of a day.

So the current Date & Time is: 9/15/2010 12:00 PM

but Excel stores it as: 40436.50

with the date part being 40436 days since 01/01/1900
and the time as .50 percent of a 24 hour day.

Look at the examples at this site:

http://www.mvps.org/dmcritchie/excel/datetime.htm

Haven't tried it, but changing the cells that contian only Time to Date & Times should correct your problems.

[opsman]

Hi thanks for that. You actually have confused me slightly there. Are you talking about the cell formatting, as visibly there are only times (24 hour) in my example?

That was also actually a secondary and more general issue. The main priority that appears to be holding me up is the ability to locate the different fields required to make my calculations, bearing in mind the example is small the real thing tends to be hundreds of rows long each time, do you have any suggestions for that issue?

You help as always gratefully received.

[NBVC]

It should be simple formula like:

=IF(D2="","",D2-B2)

copied down... and format as appropriate...

[opsman]

Hi

Once again thanks for that. I think perhaps my example sheet has confused you;

The formula you mention would work perfectly if both bits of data remain on the same row. Unfortunately in this case whilst "created" and "time" columns data regularly (but not always) appear on the same row, we can be fairly certain the arrival and finished times are always on seperate rows. The seperation is determined by a number of factors including how many updates there have been.

The cells which are empty in the example spreadsheet would hold various details that we are not interested in for the purposes of this spreadsheet, but they would not be empty.

The challenge given to me was this; to create this without resorting to VB in order that I can hand over to colleagues who will also need to understand the mechanics of the spreadsheet in the future. Most of the rest is done, it's just this last piece of the jigsaw that's creating merry hell for me!

I bet there's a DonkeyOTE moderator around here somewhere that can help??

[NBVC]

Hi
I bet there's a DonkeyOTE moderator around here somewhere that can help??

Well then, good luck!

[xenixman]

The point I was making about your Times & Dates refers to cell M44 where you get the ###### display.

If you display your dates as numbers you will see that

cell J44 is 0.53

while

cell F42 is 40429.52.

Doing your math of =J44-F42

You get: 0.53 minus 40429.52 equals minus -40428.99

Trying to display a minus number as a date or time results in ########


Additionally, if you display all your cells as custom format Date & Time of mm/dd/yyyy hh:mm, you will see that

Cell J44 displays as 01/00/1900 12:38

while

cell F42 displays as 9/8/2010 12:24


Best advice, use complete dates & times if your doing Date & Time math.



AS for your LOOKUP question, you could use something like:

=IF(J7="","",J7-VLOOKUP($E$1,$E$2:$F$100,2,FALSE))

in cell M7

The down side is you will have to specify each section limits to the VLOOKUP, so in cell M28 it would have to be

=IF(J28="","",J28-VLOOKUP($E$1,$E$14:$F$100,2,FALSE))


For the K & L columns as NBVC pointed out, just dragging the formula down should be sufficient as the Dispatch & Arrival times are only one row apart.

EDIT:

The Dispatch & Arrivals are not always one row apart, so a VLOOKUP similar to about will probably be needed.

[opsman]

NBVC - I think I need to apologise? I wasn't meaning you weren't helping, on the contrary I am very pleased for your help. Simply Donkey OTE always seems to appear like a gene when required! (although perhaps I exhausted my 3 wishes as he doesn't appear anywhere close right now! )

xenixman - That looks real interesting and I'll see what happens on a live doc. I guess the difficulty is (as you say in your edit) getting the range correct so as not to pick up two times of the same nature in one VLOOKUP.

I'll get back to you both with what happens.

Thanks once again

p.s. Just seen your formatting comment. I didn't actually format anything on the worksheet and was initially quite vexed with what you were saying. Totally understand now, but a little vexed as to how it formatted into a date time group as opposed to the time group only in the first place. I guess it must have carried over in the copy and paste from the original.

[Cheeky Charlie]

He is like a gene, somewhat helical.

There are two issues:
Your dates + times (which has been explained) need to be all dates + times or all times, but not a mix.

Once this is done, this might work (expanding on xenixman):
K4
=IF(D4="","",D4-B4)
L4
=IF(F4="","",F4-VLOOKUP(A4,A:D,4,0))
M4
=IF(J4="","",J4-INDEX(INDEX(F:F,MATCH(A4,A:A,0)):INDEX(F:F,MATCH(A4,A:A,1)),MATCH("Arrival",INDEX(E:E,MATCH(A4,A:A,0)):INDEX(E:E,MATCH(A4,A:A,1)),0)))


I think the way you have your data organised is not very practical. A database would be best, second choice is to have all information relating to any given item on one row (your examples would have 6 rows and a lot more columns, but the maths would be terrifically more sensible and the whole thing would be more reliable - and that is the point, clever formulae are not reliable and not easy to check).

hth

[opsman]

The dates and times thing was just that....one of those things! Not to worry too much about that anyway I must have been playing at some stage and forgot to change it back or something.

You are correct of course and the info does come in CSV directly out of a database. The problem is we're conducting an experiment and using our existing SQL database in a way it was never really designed to be used in order to capture the necessary data for the experiment. The solution is not intended to be long term, hence the requirement for others to simply manage the spreadsheet until the experiment is proven and a long term solution can be delivered by the pros (not me!).

I'll check out what you've got there it looks real interesting and new to me actually.....got a feeling it could be a late night!

Anyways, thanks to all three of you....I'll report back in a bit.

[opsman]

Cheeky Charlie, xenixman and NVBC..... First of all many thanks to you all....

Cheeky Charlie - The icing on the cake, it certainly works in the example, now I've got to go away and figure out what you've done and see if it still works amongst other data.

What I thought would be the simplest of items turns out to be a little more complex (I think). First I couldn't understand the difference in formatting and having gone back to change it to hh:mm, it says it's already formatted to exactly that, although the formula box clearly shows the full date time group. So, I attempt to reset it which doesn't appear to cause any problems, yet didn't solve it either....it wasn't until I changed each cell in the example sheet on the formula bar to the numeric of hh:mm that I thought the change stuck. It didn't. Any ideas what may be stopping the change and is this a bug with excel 2003?

Thanks again to you all, I'll close this post out once I've looked at the main export sheets (just in case!)

[opsman]

Fantastic, Awesome, Brilliant......

I take the final points into account and if it were to be something long term, having seen the results in the live doc it could have potential issues for people to maintain, but it's absoloutely fit for what I need in the short term and a marvellous help. What's more having transferred it across I now understand every bit which always makes it 100% times more satisfying.

Thankyou so much.

[xenixman]

Without seeing your CSV data, I would suspect that the cells with just the Time associated with them are just that, Time with no Date attached, this will undoubtedly cause you more grief, especially when your data crosses date lines from one day to another.
No matter how you format them, you will probably end up with another cell M44 situation.
If possible, convert any cell that contains just the Time into a Date & Time by adding an appropriate Date.

[Cheeky Charlie]

Really glad it worked for you, and I second xenixman's final suggestion.