How to autopopulate % Complete based off of other cells entry

tchav

Hello,

I am trying to autopopulate % complete based off of other cells YES/NO entries in the sheet.

I have 5 criteria to determine if we get to 100% Completion or not:

Columns:

Paperwork Received - YES (add 20%) NO (add 0%)

Samples Received - - YES (add 20%) NO (add 0%)

HPCL Received - YES (add 20%) NO (add 0%)

Hold (Yes/No) - - YES (change to 0%) NO (add nothing )

Micros - YES (add 20%) NO (add 0%)

Discrepancies - YES (add 20%) NO (add 0%)

Autopopulating:

% Complete Column

I was trying to use IF statements to combine them but it wasnt working.

J Tech

Hi,

You could try:

=SUM(IF([Paperwork Received]@row = "YES", 20, 0),IF([Samples Received]@row = "YES", 20, 0),IF([HPCL Received]@row = "YES", 20, 0),IF([Hold]@row = "YES", 0, 0),IF([Micros]@row = "YES", 20, 0),IF([Discrepancies]@row = "YES", 20, 0))

tchav

That worked! Thank you so much!

Last question:

Now I have status symbols too in another column. How would I have the symbol colors changed based off of the percentage listed?

Paul Newcome

@tchav You would use a nested IF statement.

=IF([% Complete]@row<= 0.50, "Red", IF([% Complete]@row<= 0.75, "Yellow", "Green"))

You can change the decimals to whatever thresholds you need.