Chart Source Data

[kwright90]

I am having problems getting my "Trends" chart to correctly note the date of each invoice expense. You should notice on the second worksheet where my source data is located. I need this "Trends" chart to illustrate monthly expenses by catagory. Is there something I need to be doing differently??? PLEASE help....

[kwright90]

My attachment was too large... sorry. Disregard....

[ChemistB]

Can you post a smaller example and/or zip your file?

ChemistB

[kwright90]

I have deleted two work sheets out of the workbook and zipped it and it is still 199KB.... any other ideas?

[ChemistB]

Can you delete all but 2 months of data to represent your values or delete all but a few values at each month?

ChemistB

[kwright90]

OK.. I have deleted 3 of the 5 charts that I will be using. The ones included now are the ones that are most obviously incorrect. I have also copy/paste specialed all of my vlookups and formulas out in order to reduce the size of the workbook. What I need is a chart for each catagory (Shop Investments, Training, Marketing, ect.) and how much funds are spent on each by month throughout the year. Originally I tried using just one chart but it was too cramped to show all information with out being confusing. My main issue is that these charts I have built are not showing correct figures. For instance I know from the data given that there has been $37,377.47 spent on Shop Investments this year but the chart is trying to say only $6,558.62 has been spent. Same with Training... the chart shows $14,300 has been spent on training when I know from the numbers that $17,500 is the true figure. Is there something different I need to do with my source data?

[ChemistB]

I see what it's doing. I just haven't figured out why. It's overlaying your column instead of stacking them. It did this even when I changed all the dates to April 1st and removed the falses. Hmmmmmmm

Okay, the stacked column chart stacks your different series. You are entering everything as a single series. It's hard to tell from the layout here but have you looked at;
1. Piviot Tables to sum your data
2. using Subtotals
3. Using Sumproduct Function

ChemistB

[kwright90]

I see what you are saying now about it overlaying my numbers...

I have been playing with a pivot table this morning and I can't seem to figure out exactly how I need to set it up in order to make a functioning chart.

As far as subtotalling goes.... what exactly would you suggest?

I'm not too familiar with the Sumproduct function.. or at least it being referred to by that.

Thanks a lot for your help! I will be playing with it on and off today.

[ChemistB]

It's very difficult to tell from your streamlined workbook what is pertainent but here's an example of how SUMPRODUCT might work.

The SUMPRODUCT Function can be used to sum 1 column based on criteria in other columns (I believe that Excel 2007 has a SUMIFS Function that does the same thing). It can do this because the criteria will be simplified as true/false and excel can be made to see the true/false as 1 or 0 respectively. The formatt is as follows

Please Login or Register  to view this content.

So if the date you are interested in is in Column C (From C2:C100) and you want the April Dates that expression would be

Please Login or Register  to view this content.

Then lets say you want Column G to be Shop Equipment

Please Login or Register  to view this content.

And your final expression, Assuming you want to sum $ in column F

Please Login or Register  to view this content.

ChemistB

[kwright90]

I really do appriciate you helping with this ChemistB and I really hate to ask, but could you put this in an example spreadsheet?

[kwright90]

Is there NO way to keep my charts from overlaying my information??? This is aggrivating.