Hi all,
i have question in regards to an assignment that I am currently doing, i am trying to figure the total time required to serve a customer based on their needs level, however, my current model only allows me to display one result after input the population value in cell a3, so, said if i were to one to know the total time required based on the table from a25 but i do not want to input the value in a3 10 times and copy the value into b25 as a model is required, is there a function in excel that allows me to formulate the total time required(b25) and generate it into the entire list?
i have question in regards to an assignment that I am currently doing, i am trying to figure the total time required to serve a customer based on their needs level, however, my current model only allows me to display one result after input the population value in cell a3, so, said if i were to one to know the total time required based on the table from a25 but i do not want to input the value in a3 10 times and copy the value into b25 as a model is required, is there a function in excel that allows me to formulate the total time required(b25) and generate it into the entire list?
help.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | ||||||||||||||
2 | population | |||||||||||||
3 | 50000 | #N/A | ||||||||||||
4 | Serverity | $0 | ||||||||||||
5 | main | 1 | 2 | 1 | 2 | 3 | ||||||||
6 | 43000 | 5000 | 2000 | 86% | 10% | 4% | 100% | |||||||
7 | 1 | 4.0 | 8.0 | 12.0 | 60% | |||||||||
8 | Break down of total population | 2 | 3.2 | 4.0 | 8.0 | 30% | ||||||||
9 | 3 | 1.6 | 3.2 | 6.0 | 10% | |||||||||
10 | 25800 | 3000 | 1200 | 30000 | ||||||||||
11 | 12900 | 1500 | 600 | 15000 | ||||||||||
12 | 4300 | 500 | 200 | 5000 | ||||||||||
13 | 43000 | 5000 | 2000 | 50000 | ||||||||||
14 | TIME REQUIRED PER PERSON | |||||||||||||
15 | ||||||||||||||
16 | 103200 | 24000 | 14400 | 141600 | ||||||||||
17 | 41280 | 6000 | 4800 | 52080 | ||||||||||
18 | 6880 | 1600 | 1200 | 9680 | ||||||||||
19 | 151360 | 31600 | 20400 | 203360 | ||||||||||
20 | ||||||||||||||
21 | ANSWER | |||||||||||||
22 | 203360 | |||||||||||||
23 | ||||||||||||||
24 | ||||||||||||||
25 | POPULATION | TIME REQUIRED | ||||||||||||
26 | 50000 | |||||||||||||
27 | 60000 | |||||||||||||
28 | 70000 | |||||||||||||
29 | 80000 | |||||||||||||
30 | 90000 | |||||||||||||
31 | 100000 | |||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B3 | B3 | =VLOOKUP($A$3,$N$6:$O$11,2,1) |
E4 | E4 | =130*$B$22 |
A6 | A6 | =$A3*$I$6 |
B6 | B6 | =$A3*$J$6 |
C6 | C6 | =$A3*$K$6 |
I7,J8 | I7 | =240/60 |
J7,K8 | J7 | =480/60 |
K7 | K7 | =720/60 |
I8,J9 | I8 | =192/60 |
I9 | I9 | =96/60 |
K9 | K9 | =360/60 |
B10 | B10 | =$A$6*$L$7 |
C10 | C10 | =$B$6*$L$7 |
D10 | D10 | =$C$6*$L$7 |
E10 | E10 | =SUM($B$10:$D$10) |
B11 | B11 | =$A$6*$L$8 |
C11 | C11 | =$B$6*$L$8 |
D11 | D11 | =$C$6*$L$8 |
E11 | E11 | =SUM($B$11:$D$11) |
B12 | B12 | =$A$6*$L$9 |
C12 | C12 | =$B$6*$L$9 |
D12 | D12 | =$C$6*$L$9 |
E12 | E12 | =SUM($B$12:$D$12) |
B13 | B13 | =SUM($B$10:$B$12) |
C13 | C13 | =SUM($C$10:$C$12) |
D13 | D13 | =SUM($D$10:$D$12) |
E13 | E13 | =SUM($E$10:$E$12) |
B16 | B16 | =B10*$I$7 |
C16 | C16 | =C10*$J$7 |
D16 | D16 | =D10*$K$7 |
E16 | E16 | =SUM($B$16:$D$16) |
B17 | B17 | =B11*$I$8 |
C17 | C17 | =C11*$J$8 |
D17 | D17 | =D11*$K$8 |
E17 | E17 | =SUM($B$17:$D$17) |
B18 | B18 | =$B$12*$I$9 |
C18 | C18 | =C12*$J$9 |
D18 | D18 | =D12*$K$9 |
E18 | E18 | =SUM($B$18:$D$18) |
B19 | B19 | =SUM($B$16:$B$18) |
C19 | C19 | =SUM($C$16:$C$18) |
D19 | D19 | =SUM($D$16:$D$18) |
E19 | E19 | =SUM($E$16:$E$18) |
A22 | A22 | =E19 |