0

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...

https://drive.google.com/file/d/0B8Z-j7zw6RADb1dLNkppWWx1Q1E/edit?usp=sharing

2
  • 1
    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.
    – teylyn
    Aug 12 '14 at 5:08
  • I've uploaded the file for added clarity.
    – Bryce
    Aug 12 '14 at 7:10
1

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))))
1
  • This worked flawlessly
    – Bryce
    Aug 12 '14 at 19:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.