finding duplicate values and then using a formula to calculate

[daymaker]

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.

[p24leclerc]

Hi,
I build a new sheet in your workbook using VALIDATION DATA instead of list box or the controls you have in column A, C, etc.
It is as convenient and has lots of advantage (faster to calculate, values are linked to cells)

Look at sheet named NEW and try to work on it, you'll see what I mean.

Also, I changed your formulas. Your formulas were wrong as they divide a percentale by 100 making it even smaller. Also, when you subtract one percentale from another one, you don't have to divide again by 100.

I created the formulas so that you will not have errors like DIV#0 or #VALUE.

Last, your formula in remaining test effort is working. As the Week number change, the formula is different.
Pierre