Thread: generating a chart from excel sheet that compares two fields

Hello,

I hate to admit it but I am absolutely useless at excel which is very bad (I know). My boss handed me a sample chart diagram that he wants me to replicate for our business in the number of audit inspections we carry out every month plotting the Planned number and Actual number carried out on to a spreadsheet. He also wants the results to generate a graph which will show our progress over the course of a 12 month period.

Now I have tried to make this chart but for some reason am not having the best of luck figuring out what exactly it is that I want to do. I get so far and then it crumbles around me and I figure I may as well seek help from experienced excel persons like yourselves.

Attached is a spreadsheet (sheet one) which shows the fields that we need to use in order to refer back to whenever we want. The important fields are
· Field One – It will be the planned number of audits carried out per month over a period of 12 months
· Field Two – This is the work in progress field that highlights the second column on our chart illustrating when an audit has been completed.

I originally thought a third field might have been needed in using the ‘Report Completed’ field but this is really a duplicate of field two (isn’t it?)

SHEET 2 is my illustration of how I would like it to look but as yet have failed many times using the chart wizard to generate such a chart. The other problem is I am trying to group dates within a month in to a group category of a month as illustrated in sheet two
{Q} is it possible to automate this so that entries added in to sheet one will automatically be updated to the chart?

If someone could share an insight in to either what I am doing wrong here I would be eternally in your debt.

I really need to sit and start from the basics of learning excel as it truly is probably one of the most important inventions since the wheel

NB - please ignore the colour breaks on the dates. It was only me trying to seperate how the individual dates should be grouped together as months for the chart purposes.

You need to summarize the data before charting it.

In the attached I used a SUMPRODUCT formula.

Sorry not quite undersdtanding.

It is automatic if you change the range used to define Planned and Completed dates. Currently set to A3:A48 and B3:B48.
You could either use a very large range to cover future projects.
Or used named ranges.

Don't want the credit of Mr pope here but is this what is meant

Code:

=SERIES(Sheet3!$D$1,'662090.xls'!XSERIES,'662090.xls'!Completed,1)

If data is added to the table in standard sheet, then the chart is updated as well.

Being interested is the best start getto so you'll do just fine.
Look at the defined 'names' and try to learn about dynamic ranges that are used in charts like =offset($A$A1,0,0,countif($A:$A)-1,1)
Understanding OFFSET() and understanding 'Names' is not very difficult. Make this a combination and voila ...
I see you have already mastered Array functions. Then this is the easy stuff.