Help with spreadsheet

[Chris]

I have my spreadsheet set up like this:
A B
C D
1 # of Core tasks # of Employees Average # of tasks/
employee Optional Trainings
(drop down with (drop down with
10-20 1
20-30 2
30-40) 3


What I can't figure how to do is create a fromula to give me an
average task per employee becuase I am using a range of 10-20 instead
of just 10 or 20. I also want the optional training number to be
dependant on the average number of tasks. So for example, If the
average task per employee is 15 or whatver they would be able to
attend 2 optional trainings, if 10, 3 optional trainings etc.

Any expertise that can help me out there with this?

 

[Bernard Liengme]

Starting in A1 I have
#empl task average
2 10-20 7.50
3 20-30 8.33
4 30-40 8.75

The formula in C2 is
=(B2="10-20")*15/A2+(B2="20-30")*25/A2+(B2="30-40")*35/A2
I assume if tasks is 10-20 then we can use the mid value of 15 for task
completed
But maybe I did not understand the question
best wishes