Creating a survey results calculator in Excel - a bit complex

[TheDave]

Alright, Excel gurus, I could use your help. I've got a bunch of survey results, and I'm expecting lots more. I'm creating a calculator in Excel to run some numbers for me faster. The main problem I'm running into is that the result I'm ultimately looking for is an average salary amount. When the survey respondents are entering that information, they're picking a range. So for this calculator to work, I need the formulas to be "converting" some respondents' data into the middle numbers of their salary ranges.

It'll probably make more sense if you just take a look at a smaller example of what I'm facing.

Data being referenced:

STATE	EDUCATION	SALARY
TX	Bachelor's	$30k - $35k/year
VT	Some college	$45k - $50k/year
AL	Master's	$65k - $70k/year
AL	No college	Less than $20k/year
TX	Associate's	$45k - $50k/year

<tbody>
</tbody>

What I'm envisioning the calculator to look like:

STATE	EDUCATION	SALARY
[dropdown menu here]	[dropdown menu here]	[formula here]

<tbody>
</tbody>

So what I'm thinking of doing is having a separate table as a reference to be able to "convert" the SALARY column and give me numbers I can actually do math with.

Something like this:

Less than $20k/year	17500
$20k - $25k/year	22500
$25k - $30k/year	27500
$30k - $35k/year	32500
$35k - $40k/year	37500

<tbody>
</tbody>

etc.

So what I'm looking for is a formula for my calculator that searches through the data being referenced (let's start with just one column - say the "STATE" column - I can combine formulas and columns later), finds instances where there's a match in the data with the appropriate dropdown that was selected from the calculator, then looks up the salaries - just for those hits, converts them using the referencing table, then gets their total average.

In other words, with the above data, if I selected TX in the STATE column of the calculator, I would expect the result to be $40,000 ((32500 + 47500)/2). If I selected VT, I should get $47,500. If I selected AL, I should get $42,500.

I'm open to all suggestions, thanks!

 

[estevaoba]

I use these formulae to extract number values from a cell with a salary range, so I can get an average.

Assuming salary range in C2 and down, try this in E2, for example, for the left hand part of salary range:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(LEFT(C2,SEARCH("k  ",C2)),"$",""),"k","")),0)[/TD]
[/TR]
</tbody>[/TABLE]

And this in F2, for the right hand part of salary range:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=VALUE(SUBSTITUTE(SUBSTITUTE(MID(C2,SEARCH("k/",C2,2)-3,3),"k",""),"$",""))[/TD]
[/TR]
</tbody>[/TABLE]

Then get the average with this array formula (paste it, then press and hold ctrl+shift keys while hitting enter key):

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=IFERROR(AVERAGE(INDEX(E2:E6,MATCH(H2,IF(B2:B6=I2,$A$2:$A$6),0)),INDEX(F2:F6,MATCH(H2,IF(B2:B6=I2,$A$2:$A$6),0))),"")[/TD]
[/TR]
</tbody>[/TABLE]

Assuming the following:

H2=dropdown of states
I2= dropdown of education

Copy formulas down.