Table Help

[KeyWest JetSki]

I need to build a table into my spreadsheet

12.50 15.00 18.75 21.25
25.00 35.00 40.00 55.00
40.00 55.00 60.00 75.00
50.00 70.00 90.00 100.00
65.00 85.00 100.00 120.00


I typed the above into my spreadsheet but I cannot create a table.

How do I create this table or do I need a table at all?

I have a spreadsheet that I would like to calculate pay for my employees.
The get a special rate depending on how many times they do function. Also I
have different level for employees skill levels. Example

Level I
Jobs Pay
1 $15
2 $35
3 $55
4 $70
5 $85

Level IV
Jobs Pay
1 $21.25
2 $55
3 $75
4 $100
5 $120

I keep track of the numbers per shift

Exampe

Employee Start End Jobs Pay
John 0900 1500 5
Joe 0900 1200 2


John is a level 4
Joe is leve 2

I need the formula to calculate the pay column

Any ideas?

Thanks

Tom

 

[Tom Ogilvy]

As constructed, the data you show is pretty much meaningless. There is no
obvious way to pick out a specific value.

 

[Guest]

Hi,
I assume your data is the rates per level per number of jobs. Put
this data in columns B to E ,rows 2 to 6 with row 1 for headers. In column A,
starting row 2, type the number of jobs (1 to 5). Define this data (a2:e6)
on a separate worksheet.

on Sheet2:

A B C D E
Job L1 L2 L3 L4
1 12.50 15.00 18.75 21.25
2 25.00 35.00 40.00 55.00
3 40.00 55.00 60.00 75.00
4 50.00 70.00 90.00 100.00
5 65.00 85.00 100.00 120.00


To get the Pay, use VLOOKUP as follows:

=Vlookup(NoJobs,PayTable,Level+1,False) where

NoJobs is the number of jobs
Level is the Paylevel ( value 1 to 4)
PayTable is the data table (a2:e6 on sheet2)

Using your example and assuming data is in columns A to E on Sheet1 then in
column E (pay) insert

- for John :-

=VLOOKUP(d2,a2:Sheet2!e6,5, False)

note the 5 = John's pay level (4) + 1. Substitute 5 with a variable?

- for Joe

=VLOOKUP(d2,a2:Sheet2!e6,3, False)

Sheet1:

A B C D E
Employee Start End Jobs Pay
John 0900 1500 5 =VLOOKUP(.....)
Joe 0900 1200 2



HTH

 

[Guest]

Sorry, couple of typos!


=VLOOKUP(d2,Sheet2!a2:e6,5, False) John
=VLOOKUP(d3,Sheet2!a2:e6,3, False) Joe