table calculating complex capacity with variables

[eli_88]

Hello,
I am looking for a way to create a table allowing me to calculate my business capacity depending on several variables.
Namely I want to take into consideration all of the following:
number of hours per week of the team
capacity for reviewing text in English
capacity for reviewing text in German
capacity for reviewing text in French

The problem is the some people of the team can review all the 3 languages and some only 2.
For example with a team of 3 people working 40 h I have a total capacity of 120h.
Employee A can review English, German and French, but if this person dedicates 20h to English for exemple, their capacity will be the remaining hours on the 2 other languages. Employee B only works with English and French. What I need to do is to calculate how much words I can review in a week for the different languages considering that each person can dedicate not more that 40h in total at work. Or also the other way around, i.e. checking against the employees capacity that I can meet the needs for my week in terms of words reviewed per week.

Is there a way to create this?
Thanks a lot for your help
Eli

[Jacc]

If you post a sample workbook describing the problem and the expected output it will be much easier to help you.

[eli_88]

Hello,
so basically what I need is more or less this:

Untitled.png

And I need to interconnect the three tasks so that if I manually modify the value assigned to one the values the other 2 change automatically without having to calculate them manually.
The problem is that I might need to change task 1 or 3 (the line outside the table) so I need to put a formula with coefficient in the table for the three tasks. I need to allot the same number as in number of hours to the three activities and :
task 1 is total n. of h - h spent for tasks 2 and 3
task 2 is 20% of task 1 + 15% of task 3
task 3 is total n. of h - h spent for tasks 1 and 2

Is there a way to calculate capacity that are interconnected like this?

I hope my explanation is more or less clear
Thanks
Eli

[shg]

What I need to do is to calculate how much words I can review in a week for the different languages considering that each person can dedicate not more that 40h in total at work.

Seems to me like a linear programming problem, but I don't understand the inputs and outputs.

You start each week with a pile of docs in various languages to review, and want to know how to get the most done and what assignments to make? And if that's true, please provide an example; I can't make sense of your pictures.

[MrShorty]

Is there a way to calculate capacity that are interconnected like this?

I am generally optimistic that such calculations are possible, with enough time, effort, and ingenuity.

It is not clear to me exactly what you want. In your sample data, Task2's times do not equal the values you suggest it does. It appears to be simply 0.2*task1 and independent of task3.

It appears to me that, if you are given any two of the task times and the total hours, you can calculate the third time. This part is mostly algebra, as the programming should be simple.

Reading between the lines, I wonder if your question is really the same as this one: http://www.excelforum.com/excel-form...ml#post4191342 If this is the kind of question you are asking, then my answer is the same -- as long as you keep "inputs" and "outputs" in separate locations in the spreadsheet, it should be a simple if function [=IF(test for unkown toggle,formula to calculate unkown, copy value from input)].

The main complication that people want with this sort of thing is to insist that the input and output tables be the same table, in which case, one must use VBA to do the processing.

Does that help, or am I completely misunderstanding?

[shg]

Something like this (using Solver Simplex LP)?

Row\Col	A	B	C	D	E	F
1		English	French	German
2	Alan	40	40	40
3	Barb	40	40	0
4	Cain	0	40	40
5
6	Input	50	60	60	170	E6: =SUM(B6:D6)
7
8	Alan	40	0	0	40	E8 and down: =SUM(B8:D8)
9	Barb	10	30	0	40
10	Cain	0	30	10	40
11		50	60	10	120	B11 and across: =SUM(B8:B10)