I am creating a spreadsheet that is pulling employee data from another sheet. Currently, our method for making salary recommendations is somewhat arbitrary and not consistent in application. I am attempting to create a spreadsheet that pulls from our employee data and recommends increases based on a number of criteria, and eventually recommends a total increase recommendation.

However, the recommendations need to fall within the range of our positions. I need to create a statement that takes the base salary and multiplies it by the percent increase that is determined from other criteria - BUT - if the overall recommendation falls short of the range, I need it to recommend the minimum - AND - if it is above the maximum, I need it to cut the increase off at the maximum.

For example:

John Smith makes $100,000 but is being recommended for a 20% increase, to $120,000, however the max allowed is $110,000 - therefore the recommendation needs to be $110.

Bobby Peterson makes $60,000 but is being recommended for a 20% increase, to $72,000, however the minimum allowed is $80,000 - therefore the recommendation needs to be $80.

What formula would accomplish ensuring the minimum and maximum are considered? This is being used, but I have to use 2 cells and don't like that.

=IF(ROUND((1+basesalary)*%increase,-2)<minimumsalary,minimum salary,(ROUND((1+basesalary)*% increase,-2)))

Does this make sense?