Find Min, Mid or Max Value

[msquared99]

OK, I have this spreadsheet, Sheet1 contains salaries. In column A is the base rate, column C is the position name. In Sheet 2, column B is the position name, column D is the min pay range, column F is the mid pay range, and column H is the max pay range.

If Sheet 1 column A has $150,000, then I want to know where that is on the pay scale in Sheet 2. Say Sheet 2 min pay is $85,108 mid pay is $110,640 and max pay is $136,173. I want to return "Mid" on Sheet 1 column C compared to where the $150,000 salary range is.

Attached is an example.

Thanks!

 

[Luke M]

I don't understand your file or example. In the file, it loosk like the key column on Sheet1 is the Position #, as Position Name is blank in several places. HOwever, this does not appear on SHeet2. Further, on Sheet1, you have Posiiton Name of "Manager", but values appear to be for a level 14 - Director. How is this?

In your example here, if base is 150000, and the 3 values are 85108/110640/136173, why would the answer be "Mid"? I would think Max...but then, how can our number be bigger than Max?

Also, what do do if value is half way between the Min and Mid point?

I think you need to re-look at your spreadsheet, and think about what question you want.

 

[msquared99]

Oooops, a typo, If the salary is $150,000 then the answer would be Max or anything equal to $136,173 or above would be Max.

If the salary were $85,108 or lower the answer would be Min.

If the salary were between $85,109 to $136,173 the answer would be Mid.

 

[NARAYANK991]

Hi ,

3 salary points seem to be giving rise to 4 slabs , as in :

0 - 85,107

85,108 - 110,639

110,640 - 136,172

136,173 - 999,999,999

Narayan

 

[msquared99]

I have it figured out.

Thanks for the thoughts. It helped me figure it out.