Creating a survey results calculator in Excel - a bit complex

TheDave

New Member
Joined
Aug 3, 2011
Messages
11
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:
STATEEDUCATIONSALARY
TXBachelor's$30k - $35k/year
VTSome college$45k - $50k/year
ALMaster's$65k - $70k/year
ALNo collegeLess than $20k/year
TXAssociate's$45k - $50k/year

<tbody>
</tbody>

What I'm envisioning the calculator to look like:
STATEEDUCATIONSALARY
[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/year17500
$20k - $25k/year22500
$25k - $30k/year27500
$30k - $35k/year32500
$35k - $40k/year37500

<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!
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

estevaoba

Board Regular
Joined
Dec 26, 2016
Messages
96
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:

Code:
[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:

Code:
[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):

Code:
[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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top