Wages to Performance

AngryPossum

New Member
Joined
Feb 24, 2011
Messages
2
Hey guys!

Come across a slight problem, the way that pay is done, is depending on how many bookings you make per hour, the more you get paid per hour...

I have a function that works it out for me;

=IF(E7>0,(LOOKUP(G7,{0,0.1,1.4,1.41,1.5,1.8,2},{15,15,15,16.5,18.5,20.5,23})),"")

but things change... And instead of changing the formula each time, is there a way to make the {15,15,15,16.5,18.5,20.5,23} bit select fields instead?

i.e

A1 B1 C1 D1 E1
Hours Bookings BPH Rate Wage
12 22 1.83 20.50 246
12 17 1.42 16.50 198

and the fields that can change would be

A1 B1
BPH Wage
<1.4 15
>1.4 16.5
>1.5 18.5
>1.8 20.5
>2 23

So able to change either A1 or B1 and it will affect D1 in the other table, but without using macros, if it can be done in a formula, that would be perfect

If you get what I mean...

Cheers in advance!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Hi, welcome to the board.

You could do this using VLOOKUP, which would mean you would have to maintain the data in a table which the VLOOKUP would refer to.

Would that help ?
 

AngryPossum

New Member
Joined
Feb 24, 2011
Messages
2
That would be perfect! Thanks!

I tried to do it with the one I had, but wouldn't work! Would only accept numbers rather than references...
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
First of all, set up your data table like this

A1......B1
BPH....Wage
0.......15
1.4....16.5
1.5....18.5

and so on.
I'm assuming the first 0 is in cell A2, and this table takes up the range A2:B11.

Code:
=if(e7>0,vlookup(g7,$A$2:$B$11,2,true),"")
 

AngryPossum

New Member
Joined
Feb 24, 2011
Messages
2
You sir are an absolute bledy LEGEND! The amount of time I have spent on that, and boom, to the rescue! I cant thank you enough!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,547
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top