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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]STATE[/TD]
[TD]EDUCATION[/TD]
[TD]SALARY[/TD]
[/TR]
[TR]
[TD]TX[/TD]
[TD]Bachelor's[/TD]
[TD]$30k - $35k/year[/TD]
[/TR]
[TR]
[TD]VT[/TD]
[TD]Some college[/TD]
[TD]$45k - $50k/year[/TD]
[/TR]
[TR]
[TD]AL[/TD]
[TD]Master's[/TD]
[TD]$65k - $70k/year[/TD]
[/TR]
[TR]
[TD]AL[/TD]
[TD]No college[/TD]
[TD]Less than $20k/year[/TD]
[/TR]
[TR]
[TD]TX[/TD]
[TD]Associate's[/TD]
[TD]$45k - $50k/year[/TD]
[/TR]
</tbody>[/TABLE]

What I'm envisioning the calculator to look like:
[TABLE="width: 500"]
<tbody>[TR]
[TD]STATE[/TD]
[TD]EDUCATION[/TD]
[TD]SALARY[/TD]
[/TR]
[TR]
[TD][dropdown menu here][/TD]
[TD][dropdown menu here][/TD]
[TD][formula here][/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Less than $20k/year[/TD]
[TD]17500
[/TD]
[/TR]
[TR]
[TD]$20k - $25k/year[/TD]
[TD]22500[/TD]
[/TR]
[TR]
[TD]$25k - $30k/year[/TD]
[TD]27500[/TD]
[/TR]
[TR]
[TD]$30k - $35k/year[/TD]
[TD]32500[/TD]
[/TR]
[TR]
[TD]$35k - $40k/year[/TD]
[TD]37500[/TD]
[/TR]
</tbody>[/TABLE]
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.