# Excel: Percentages and ranges

I am attempting to make a bonus spreadsheet for work and am having some issues with the following.

I am using this formula to determine 80, 90 and 100%

``````=IF(D6>J8,0,IF(AND(D6<=J8,D6>J7),80,IF(AND(D6<=J7,D6>J6),90,IF(D6<=J6,100,0))))
``````

In this instance `D6:D8` are the value's 3, 4 and 5 (80, 90 and 100% respectively) and `J7` is my dynamic value, in this case 4.5.

Currently with this formula a value of 4.5 would return a value of 80%, however I would like to find a way to have it return a value of 85%. I am having trouble with this as my %'s are not 'mathematically' correct and the lowest % returned would be 80 and the highest would be 100.

Hopefully this makes sense to someone, somewhere. If anything is unclear or more information is needed, please let me know and I will do my best to clarify.

Edit: Here is a copy of the file. Depending what I put in the "result" column, the "% of range" column would adjust based on the "range goal". Again, hopefully this makes sense, bit of a doozie to explain...

• What is in J6 and J8? Instead of posting a formula, describe the desired output and the business logic. Post a sample file. Use a file sharing service and post a link. Aug 12 '14 at 5:08
• I've uploaded the file for added clarity. Aug 12 '14 at 7:10

If i understand you correctly you want a function that converts values from the interval [J8,J7[ ([4,5[) to the interval ]90,80]?
If yes this should solve your question:

``````=IF(D6>J8,0,IF(AND(D6<=J8,D6>J7),80+10*((J8-D6)/(J8-J7)),IF(AND(D6<=J7,D6>J6),90,IF(D6<=J6,100,0))))
``````
• This worked flawlessly Aug 12 '14 at 19:51