Formatting and Status Checks

Pestomania

Hi,

I am trying to find the simplest way to format and complete a "status check" on each row based on the field "Process Type".

So for each process type, I have identified a list of fields that are required to be filled in. I can use conditional formatting to make each of those "highlight" when empty, that's simple.

What I would like to identify is a way to determine:

How many fields are required for the process type (Denominator)

How many fields are completed for the process type (Numerator)

What is the percentage complete?

Paul Newcome

Are you able to provide some screenshots for context?

Pestomania

This is the best I can think of doing???

The highlighted are "required" fields.

Paul Newcome

Are you looking for the formula(s) in general, or are you trying to get something specific for a column formula? Things are going to have to be hard-coded into your formula(s) or manually entered into helper columns though as we cannot base formulas off of formatting.

Pestomania

I am okay with having another table or something that indicates which column names are required based on Process Type. Is there a way to tell the system to look for the Process Type in "Table Requirements" and look for responses in those columns?

I don't mind the hardcoding either because these tables don't change enough. I just don't know how to write that formula.

Paul Newcome

There MIGHT be a way to get a table to work, but it wouldn't be specifying column names. It would be more specifying the column indexes which does mean that any changes to the order of the columns would need to be handled appropriately.

Basically what you would do would be to create your table with the process in one column and then a string in the next column that represents which columns should be filled in. Make sure it is a multi-select dropdown type column with the numbers one through four as the options.

Parent Process ….. 1/2/3/4

Sub-Process ……… 1/3/4

Third Party ………… 3/4

Critical Apps ……… 1/4

For maintenance and troubleshooting, I would use a helper column in the working sheet with an INDEX/MATCH to bring this string over from your table. This column would be [Required Fields] and would also be a multi-select dropdown.

Then in the next column [Filled Fields] to output which columns have in fact been filled in you would use something along the lines of

=IF(Description@row <> "", "1" + CHAR(10), "") + IF(Building@row <> "", "2" + CHAR(10), "") + IF(MTD@row <> "", "3" + CHAR(10), "") + IF(Strategy@row <> "", "4" + CHAR(10), "")

And finally the [Percent Complete] column would house something along the lines of

=IFERROR(COUNTM([Filled Fields]@row) / COUNTM([Required Fields]@row), 0)

Pestomania

What would this look like if I was just doing direct formula coding?

Paul Newcome

Is it possible that for one of the rows that doesn't require all four, a field that is not required could have something in it, or are you able to say that for a certain row only two are needed and then we assume that if there are two cells out of the four filled in, they are the correct ones?

Pestomania

It is possible that it could have something in it, but I would say it should not be counted towards the overall progress as it may skew the information. Lets say that if I needed "Building" but inputted "MTD", then I will be missing "Building".

The conditional formatting assists in identifying missing data but the integrity of the data would say it is 100% when it isn't.

Paul Newcome

In that case, it would get pretty complex. The most straightforward approach would be a nested IF that basically says if the Process Type is "ABC" then insert string of IFs added together based on required fields which would then be divided by a hard-coded number. Then a nested IF using the same logic for Process Type, "DEF", so on and so forth.

=IF([Process Type]@row = "Parent Process", (IF(Description@row <> "", 1, 0) + IF(Building@row <> "", 1, 0) + IF(MTD@row <> "", 1, 0) + IF(Strategy@row <> "", 1, 0)) / 4, IF([Process Type]@row = "Sub-Process", (IF(Description@row <> "", 1, 0) + IF(MTD@row <> "", 1, 0) + IF(Strategy@row <> "", 1, 0)) / 3, IF([Process Type]@row = …………………………………………………………………