Forum Discussion
Formula help. Different variables
LiamL2002 It does help.
this illustrates one way to solve this. Put all those criteria into a two-dimensional table, with OT hours across the top (the horizontal dimension), SPH/EPH values down the vertical dimension. And use INDEX and MATCH to select the appropriate number of points.
There would be other ways to arrange the data entry. You don't specify how you're going to use this, so this basically just illustrates the concept. All the work (once the data table is arrayed properly) is done by a single formula, in cell C14. A copy of the spreadsheet is attached.
=INDEX($C$4:$G$7,MATCH($C$13,$A$4:$A$7,0),MATCH($C$12,$C$2:$G$2,0))
Apologies,
So if someone has worked 3 hours of overtime up to 5 and then also hit the "22/15" SPH/EPH basically messages sent per hour. They would get the 2 points 23/16 the 3 points and so on. I started to struggle when I got to the 5-7 hours overtime criteria and hitting the 22/15 SPH/EPH and the points it then would assign.
It's a team incentive running to encourage overtime, but the team would gain more points if more overtime has been done and reached higher numbers towards their targets.
I hope this helps a little.
LiamL2002 It does help.
this illustrates one way to solve this. Put all those criteria into a two-dimensional table, with OT hours across the top (the horizontal dimension), SPH/EPH values down the vertical dimension. And use INDEX and MATCH to select the appropriate number of points.
There would be other ways to arrange the data entry. You don't specify how you're going to use this, so this basically just illustrates the concept. All the work (once the data table is arrayed properly) is done by a single formula, in cell C14. A copy of the spreadsheet is attached.
=INDEX($C$4:$G$7,MATCH($C$13,$A$4:$A$7,0),MATCH($C$12,$C$2:$G$2,0))