Graphs based on Conditional Formatting

[ljmarstin]

Is there a way to develop a graph based on the conditional formatting in a column? I have multiple date columns that turn red or green based on if a task was accomplished on time or not. I need to be able to graph each one of the columns showing the percentage of times they meet or do not meet the milestones that are set. Looking for advise on best way to display the data.

Thank you,

Lynda

[MrShorty]

How much of this is specific to chart creation, and how much is more about how to calculate the data you need to show on the chart? Charts have essentially no calculating ability, so the first step, if I understand correctly, will be calculate the "meet/do not meet" results somewhere in the spreadsheet. It's not clear from your brief post how you are doing the conditional formatting. If you are using "format based on formula", then the formula you need in the spreadsheet will probably be similar to the formula you are using for your conditional formatting. Once you have calculated the desired data in the spreadsheet and arranged it properly (see here for a detailed discussion of how to arrange data in the spreadsheet for easy charting: https://peltiertech.com/good-chart-data/ ), then the chart should be easy to create.

[ljmarstin]

I'm sorry if I was unclear. I have a current spreadsheet that uses formulas to determine the conditional formatting a date that is entered is less than or equal to 1 day from another date on the spreadsheet. I was asked if there is a way to show on a graph how many of them are green (meeting the formula) and how many are red (not meeting the formula). I am unsure what the formula will be to show each of the conditional results. I need it to show that 75% of the time, we are meeting the less than or equal to 1 day in a graph. Hope this helps clarify.

[MrShorty]

If I understand correctly, you have a column of dates with a conditional format that says =date>otherdate-1 or something similar. Do you have somewhere in the spreadsheet where you are counting how many dates meet the criteria (perhaps a COUNTIFS() function like =COUNTIFS(dates,">"&otherdate-1)?

[ljmarstin]

No. Those are the formulas that I need to set that up. I can graph off of there. I am so appreciative of the help. It has been a long long time since I have had to develop formulas and just can't remember how. I know what I need it to do but I can't remember how to write them.

[MrShorty]

It is going to be difficult to be specific without seeing how your raw data are set up. If you are unfamiliar with the COUNTIFS() function: https://support.microsoft.com/en-us/...rs=en-us&ad=us I would expect that you have all of your dates in a single column, and that you have the "reference date" somewhere else in the spreadsheet. From there it is a matter of understanding the exact logic you want to apply, then programming that logic into the COUNTIFS() function. As I said, it is difficult to be very specific without knowing some of the details of your spreadsheet.

[ljmarstin]

I have attached a sample of the spreadsheet so you can see what I have. They are the columns dates are being entered into. I think you and I are on the same page. I attached a sample sheet.

Lynda

[MrShorty]

There's quite a bit there. Looking at the conditional formatting rules I can see several columns compared to several other columns. While others would probably do this without helper columns, I would be inclined to add helper columns to simplify the programming. For column C, for example, the conditional format rule is format red if column C is 1 day later than column B. In column Z, I enter the same formula as the conditional formatting rule =C2>B2+1 (copy/paste/fill down as far as needed). Similar helper column for the other columns of interest.

Then, I choose a convenient place to build the summary table (I added a new sheet for this). In row 1, I enter the text identifiers/categories for each milestone ("routed date" "date offer accepted by applicant" etc.). In row 2, I enter COUNTIFS() formulas for each category. =COUNTIFS(Tracker!Z$2:Z$1048576,TRUE) to get the count of each. To get the ratio of lates to total, something like =A2/COUNTA(Tracker!A$2:A$1048576). Then use rows 1 and 2 or rows 1 and 3 on the helper sheet to build the chart.

Will something like that work for you?

[ljmarstin]

Sounds like it would work, I just need to make sure those extra columns are not visible as I am not doing the data entry and do not want anything overwritten or erased. Could you send back a sample of what you did so i can look it over.

[MrShorty]

duplicate!!!!!!

[MrShorty]

Here's my file. Note that I only did a couple of categories to illustrate the process. Also, I moved the helper columns into the second sheet in response to your concern about other overwriting something adjacent to the main table (though you might consider becoming familiar with Excel's tools for locking cells so people cannot inadvertently overwrite important formulas).

[ljmarstin]

Worked perfect!!! Thank you so much for your help.