How to calculate remaining % using required % and performed %?!

Hello all,

I am trying to determine project hours by percentage as well as hours by grade level (GL).


I have 6 columns:

Hours Required (based on GL)
Hours Performed
Hours Remaining
% Hours Required (based on GL)
% Hours Performed
% Hours Remaining

I have the hours formula complete, as each grade level has a different requirement for projects they must complete in a total of 4 levels of complexity. Using a long =IF statement by grade level

=IF(Dashboard!G4="PC 1","1260","")&IF(Dashboard!G4="PC 2","840","")&IF(Dashboard!G4="PM 1","775","")&IF(Dashboard!G4="PM 2","1040","")&IF(Dashboard!G4="PM 3","1040","")

I can calculate required/performed/remaining. Now, using this same approach, I need to show the completion in percentage.

I have the initial 'Required %' formula done:

=IF(Dashboard!G4="PC 1","60%","")&IF(Dashboard!G4="PC 2","40%","")&IF(Dashboard!G4="PM 1","25%","")&IF(Dashboard!G4="PM 2","20%","")&IF(Dashboard!G4="PM 3","20","")

What I am trying to do now is calcuate the total percentage performed and remaining. I assume I will use the output of the 'Required' Formula and the 'Performed' Formula to create a sum of some kind, but I am scratching my head on how to calculate this.

Any ideas?

Can you post a sample workbook with expected results. I think your "IF" formulae could be replaced by using a table and VLOOKUP.

A sample is attached (see Sheet 1)