Hello,
I'm creating an excel workbook with certain entries which will help me in scheduling tasks for my team, calculating effort. Now, the first column is the calendar week(CW). So the calendar week could be anything starting from 1 to 52. Also, there could be more than 1 data cell which would contain the same calendar week. Lets say there are 5 rows with CW as 1, 6 rows with CW as 2, 10 rows with CW as 3 etc. In columns B,C,D,E & F, I've some data as well. In column G, I've some numbers. Now, in the column H, I need to calculate the effort required for which I use the numeric data in column G&F. I'm using the formula (Gn/176)/Fn, where n denotes the row number. This is working fine. Next, in column I, I need to calculate the effort remaining. For this in the 1st data cell I'm using the formula (100-H2)/100 for the 1st data cell(effort is being calculated in terms of %). Now, for the next data cell in the same column, the effort remaining would be calculated as (I2-H3)/100, for next cell (I3-H4)/100 and so on. The reason being, in a particular CW, the effort has to be 100%. So now, when the CW value changes again the process starts from the beginning by calculating the effort remaining using (100-Hn)/100.
I wanted to know if we can use a similar formula(syntax) for the entire column E(effort remaining) which would automatically check for the corresponding CWs and adjust the calculation formula. I believe it can be done using IF-THEN-ELSE but I've just started to learn the Excel formulas. Could you please help me out with this?
I've attached the excel workbook which I've created.
Bookmarks