Forum Discussion
Solved
Formula help. Different variables
Please could somebody help me, I am trying to automate points for an incentive.
So, the more points based gained based on certain variables.
I have attached an image which would be the criteria. I just can't figure it out at all or if this is even possible.
Thank you.
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))
I have attached an image which would be the criteria. I just can't figure it out at all or if this is even possible.
Unfortunately, you haven't explained those criteria. They're (presumably) clear to you, but all they are to an outsider is a bunch of numbers, numbers in different boxes of associated hours. And one of the columns has a cryptic heading--SPH/EPH--which undoubtedly is clear to you, but could yield all kinds of interpretations to that outsider.
An image may be worth 1,000 words, but sometimes 100 or 200 words can be worth more than an image.
I gather you're trying to figure out a way to have the points calculated--although that may be an incorrect reading. Either way, if you could spend a few words explaining in English what it is you want to deliver, and how those various boxes, and numbers in them, relate, I'm quite sure that Excel can handle it. But it--whatever IT is--does need to be defined, explained. Leave the Excel formulas to us; how would you explain the points system that you want automated to a bright person who is unfamiliar with your workplace, explaining that in plain ol' English?
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))
