Difficulty creating charts from complex data set

Hi there,

I'm having an issue with a complex set of data and hope someone can help me.

I am working with a financials spreadsheet, which has three rows per project/work area (for OPEX, CAPEX and total spend) and three columns per month per project (budget, forecast, actuals). The sheet needs to cover the whole financial year, so there are 12 months - each divided into three. The sheet is quite complex due to the size and data import methods used, but I need to create a chart showing budget/forecast/actuals over time, per project (i.e. filter to show one project at a time).

I've attached a sample sheet, which shows 4 projects (in reality there are around 60). I realise that the spreadsheet might have been better in another format altogther but am loathe to go down that path due to the size and the way the data is pulled from our financial extracts each month.

Another complicating factor; the data needs to be showed cumulatively, i.e. August figures should actually be July + August.

Is anyone able to offer advice on how to produce a line chart which shows this info clearly?

Thanks so much,
Rosa

There is no easy way to chart that data.
You will need to create a data set in a layout suitable for charting.

Kia ora,

I'm absolutely with Andy here (how could I not be?!)

Attached is a chart that I painfully stitched together by Ctrl-clicking the respective cells for the series, but with more than 60 projects you'll get RSI and will have OSH on your back.

Better to create a table looking up the values you want to chart and then build the chart off that simpler table. Once you lay the groundwork with a summary table, you can actually do some nice dynamic charting, i.e. selecting projects from a drop-down list, etc.

cheers,

It would help to know what your desired chart is. Do you want to show projects side by side? Do you want to compare forecast vs actuals for a project? Etc.

Kia ora,

see attached, Sheet2.

I've created three different lookup formulas, one for OPEX, one for CAPEX and one for Total.

Enter the project ID in B2.
B3 is needed by the Index() formula, as are the numbers in columns L to N. There are probably ways to incorporate these into the Index() formula, but this way it's quite fast. You can hide columns L to M and row 2, if you want.

Now you can start charting.

To do an overall total of all projects, I think it might be best to do the summing up of all data on Sheet1, give that some kind of Project ID and use it like a project code.

Does that help any?

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

@MarvinP,

this is a great suggestion and supports my general belief that data entry should happen in one sheet and data analysis in another.

There may be situations, though, where the OP can not influence the source data, for example, if it is delivered by a company ERP system in a pre-determined format. Believe you me, I've seen my share of badly designed CrystalReports and other BI outputs that the end user just has to suck up and swallow.

If RosaNZ has the power to change the data entry for the data she needs to analyse, then your flat file format is certainly beneficial.

I hope I have presented a way of grabbing data from a pre-formatted report-style output that is dished up to the user without them being able to change the format or presentation.

RosaNZ, you have several options at your fingertips, now. Make the best of it.

Kia ora.