IFTHEN formula? Formula needed to suggest existing salary range

Hello,

Does anyone know if this is possible?

I have the below company salary ranges that overlap:

A=25,000 to 32,000
B=30,000 to 37,000
C=36,000 to 44,000
D=40,000 to 50,000


I have market data that suggests what the lowest and highest pay should be for specific jobs, as well as what the median pay should be. For example, a Marketing Rep is suggested to be paid between 33,000 to 40,000 with a median pay of 35,000. Does anyone know if there is a way (perhaps formula) in Excel that would look at the suggested market data and fit it into one of our company's ranges? maybe an IFTHEN formula??

Any help or suggestions would be appreciated. Thanks!

Doesn't the answer depend on your definition of "fit"? On the one hand, since the median in your example for a Marketing Rep is $35k, that would clearly suggest that it's most similar to Salary Range B at your company. However, given the range for the Marketing Rep, it could be either B or C. So, in my opinion, you need to define the business rule(s) here before trying to devise a formula to implement them.

If we were to look at just our salary range medians, do you know a formula that could look at the market data median and determine what salary range media is closest?

For example, if our salary range medians are

A=28,500
B=34,000
C=40,000
D=45,000

and the market data median is 37,213, then the formula would place it in range C because it is the closest to C's median. Is that possible?

Sure, you just want the MIN of the difference between the market data min and your salary range medians. You could check out the statistical functions (I'm no expert there) or just use a helper column with the absolute value of the difference and then look up the range. For example, put your data in columns B and C (B1 through B4 in your example for the letter and C1 through C4 for the $). Use column A as your helper with

Please Login or Register  to view this content.

copied down (obviously referencing 37213 as a cell). Then you want to use a lookup:

Please Login or Register  to view this content.