Calculate Value based on three variables (1 Viewer)

[samirsaid123]

Hello,

I need help with a problem I have. The case is as follows:

I have two files. File A includes information about employees in respective departments, looking like this:

Month | Year | Total | Department 1 | Department 2 | etc.
12 | 2006 | 10 | 4 | 5 | ....
01 | 2007 | 12 | 6 | 7 | .....

Every month fills a new row in the file.


File B has the following 4 fields:
A1: 100 USD (cost)
A2: Department 1 (text, same as in File A the title of the department)
A3: 01 (month)
A4: 2006 (year)

I would like to run the following operation:
Based on input in A3/A4 the command shall find the respective entry in File A and divide A1 by the total amount of people of that respective month and multiple it by the amount of people of the department specified in A2.

Example: I enter:
A1: 100
A2: Department 1
A3: 12
A4: 2006

The command shall now divide 100 by 14 multiplied by 4. How do I do that?

 

[jc1104]

The easiest way I have found to reference multiple columns in Excel is to treat it like an Access Primary Key (where the Primary Key is joined over several fields).

By this I mean:
Create a new column BEFORE the existing columns on BOTH files.
Take your defined lookup variables (File A: Month and Year, File B: A3 and A4), and join them together (=A3&A4) in this new column. This will give you, using your example, 122006.

Now you can use the VLOOKUP command to cross-reference File A from File B and select the data you require.

I have attached a sample Excel file that might help.
Unfortunately, I don't follow your maths:

Based on input in A3/A4 the command shall find the respective entry in File A and divide A1 by the total amount of people of that respective month and multiple it by the amount of people of the department specified in A2.

Example: I enter:
A1: 100
A2: Department 1
A3: 12
A4: 2006

The command shall now divide 100 by 14 multiplied by 4. How do I do that?

For Month=12 and Year=2006, the respective entry in File A for Total of amount of people of that month=10.
The number of people of the department specified in A2 (Department 1)=4

Therefore, surely you mean, 100 / 10 * 4 = 40 ???

Anyway, hope this helps in some way.

JC

 

[samirsaid123]

Thank you! I tried to figure out a way where I do not have to combine both cells, but in the end why making it more complicated if such a neat solution is available as well.

Best Wishes,
- Samir