Mer
asked on
How to retrieve an average (Payout %) based on attainment metric. (Part Two)
Hey everyone,
Asked this question recently and got an answer that worked but I have a new problem that requires a new solution.
Example File Attached.
Basically the Attainment percentage will yield a % Payout Factor, not every value is listed in the attainment/payout factor chart so the formula has to produce a % Payout Factor that's aligned with the chart giving a result that makes sense when the Attainment doesn't appear directly on the chart.
Example, if Goal Attainment Chart has a 100% attainment which results in 100% payout factor, and 105% attainment that gives a 141.7% payout, if the employee achieves 101% attainment their payout factor should be between 100-141.7% but not just an average, it has to be closer to the 100%'s payout factor, maybe around 110%?
If anyone has a solution, it'd be greatly appreciated.
Thank you,
- Rick
Example.xlsx
Asked this question recently and got an answer that worked but I have a new problem that requires a new solution.
Example File Attached.
Basically the Attainment percentage will yield a % Payout Factor, not every value is listed in the attainment/payout factor chart so the formula has to produce a % Payout Factor that's aligned with the chart giving a result that makes sense when the Attainment doesn't appear directly on the chart.
Example, if Goal Attainment Chart has a 100% attainment which results in 100% payout factor, and 105% attainment that gives a 141.7% payout, if the employee achieves 101% attainment their payout factor should be between 100-141.7% but not just an average, it has to be closer to the 100%'s payout factor, maybe around 110%?
If anyone has a solution, it'd be greatly appreciated.
Thank you,
- Rick
Example.xlsx
ASKER
Shums, this formula does find the average between the two numbers but it doesn't give me different values for Example: 101%,102%,103%,104% all come out to 120.85%. As specified above a 101% payout should probably be around 110% where a 102% will be around 120%, 103% - around 130%, etc. considering the 105% payout is 141.7%.
Hope I am being accurate enough in my examples/problem description.
Thanks again though for your effort sir.
Hope I am being accurate enough in my examples/problem description.
Thanks again though for your effort sir.
Rick,
What will be Pay Out Factor for 117%, where in your chart it says 225%?
What will be Pay Out Factor for 117%, where in your chart it says 225%?
ASKER
Shums, I should of wrote "Capped" after 115% Attainment and 225% Payout Factor. Any Attainment greater than 115% will result in a 225% Payout Factor.
Rick,
What will be Pay Out Factor for above 110% to 114% Attainment?
What will be Pay Out Factor for above 110% to 114% Attainment?
ASKER
The Payout Factor should come out at a rate based upon the payout factor above the attainment value you're looking for and below it, I'm not sure the exact #'s but it should basically be reflected by the % of the Payout Factor. So 100% Attainment equals 100% Payout Factor and 105% equals 141.7% based on the chart provided.
FOR EXAMPLE: An attainment of 102.5% (just for example, each attainment is only whole numbers) should be the exact middle of Attainments 100% and 105% which is between payout factors of 100% and 141.7% ... (100% + 141.7%) / 2 = 120.85% therefore 102% should be below 120.85, and so should 101% while 103% and 104% should be somewhere above 120.85% Payout Factor.
Thanks again for trying to help!
FOR EXAMPLE: An attainment of 102.5% (just for example, each attainment is only whole numbers) should be the exact middle of Attainments 100% and 105% which is between payout factors of 100% and 141.7% ... (100% + 141.7%) / 2 = 120.85% therefore 102% should be below 120.85, and so should 101% while 103% and 104% should be somewhere above 120.85% Payout Factor.
Thanks again for trying to help!
Rick
please find attached.
=IF(A2>=1.15,OFFSET(INDEX(
JIMJAM.xlsx
please find attached.
=IF(A2>=1.15,OFFSET(INDEX(
JIMJAM.xlsx
ASKER
JimJam, thanks again for the attempt but this also has the same output for 101-104% as well as any other in between values. Perhaps the above comment I just made to Shums will help illustrate what I'm looking for?
Again, thank you both so much for the help!
Again, thank you both so much for the help!
sorry Rick, cannot help further. as the logic used do not come close to any algorithm.
i quote
you initially stated in your example that 101% should yield around 110% and now based on your statement qouted, it should be below 120.85 where in your chart column F and G the next below amount is 115 and not 110.
for a formula to work, there has to be a logical scenario with adequate information to derive output from.
another example of inconsistency,
i quote
FOR EXAMPLE: An attainment of 102.5% (just for example, each attainment is only whole numbers) should be the exact middle of Attainments 100% and 105% which is between payout factors of 100% and 141.7% ... (100% + 141.7%) / 2 = 120.85% therefore 102% should be below 120.85, and so should 101% while 103% and 104% should be somewhere above 120.85% Payout Factor.
you initially stated in your example that 101% should yield around 110% and now based on your statement qouted, it should be below 120.85 where in your chart column F and G the next below amount is 115 and not 110.
for a formula to work, there has to be a logical scenario with adequate information to derive output from.
another example of inconsistency,
while 103% and 104% should be somewhere above 120.85% Payout Factorwhere exactly the above 120.85 should come from? is it 141.70%
maybe i am getting old and cannot get my head around this. Shums you look like a young fella, perhaps it may ring a bell for you
LOL Professor,
I am on the same page as you are. as you stated we cannot create our own formula as per required logic, we need to follow or mix with default Excel Functions.
Sorry Rick, your quote
The only way we can make this possible is to have helper column for FROM and TO range, then average it, with single lookup column with multiple criteria, it sounds VERY difficult.
I am on the same page as you are. as you stated we cannot create our own formula as per required logic, we need to follow or mix with default Excel Functions.
Sorry Rick, your quote
above a 101% payout should probably be around 110% where a 102% will be around 120%, 103% - around 130%, etc.made my eyes rolled and head busted.
The only way we can make this possible is to have helper column for FROM and TO range, then average it, with single lookup column with multiple criteria, it sounds VERY difficult.
ASKER
JimJam and Shums, when I stated it should be around 110%, then said it should be under 120.85% (which is the AVERAGE of 100-105% Attainment (aka 100%-140.7% Payout Factor), those were both regarding the 101% Attainment's Payout Factor.
There is logic behind this for sure, I just don't know how to come up with the formula.
There is logic behind this for sure, I just don't know how to come up with the formula.
ASKER
That example which stated an attainment % of 102.5 (which again, is impossible, considering I'm using whole numbers only for attainment, I'm only using this number because it is the middle value between two of my attainment percentages (100% and 105%)... this should result in the Middle of their respective % Payout Factors (which are 100% and 140.7% which comes out to be 120.35%. Then I mentioned a 102% Attainment (Less than 102.5% (example attainment)) would have to achieve Less Than 120.85% Payout Factor... and the 101% would also have to achieve Less Than 120.35% and it would be around 110%.
I apologize, this problem is very confusing. Lol and again, I thank you both for helping.
I apologize, this problem is very confusing. Lol and again, I thank you both for helping.
ASKER
JimJams and Shums, I've just realized my typo which may have been causing this confusion 120.35*** not .85 I'm so sorry! lol (100 + 140.7) / 2 = 120.35. I'm not sure why I used an 8 instead of a 3.
Rick, i will give it a try whenever i get a chance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the attempt JimJam & Shums, really appreciate it. I will upload here when I grasp the concept and figure it out. Hopefully I explained it correctly.
you are welcome RIck. did you see the last solution i posted? is it working for you?
ASKER
No JimJam, unfortunately not. I need to work on my explanation skills lol.
No comment has been added to this question in more than 14 days, so it is now classified as abandoned.
If you feel this question should be closed differently, post an objection and a moderator will read all objections and then close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
If you feel this question should be closed differently, post an objection and a moderator will read all objections and then close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
Try below:
Open in new window
Please find attached...Rick_Lookup-Between-Two-Values_v3.xlsx