If resource="test", and percentage = 100%, then (total time - hours) = remaining time

[mpatten]

Hello,

I am trying to write a formula as follows: IF RESOURCE="TEST", AND PERCENTAGE = 100%, THEN (TOTAL TIME - HOURS) = REMAINING TIME

RESOURCE (!Priority List'G2)
HOURS (!Priority List'H2)
PERCENTAGE (!Priority List'J2)
TOTAL TIME (B2)

This is to show remaining hours a resource may have after completing a task.

[RChad]

Hi,

Can you attach sample workbook please? I can only guess this might work without the workbook to test the formula.

Please Login or Register  to view this content.

[mpatten]

Attached sample.

[RChad]

Hello,

I inserted the Actual Time and now the percentage of work is calculated.

See attached revision

[mpatten]

Thank you, but what I was actually looking for was on the Resource Hours tab.

So if a project hits 100% completion, those estimated hours from that row would be subtracted from the overall estimated hours to the allotted resource.

[RChad]

So which comes first the chicken or the egg? What I mean is the percentage determined first and then the hours remaining calculated or visa versa?

[mpatten]

Well I supposed the percentage would need to be calculated first, then the remaining hours calculated after on a separate sheet.

[RChad]

Does my latest version come close to what you want?

[mpatten]

No

So estimated hours is an accumulation of the total amount of hours estimated for a resource. So if a task hits 100%, doesn't need to be calculated, I had it so someone could just type in 20 and 20% was referenced, that would subtract the hours in that row from the accumulated hours for a resource.

Make sense?

[RChad]

I think you need to start with a basic setup on one sheet showing the expected results with examples.

Then maybe I can better understand what you are trying to do.

Thanks

[mpatten]

William has 50 hours of accumulated task time (10, 20, 20), if William completes the 10 hour task (100%), then he has 40 hours of remaining task time.

Estimated Hours Allotted: 50 (=SUMIF('Priority List'!G2:G45,"William)",'Priority List'!F2:F45)
Hours Completed: 10
Total Hours Remaining: 40

This shouldn't be calculated until the progress has reached 100%.

[RChad]

I am not sure again so I created a scenario in this workbook based on job time.