Plotting a chart to show monthly target and achieved

[cherias]

Hello All,

I am caught up in a weird situation, when plotting a chart to show monthly target vs achieved data. The scenario is that for particular month, say MAY, the SR's targeted for completion was 9 and 1 of it got completed in APR and the rest 8 got completed in MAY, so when plotting the graph, its showing that 1 less was completed in MAY, whereas it was completed earlier itself. So my query is how to show target vs achieved in case, few of the projects/tasks may have been completed before time or earlier month. Please advise and assist.


Explained below with screen shots -

RAW DATA

Raw Data for Excel Forum_Target Vs Achieved.jpg


CONSOLIDATED TABLE (populated using COUNTIFS, to know the number of SR's targeted and achieved for a particular month)

COUNTIFS Data_Consolidated Table.jpg


FINAL GRAPH, which shows a deficit by 1 in "Achieved" for MAY whereas that 1 SR was completed before time in APR

Actual VS Target Chart - Discrepancy.jpg

[Pepe Le Mokko]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Andy Pope]

It's not the chart that is wrong but the formula you are using to determine which of the Apr and May records were completed.
Rather than counting the Actual completions for a given month you should count the targets for a given month AND those that have any Actual completion date.

[cherias]

Hello Andy / Pepe,

Thanks for your guidance on how to use the forum correctly. Please find attached the raw data sheet for the issue. As mentioned earlier, I am trying to plot a chart to show the target vs achieved per month. Now look at the chart (pasted earlier) for the month of May is shows that one less project was achieved (08) than what was the target (09). However, when we look at the raw data, it seems, that one project was completed early and in month of Apr. So if someone looks just at the graph then it seems that for the month of May'19 one less project was achieved than the target but that is not the case. So how to plot or depict this scenario in a graph. Looking forward for help and guidance

[JeteMc]

As Andy suggests, I believe that if you paste the following formula into cell C2 on the CountIFS_Table sheet and copy down, it will yield the expected results:

Please Login or Register  to view this content.

Let us know if you have any questions.

[cherias]

Hello JeteMc,

Thanks for the inputs and it worked well for the data set I had provided. So its excellent solution.

However, I may need a little more help to fit in this formula to a chart I was doing and attached in the raw data for it.

So what additional I did :-

1) Add columns for LoB & DC in the Raw Data sheet
2) Inserted a Option and Combo Box in the CountIFS_Table sheet
3) Added a column Achieved With Conditions in the CountIFS_Table sheet. This columns calculate the achieved value based on selection of the option & combo box

The objective is that to have the column "Achieved With Conditions" updated based on the selection of the option & combo box.

Problem :- When I select "All" from drop-down, then it gives me an error.

[JeteMc]

It appears that the issue is with the formula in cell D19 which reads: =INDEX($M$19:$M$23,D18,0)
If that formula is modified to read =INDEX($M$19:$M$24,D18,0) then values are given for "ALL".
As the values given in D5:D16 are the same as in C5:C16 you could change the formula in D5 and down to read:

Please Login or Register  to view this content.

I believe this should be more computationally efficient, unless the intent is to remove column C.
Let us know if you have any questions.

[cherias]

Thanks a lot JeteMc for the kind help and guidance.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.