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 |