Help on function VLOOKUP and TIME

**loobylooe** · 02-05-2012, 01:56 PM

Working on time and motion study in excel 2003. Need a function to recognise data as a letter e.g. A = Admin, E = Email etc then multiply by time (as in 5 min to each task). Unsure if VLOOKUP and TIME correct functions to use and how to set up.

**Fotis1991** · 02-05-2012, 02:13 PM

Hi and welcome to the forum.

For me, is not clear what are you asking for..

...Need a function to recognise data as a letter e.g. A = Admin, E = Email etc then multiply by time (as in 5 min to each task).

A=ADMIN>>>=IF(a1="Admin";"A")????????

Multiply which??

**loobylooe** · 02-05-2012, 02:30 PM

Thank you, I attach my worksheet which might explain it more easily.

Each task listed A, E, TO (Admin, Email etc) takes 5 mins to do.

I need to count how many A's there are say, 8 A's then multiply by 5 (as in 5 mins each) and so on. I think I would need VLOOKUP for this and also the TIME function to include hours and mins as some tasks might add up to an hour. I am not sure how to set the whole thing up.

Time in Motion Study2.xls

**protonLeah** · 02-05-2012, 02:41 PM

You should have posted a workbook with some sample daily logs. The w.b. that you did post only gives a list of abbreviations. It does not specify how much time is allotted for the particular tasks listed.

**Fotis1991** · 02-05-2012, 02:46 PM

I am not sure, if i get it...

To count how many A's there are..you need to use Countif function.

Something like this..>>>>=COUNTIF($B$3:$M$12,B27)

Then multiply by 5..

But i am quite sure, that you are looking something else........

**vlady** · 02-05-2012, 07:24 PM

@fotis I agree on that..

maybe like this one.

Copy of Time in Motion Study2.xls

**loobylooe** · 02-06-2012, 07:33 AM

Many thanks for your suggestions. You have been very helpful. I will have a play with it and see how I get on and make contact again if I need further help.

**loobylooe** · 02-12-2012, 02:00 PM

Thank you for your help on the attached but I need it to be changed slightly and cannot work out how to do it. I can make sense of how you have set it up and I would have def had difficulty doing this myself but the numbers shown in mins at the top of the row are misleading. This is what I was given to work with.

Each task is 5 mins duration. I need to work out, based on your formula which looks the ideal one for this task, to count how many A’s, B’s E’s etc there are in the worksheet. I have tried amending the formula but for some reason cant see how it relates to B, TO and so on. The naming of my cells (as in define) may have been incorrect at the outset but I am not sure.

By ignoring the mins stated in red at the top and looking at all tasks being 5 mins each only per task, please advise how we could convert this to correspond with each separate task.

This must be the simplest task one would ever have to do but of course it wont work correctly without the correct information. Please advise further.

Many thanks again you have saved me a lot of hard work and angst!
Copy of Copy of Time in Motion Study2.xls

**dangelor** · 02-13-2012, 01:39 PM

Try this SUMPRODUCT formula, after changing the minutes headers to numbers only, e.g. 5 min to 5, 10 min to 10, etc.

=SUMPRODUCT(--($C$3:$N$12=C21)*($C$2:$N$2))