Formula to Calculate Multiple Columns into a %

Not so formula savvy

I am trying to calculate multiple columns (containing dates, text, drop downs etc.), that are not in sequential order to track the percentage of the columns are completed (not blank).

I am willing to put the columns together if it's easier to tally the % but I worry if a column is added or removed, the fx will get corrupted?

Hope that makes sense?

Column e.g.

Column 1 - Date

Column 2 - Yes

Column 3 - text

@Paul Newcome 😁

Brian Wilson DC

@Not so formula savvy I would manage this with 4 helper columns. I would have my sheet setup as follow:

Date Column - Set to Date Only

Yes Column - Set to either text entry, or drop down containing a "yes" value.

Text Column - Set to text entry

% Complete Column with percentage formatting applied with the following formula:

=IF([% Column Helper]@row = 3, 1, IF([% Column Helper]@row = 2, 0.66, IF([% Column Helper]@row = 1, 0.33, IF([% Column Helper]@row - 0, 0))))

Date Helper Column set to the following formula:

=IF(ISBLANK(Date@row), 0, 1)

Yes Helper Column set to the following formula:

=IF(ISBLANK(Yes@row), 0, 1)

Text Helper Column set to the following formula:

=IF(ISBLANK(Text@row), 0, 1)

% Complete Helper Column set to the following formula:

=SUM([Yes Helper]@row:[Date Helper]@row)

You can hide all of the helper columns, but for reference I've left them open below. It will produce the following results:

I hope this helps!

Regards,

Brian

Not so formula savvy

I probably should add that I technically have about 17 columns in one scenario and 60 columns in another…?…hope that made sense?

Paul Newcome

@Not so formula savvy While this may technically be possible with the columns not being next to each other, it will be much more efficient if you did have them together. As for your concern with columns being added if they are all together, as long as the new column is added after the first column but before the last column (so anywhere in between the first and last) it will be picked up.

=COUNTIFS([First Column]@row:[Last Column]@row, @cell <> "") / COUNTIFS([First Column]@row:[Last Column]@row, OR(@cell = "", @cell <> ""))

Having them separated doesn't necessarily require helper columns until possibly we get to that one that has 60 fields, but that is simply to work within the character count restrictions. It also means that columns added in the sheet that need to be included will also need to be manually added to the formula(s) manually each time. The basic idea is that you string a series of IF statements that output 1 or 0 based on whether or not the field is blank and add them together and then divide by the total number of fields.

=(IF([First Column]@row <> "", 1, 0) + IF([Second Column]@row <> "", 1, 0) + IF([Third Column]@row <> "", 1, 0)) / 3

As you can see… The character count can quickly get up to that 4,000 limit in which case you would then need another column with a similar formula to house the next set of columns. Once all of these sections are created, you would then add them together in another column.

So the next column would be

=(IF([Fourth Column]@row <> "", 1, 0) + IF([Fifth Column]@row <> "", 1, 0) + IF([Sixth Column]@row <> "", 1, 0)) / 3

Then

=[First Section]@row + [Second Section]@row

Of course you would be able to have more than 3 in each section, but that is the general idea if you wanted to keep the columns separated.