Excel 2007 : Field Calculation based on Conditional Formatting Results & Text Dropdown Picks

I’m guessing there’s not an easy way to do this; however, I thought I’d ask…

Need to determine “% Complete” for individuals as well as for different populations:
-The % Complete would defined by whether the conditional formatting rules are satisfied on multiple fields.
-The populations would be associated with entries in different fields with dropdowns.

It’s easier to see this to understand it, so I attached a sample similar to my spreadsheet.

White date boxes are considered incomplete, and blue boxes are considered complete. (White boxes may be populated with a date even if incomplete.)
-Would like to calculate % complete for each individual record (see empty column at end)
-Would like to calculate % complete for groups (see empty columns on “dropdown lists” tab)
-Also would like to lock the “% complete” cells, so they stay as calculated fields.

Thanks!

Hello there,

Attached is your file updated to include the adjustments you requested. I left comments in a cell on the first worksheet and the second which hopefully explains what each cells formula is doing.

I have unprotected all the cells in your workbook except those that contained the percentages. To protect these cells you will need to go to the Review Tab and select protect Workbook, uncheck the select locked cells option and type in the password for your workbook. It will ask you for the password again and then you should be finished.

Please let me know if you have any questions!

Thanks!

Wow – thank you – you really quickly evaluated something that would have taken me several days to figure out. Terrific! And your comment explanations are so clear and helpful.

Thanks also for the tips on protecting cells; I had never done that before.

I do have a question though on Individual Percentage:

I like the formula you created for the individual record’s %. However, is there any way to base it off of the formulas in the conditional formatting for the cells of that row? I suspect it would be complicated since there are at least three variations. However, for this worksheet, whether it is populated isn’t the trigger, it’s only if the date falls within the ranges for that column (e.g., annually, every two years, anytime). If you play around and change the dates, you’ll see it will either stay white or turn blue.
Any ideas?

Also - just thought of one more thing - Just in case we choose to do this -

Is it easy to come up with a formula that excludes the N/A's from the 28 total items used in the % calc? It would have to be dynamic, based on each record... something like 28 minus the number of N/A's would be the denominator?

sorry for the extra item... thank you!

For your first question I'm going to have to take a better look tomorrow, but I'll see if there's something that I can come up with, as I will be leaving for the day shortly.

For the second question, all you would do is take out the addition sign and everything that followed it up to the / or divider sign.

Thanks,

RVASQUEZ

Thank you - a fresh head for both of us today will be good! Looking forward to more of your insight. What you provided me so far is a world of help.

Hi, for the 2nd question, I did this, and it seemed to work the way I wanted it to:

=(COUNT(L4:AM4))/(23-COUNTIF(L4:AM4,"N/A"))

[I needed the denominator to change, and it does. Also, there were hidden columns not to be included in the denominator, so I changed it to 23. But it works perfectly now. Thanks so much for the help!]

I'll let you kick around the conditional formatting idea, as will I.

Looks good, although you shouldn't have to do it that way. The COUNT functin only counts the cells that contain a numerical value so it would exclude the N/A since they are text. You should be able to use the below formula like I suggested earlier.

=(COUNT(L4:AM4)/23)

Hey there,
What exactly do you mean by the below question?

However, is there any way to base it off of the formulas in the conditional formatting for the cells of that row

I think I'm on the verge of doing what we discussed... using the COUNTIFS function and pulling the formulas from the conditional formatting into the formula in column AR. (I just reaquainted myself with what's in there, and I think it might work.)

Haven't tried it fully yet though, and will let you know what happens.

Also, yes, I understand what you're saying about the other part of the formula; however, if there's 28 categories and 5 don't apply to me, then I want the cells populated with dates for me to be divided by 23 insteaad of 28. That's why I adjusted it. However, I would have never gotten that far without your initial formula, so thank you!

Oops - I missed your earlier message somehow....

From a blank cell in body of the worksheet, if you go to Conditional Formatting on the Home tab, click on Manage Rules, click on the 2nd one & click Edit Rule, you'll see that the column you are in has a valid range for the date entries in that column, either:
- any date [<=today]
- annually [>=TODAY()-365.24]
- every two years [>=TODAY()-730.48]
- every six months [>=TODAY()-182.62]

And if you go back to the rules manager, it shows which columns that formula applies to.

So the attachment has the conditional formatting rules and columns that I am trying to incorporate into a COUNTIFS function in Column AB. However, I think I'll work on it tomorrow morning, unless you have any good tips. Thanks!

Although, I think we can probably work with the formula you gave me to count all populated dates, rather than all populated dates that fall into the range specified for that column, I was still trying to figure out how to count only the dates that fall into the range.

I tried this formula and couldn't make it work, whether I separated the range & criteria for each of the four scenarios with just a comma, or brackets & a comma, or parentheses & a comma:

=COUNTIFS(($L$4:$P$183,$S$4:$S$183,$U$4:$U$183,$AF$4:$AF$183,$AK$4:$AL$183,OR(Q4="N/A",AND(Q4>=TODAY()-365.24,Q4<=TODAY()))),($W$4:$W$183,OR(W4="N/A",AND(W4>=TODAY()-182.62,W4<=TODAY()))),($Q$4:$R$183,$V$4:$V$183,$AD$4:$AD$183,$AH$4:$AJ$183,$X$4:$AA$183,$AM$4:$AN$183,OR(Q4="N/A",AND(Q4>=TODAY()-365.24,Q4<=TODAY()))),($T$4:$T$183,$AB$4:$AC$183,$AE$4:$AE$183,$AG$4:$AG$183, OR(T4="N/A",AND(T4>=TODAY()-730.48,T4<=TODAY()))))/(26-COUNTIF(L4:AM4,"N/A"))

It'd be so nice just to be able to say if the date is blue, count it. And if it's background has no fill, then don't count it.

Is my goal any clearer to you? Any ideas?