• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help and advice on solving a problem

I have a particular question below that I would like to solve using formulae rather than VBA or Pivot tables (I appreciate I have posted a similar request "Can I create these charts by using a PivotChart technique" from 1st August). The reason for this new request is I believe some users of spreadsheets can understand the concept of formulae easier than VBA or Pivot Tables (if they are not familiar with them) especially when we distribute spreadsheets to other users.

However, it often amazes me how responders on this forum arrive at their solutions to particular problems. This is obviously a reflection of the responder’s level of experience in Excel and their understanding of the various functions involved.

So I would not necessarily just like the answer to the problem (although that would be very nice) but I would like to understand the thought process involved in solving the problem. As the old saying goes, “Give a man a fish, and you feed him for a day. Teach a man to fish, and you feed him for a lifetime.”

In the attached file, I have the data involving the delivery of documents to a client linked to various tasks in a MS Project Plan of Work (POW). I have copied these dates from the POW into the spreadsheet. This key information is in columns B:C. Each document has a “Forecast date” (for delivery), an “Owner” (a member of the project team) and a Status (“Complete”, “Overdue” or “Not Yet Due”). The Status values are determined from the POW.

I would like to develop a set of formulae in the cells I5:AY22 to show the following (in the attached example the coloured boxes have been manually drawn as an example):

  • For each Owner, a sum of the number of documents for each time period between Start Month / End Month.

  • For each of these values, we should see (with a coloured indication) if these documents are either “Complete”, “Overdue” or “Not Yet Due” vs. an Owner.

  • If this summary data set can be created, could it also be made into a chart so we could show “today()” as a line on the chart to show progress or delay but have a similar appearance to the area in G3:AY22?
Many thanks
 

Attachments

  • Chando-Documentation tracking chart.xlsx
    14.5 KB · Views: 1
In this layout, you have all conditions set up in header ranges for columns and rows of matrix.

Since, you are counting number of records. Your first option is to use COUNTIFS.

So, date range must be both >= Start of Month and <= End of Month.
=COUNTIFS($B:$B,">="&I$3,$B:$B,"<="&I$4)

COUNTIFS() family of functions, automatically shrink down calculation range to used range. So you can safely use entire column as criteria range.

Looking at your set up, you want criteria references to be static as you drag down, but move as you drag across. So relative for column and absolute for rows (I$3).

Now for other two conditions.
=COUNTIFS($D:$D,$H5,$E:$E,$G5)

This time you want Column to be static as you drag across, but rows to change as dragged down ($H5).

Put it together...
=COUNTIFS($B:$B,">="&I$3,$B:$B,"<="&I$4,$C:$C,$G5,$D:$D,$H5)

Copy across and down.

Next is to apply conditional format (CF). Since you want 3 separate colouring, you'll need 3 separate CFs.

Since, you need to reference H column always and it's own cell value need to be checked, formula CF is needed.

Select entire data range (I5:AY22).

=($H5="Complete")*(I5>0)

$H5 since, you always reference H column, but want to change row.
I5 since, check is done on each cells in range.
* is same here as AND().
In Excel True = 1, False = 0. So unless both left and right of multiplication is 1, it will be 0 (False).

Now do the same for other 2 state.

Now, since COUNTIFS will return 0 for non meeting criteria. You'd want to hide it.

One way to do it is to set font colour to match that of cell fill.

Then, in each CF apply Font and Fill colour to override base setting in range.

See attached sample.

I wasn't sure what you needed in for the chart.
 

Attachments

  • Chando-Documentation tracking chart.xlsx
    19.2 KB · Views: 3
Chihiro, this is a neat solution and very clearly explained. I had thought that I might need to use a LOOKUP function since the area of the data is not the same as the area of the output. I had thought of countif(s) too and also sumproduct but I could not get the calculations to work out. My question on the chart was not very well explained....all I wanted to do on a chart was to add a vertical bar to show where "today" was. This would then give a reference ... anything to the left of the "Today" line should have been completed or else there is a problem. Anything to the right of "Today" should be "Not Yet Done", or maybe "Complete" if the document has been provided early.

Many thanks.
 
Thanks. I normally use a technique similar to the one in the Peltier example. A lot of my charts are dates on the X-axis and % on the Y-axis. Through trial and error, I have found that when using an X-Y scatter plot for the chart, it is fairly easy to insert a vertical bar at Today's date by adding a new data series as shown below. Then add a data label to the top of the data series and link this to one of the two dates in the data series.

Regarding the original post, I think I can achieve the effect of highlighting Today's date (within a particular month) with CF.

upload_2018-10-25_15-23-23.png
 
Back
Top