Is there a quicker way? Calculations Based on Drop Downs.

navxls

New Member
Joined
Aug 18, 2016
Messages
3
Hi I am currently working on creating a model and I am running into some issues.

My current method is very time consuming, and when complete will use too many columns and if statements.

I want to build a model that calculates the cost of paying raises to certain employees based on a drop down.

[TABLE="width: 291"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 291"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]SalaryClass[/TD]
[TD]Hours[/TD]
[TD]Hourly Wage[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]10[/TD]
[TD]7.5[/TD]
[TD]NYC[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]30[/TD]
[TD]7.75[/TD]
[TD]PIT[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]40[/TD]
[TD]8[/TD]
[TD]PHL[/TD]
[/TR]
[TR]
[TD]SL[/TD]
[TD]25[/TD]
[TD]12[/TD]
[TD]MIA[/TD]
[/TR]
[TR]
[TD]SL[/TD]
[TD]35[/TD]
[TD]15[/TD]
[TD]PIT[/TD]
[/TR]
[TR]
[TD]SL[/TD]
[TD]35[/TD]
[TD]18[/TD]
[TD]NYC[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]40[/TD]
[TD]30[/TD]
[TD]MIA[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]PHL[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]NYC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is how most of my data looks.

I want to create multiple drop downs to see the cost of increasing certian wages.

My issues here are that every time I want to create a trickle down effect I have to recalculate with a new column. I have about 4 trickle downs with 3 options each which leads to about 81 different columns I would have to create just for one year.

To give you an example say the New Minimum Wage is 8.50

The Model says the cost of increasing minimum wage to 8.50 is X. The maximum wage increase was 1 dollar ? Do you want to increase everyone who didn't get the increase by 1 dollar --> Yes, No, Increase by X% instead.

----> List New Cost.

Do you want to increase the wage in SL wage class. Yes by the wage increase, no, Increase by X% instead.

---> List New Cost


Currently I am using If statements to calculate things in a new column. Then using vlookup to return values based on the items selected in the drop down list. I feel there might be a more efficient way of doing this using fewer columns.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,226,730
Messages
6,192,705
Members
453,748
Latest member
akhtarf3

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
Back
Top