Forum Discussion

Siim Lüüs's avatar
Siim Lüüs
Copper Contributor
Oct 23, 2017

help with formula

Hello, 

How is the simpliest way to solve this kind of problem (example file):

I want to make easy resources planning table.

First i insert work lines (department, year, planned start week, planned end week, planned quantity, planned time (h), % ready. Time prognosis is calculated by (planned time/planned quantity) * ((100%-% ready)*planned quantity).

Second table is replanning works - it takes time prognosis and divides it between weeks (if work is not ready and planned start is before current week, planned start is replaced with current week; if work is not ready and planned end is before current week, planned start and planned end is replaced with current week.

Now the main problem - how can i get the table what shows planned time per every week (see the planned time per week table)?

Thanks,
Siim

Formulas & Functions
Need Help

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 23, 2017

    If to take from the second table that could be

    =SUMPRODUCT(($T$4:$T$7)*($E$4:$E$7=$V4)*($Q$4:$Q$7>=W$3)*($R$4:$R$7>=W$3))

    second multiplier checks the department, third and fourth ones if the week is within your range, and we sum all what meet the criteria from latest column

     

    You may add another AND conditions criteria, e.g. year

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 23, 2017

    Hi Siim,

     

    Does that mean you ignore re-planning time (second table) in the third table? Or you take Time Per Week from second table?

Resources