trying to set a floor and a ceiling for value calculation but failing

[justinhampton81]

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?

[pb71]

Could you send an example of your current workbook?

[justinhampton81]

Could you send an example of your current workbook?

here you go. thanks.

[masteff]

You need a nested MIN and MAX

=MAX( MIN( salary * (1+recommended%) , 110000 ) , 80000 )

In words, the larger of either a) the lesser of either i) the new salary or ii) the top limit or b) the bottom limit.

In terms of your sample spreadsheet, it would be: =MAX( MIN( B5*(1+B20), B16 ), B14 )

[justinhampton81]

You need a nested MIN and MAX

=MAX( MIN( salary * (1+recommended%) , 110000 ) , 80000 )

In words, the larger of either a) the lesser of either i) the new salary or ii) the top limit or b) the bottom limit.

In terms of your sample spreadsheet, it would be: =MAX( MIN( B5*(1+B20), B16 ), B14 )

You're the man! thanks.