Help - Not sure where I should start

[bearli89]

I'm trying to build a worksheet that will be able to calculate amounts based
on several rate levels.

Desired end result: Worker types in time (3:53pm) in cell (I'll call it F2
(Monday)). In cell G2, which will be hidden, there will be a formula that
brings back a rate multiplier. This rate multiplier will be multiplied by an
hourly rate that is summed in the last column. These formulas will be
applicable to each day of the week.

Work completed thus far: Tab 1 is my primary sheet where the work will be
completed. Column C is my Rate Code (R1 = $5, R2 = $4, etc.). Column D has
a Lookup function (which is working like I intended). Tab 2 contains my Rate
table from which the values are being pulled. Columns E, G, I, K & M will
have the data entry point. Columns F, H, J, L & N will have the formulas
that bring back the correct rate multiplier. Column O will total the amounts
calculated each day based on the Rate Code and the time that was entered.
Now for where I'm stuck.

I think I need an IF statement that will look up my rate multipliers;
however I'm not sure how to do for a range of numbers. For example, the
range of timescale one (Time1) is 3:31 - 3:45. The multiplier for any times
entered within this range should be 0. Time2 is 3:46 - 4:00, with a
multiplier of 0.25). So in my example above, if someone enters in 3:53 and
their Rate Code is R1, then the result should bring back a multiplier of
0.25. I would then tack on additional work for it to multiply that amount
times the $5.

Any help is greatly appreciated!!

 

[T. Valko]

How many different timescales do you have? If you have more than a "few"
then you should build a lookup table.

Need more info.

 

[bearli89]

Thanks for your response. As I've done more research into my problem, I have
discovered that I have too many arguments for the IF function. I have 10
time elements that need to be considered for each entry.

Can a look up function be built to look for a time that is within an
appropriate range of times and bring back the corresponding multiplier? I
had set up a timescale table w/4 columns: TimeCode, Start, End, and
Multiplier; but couldn't make anything work beyond that.

Thanks for any help.

 

[Pete_UK]

All you need is a simple table which lists the start time of each
period in one column and the multiplier in the next column - something
like this in X1:Y9:

00:00 0.1
04:00 0.25
08:00 0.3
12:00 0.1
15:00 0.2
16:00 0.25
18:00 0.3
20:00 0.4
23:00 0.2

and so on. Then to access this you will need:

=VLOOKUP(A1,X$1:Y$9,2)

assuming your start time is in A1 - this will return the appropriate
multiplier. You can add more times - just make sure they are in the
correct sequence, and adjust the table range in the VLOOKUP formula as
appropriate.

Hope this helps.

Pete

 

[bearli89]

Pete_UK,

Thanks for this information. I'd like to just clarify that I understand.
Given your example, I won't need to list an end time. If I were to enter
04:16 in the cell, then it would bring back the 0.25 multiplier because the
amount is greater than 04:00, but less than 08:00. Is that correct?

Once again, I appreciate the help.

bearli89

 

[Pete_UK]

That is correct - your end time is actually the start time in the next
row, and that's why it has to be in the correct sequence.

Pete

 

[bearli89]

Pete,

It worked beautifully. Many thanks!!!

bearli89

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete