Calculation based on input entered

Question

Hi Everyone

I’m trying to create a function that helps me calculating based on input I entered.

For example, I’m trying to calculate employees overtime and I have a table that include the following:

Headquarter employees:

Grade - salary per day

1 - $200

2 - $150

3 - $100

4 - $50

Non-headquarter employees:

Grade - salary per day

1 - $180

2 - $130

3 - $80

4 - $30

I want to create three cells. First one is to choose headquarter or non-headquarter. Second one is how many days which I can input manually depending on how many days the employee worked. Third one is where I input the grade number and then I want it to calculate it by: number of days (I inserted manually) X the salary per day (which is based on the input I inserted in third cell). For example, employee is grade 1 headquarter worked for 5 days. First cell would be to choose either headquarter or non. I choose manually headquarter. Second cell I put 5 which is days. Third cell I put 1 which is grade so it should be 200 X 5 = 1000

Hope it is clear and thank you so much in advance.

Answer 1

Hi Thamer

Please, try the following

1. Create a table similar to the one in the picture below
2. Try the formula

=INDEX($H$1:$J$5,MATCH($C2,$H$1:$H$5,0),MATCH($A2,$H$1:$J$1,0))*$B2

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

Try this formula.

=IF(A2="Headquarter",INDEX({200,150,100,50},C2),INDEX({180,130,80,30},C2))*B2

Answer 3

Thank you very much Jeovany, I applied it and worked great just like what I was looking for.