Nearest Rate

[HuckFinn]

I have a rate (i.e., a salary rate) in cell P8 in sheet A, and I want to find the nearest midpoint (another salary rate - column F shown below) from a salary structure in sheet B. Ultimately, I want the formula to return a grade level from the salary structure.

The challenge is that I have two separate salary structures, and depending on a specific criteria (i.e., location) I need the formula to look from the correct structure. I have been trying the INDEX & MATCH function, but am spinning my wheels. Location is in column J of sheet A and is listed as structure "A" or "B"



So for example, if job 1 has a market value of $47,650, the formula would return a value of 2 if the job was from structure 1 or some other value of it look at structure 2.

Any thoughts on how to accomplish this?

 

[Toadstool]

Pending

 

[HuckFinn]

In this case, they are side by side. Midpoints for Structure A are F4:F23 and midpoints in Structure B are listed in cells O4:O10.

 

[Dermot]

The two salary structures are easy enough
IF(Structure="A", use formula1, use formula2)
the question is how to find the right midpoint

It can be done very simply if you add a helper column, that gives Excel the threshold figure for each grade, ie the point at which you start using the next grade.
And your criteria is to choose the grade whose midpoint is nearest the salary
That means the threshold between grades is halfway between the midpoints
So for the table above, your helper column is
Grade Threshold
1 0
2 45950 (the average of 43500 and 48400)
3 51100
4 56850
5 9999999 (very big so it's always larger than salary)

Then your formula is = MATCH(Salary,Threshold Column,1) which will return the grade number

 

[Toadstool]

Hi HuckFinn,

So if your Sheet B structure is:

HuckFinn.xlsx
	D	E	F	G	H	I	J		L	M	N	O	P	Q	R
2		Structure A									Structure B
3	Grades	Minimum	Midpoint	Maximum	Range Spread	Grades				Grades	Minimum	Midpoint	Maximum	Range Spread	Grades
4	1	$ 36,300.00	$ 43,550.00	$ 50,800.00	40%	1				1	$ 46,464.00	$ 51,172.00	$ 55,880.00	20%	1
5	2	$ 40,300.00	$ 48,400.00	$ 56,500.00	40%	2				2	$ 51,584.00	$ 56,867.00	$ 62,150.00	20%	2
6	3	$ 44,800.00	$ 53,800.00	$ 62,800.00	40%	3				3	$ 57,344.00	$ 63,212.00	$ 69,080.00	20%	3
7	4	$ 49,900.00	$ 59,900.00	$ 69,900.00	40%	4				4	$ 63,872.00	$ 70,381.00	$ 76,890.00	20%	4
8	5	$ 55,500.00	$ 66,600.00	$ 77,700.00	40%	5				5	$ 71,040.00	$ 78,255.00	$ 85,470.00	20%	5
Sheet B

Cell Formulas
Range	Formula
O4:O8,F4:F8	F4	=AVERAGE(E4,G4)
Q4:Q8,H4:H8	H4	=(G4-E4)/E4

You could just use a CHOOSE, but note this doesn't check for values being below Minimum or higher than Maximum:

HuckFinn.xlsx
	J						P	Q
7	Structure						Salary	Grade
8	A						$ 47,650.00	2
9	B						$ 50,000.00	1
10	A						$ 22,222.00	1
11	B						$ 99,999.00	5
12	A						$ 57,777.00	4
13	B						$ 57,777.00	2
Sheet A

Cell Formulas
Range	Formula
Q8:Q13	Q8	=CHOOSE((J8="A")+1,INDEX('Sheet B'!$R$4:$R$8,MATCH(MIN(ABS('Sheet B'!$O$4:$O$8-P8)),ABS('Sheet B'!$O$4:$O$8-P8),0)),INDEX('Sheet B'!$I$4:$I$8,MATCH(MIN(ABS('Sheet B'!$F$4:$F$8-P8)),ABS('Sheet B'!$F$4:$F$8-P8),0)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Dermot]

That's a clever formula, Toadstool, but if it can be done with a little helper column and a formula of = MATCH(Salary,Threshold Column,1), as I suggested above, isn't it better to keep things simple? I worry when formulae get that complex, because they become almost impossible for ordinary users to deal with ,if anything goes wrong.

 

[HuckFinn]

Toadstool - That formula worked nicely. I appreciate it. I will check the min / max value issue. Thanks again for the quick response! Thanks also to Dermot.

 

[Toadstool]

That's a clever formula, Toadstool, but if it can be done with a little helper column and a formula of = MATCH(Salary,Threshold Column,1), as I suggested above, isn't it better to keep things simple? I worry when formulae get that complex, because they become almost impossible for ordinary users to deal with ,if anything goes wrong.

I understand where you're coming from and I'll often suggest helper columns, but I was already working on the CSE entry when you posted and OPs often say they don't want helper columns so I thought I'd put it out there.

If you weren't keen on that then you're probably not going to like my approach if there's more than two Structures.

HuckFinn.xlsx
	D	E	F	G	H	I				M	N	O	P	Q	R				V	W	X	Y	Z	AA
2	Structure A									Structure B									Structure C
3	Grades	Minimum	Midpoint	Maximum	Range Spread	Grades				Grades	Minimum	Midpoint	Maximum	Range Spread	Grades				Grades	Minimum	Midpoint	Maximum	Range Spread	Grades
4	1	$ 36,300.00	$ 43,550.00	$ 50,800.00	40%	1				1	$ 46,464.00	$ 51,172.00	$ 55,880.00	20%	1				1	$ 23,500.00	$ 29,500.00	$ 35,500.00	51%	1
5	2	$ 40,300.00	$ 48,400.00	$ 56,500.00	40%	2				2	$ 51,584.00	$ 56,867.00	$ 62,150.00	20%	2				2	$ 32,000.00	$ 38,000.00	$ 44,000.00	38%	2
6	3	$ 44,800.00	$ 53,800.00	$ 62,800.00	40%	3				3	$ 57,344.00	$ 63,212.00	$ 69,080.00	20%	3				3	$ 40,500.00	$ 46,500.00	$ 52,500.00	30%	3
7	4	$ 49,900.00	$ 59,900.00	$ 69,900.00	40%	4				4	$ 63,872.00	$ 70,381.00	$ 76,890.00	20%	4				4	$ 49,000.00	$ 55,000.00	$ 61,000.00	24%	4
8	5	$ 55,500.00	$ 66,600.00	$ 77,700.00	40%	5				5	$ 71,040.00	$ 78,255.00	$ 85,470.00	20%	5				5	$ 57,500.00	$ 63,500.00	$ 69,500.00	21%	5
Sheet B

Cell Formulas
Range	Formula
X4:X8,O4:O8,F4:F8	F4	=AVERAGE(E4,G4)
Z4:Z8,Q4:Q8,H4:H8	H4	=(G4-E4)/E4

HuckFinn.xlsx
	J						P	Q
7	Structure						Salary	Grade
8	A						$ 47,650.00	2
9	B						$ 50,000.00	1
10	C						$ 22,222.00	1
11	A						$ 99,999.00	5
12	B						$ 57,777.00	2
13	C						$ 60,000.00	5
Sheet A

Cell Formulas
Range	Formula
Q8:Q13	Q8	=MATCH(MIN(ABS(OFFSET('Sheet B'!$A$2,2,MATCH("Structure "&J8,'Sheet B'!$2:$2,0)+1,5)-P8)),ABS(OFFSET('Sheet B'!$A$2,2,MATCH("Structure "&J8,'Sheet B'!$2:$2,0)+1,5)-P8),0)
Press CTRL+SHIFT+ENTER to enter array formulas.