Wages to Performance

[AngryPossum]

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!

 

[Gerald Higgins]

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]

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]

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.

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

 

[AngryPossum]

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!