Chart jagged, irregular data - maybe a pivot table?

**pmdprt** · 09-07-2010, 06:04 PM

I use Excel only for the most basic operations and tasks and am far from an advanced user. I have a requirement to chart some data and I'm having a difficult time figuring out how to accomplish it without manually pre-processing all the records (rows).

I need to chart the percentages of statuses per day on a set of leads we received. In other words, we received a variable number of leads each day and each lead as a status or disposition. I need a line graph that will show me the percentage of leads for each status for that day. Here is some sample data:

Please Login or Register  to view this content.

As you can see each day can have a subset of available statuses. In other words, there are 8 potential statuses (not represented in the sample above) but the data I have will only list the statuses that have at least one lead attribute to it.

I would like to condition or process this data so that I end up with a line graph that would show, for example, the "WON" status with the following values:
7/18/2010 50.0%
7/19/2010 44.4%
7/20/2010 20.0%

and for "Ready to Order":
7/18/2010 8.3%
7/19/2010 0% <-- ** Note the 0% inserted when there is no data
7/20/2010 20.0%

That's about the best explanation I can give. Hopefully it's clear enough that you understand what I'm after. I'll really appreciate some help on this if it's even possible.

Thanks,
Steve

**Cheeky Charlie** · 09-07-2010, 06:26 PM

I'd agree, seems like a pivot table is the right solution:
1. Please title your thread with something that makes it more useful for others searching the forum (although nothing obvious springs to mind - I'm just trying to stop you getting mod-bashed)
2. Upload an example and we'll see what we can do (assuming you do 1)

hth

**pmdprt** · 09-07-2010, 06:35 PM

I tried to come up with a better title but couldn't think of anything. I tried again and hopefully have made an improvement. Thanks for the suggestion though, I appreciate it.

I have attached a sample file to this reply.

**MarvinP** · 09-08-2010, 03:00 AM

Hi pmdprt
See the attached with a Pivot Table included on your sample. Right Click anywhere on the Pivot Table Total Column to display the dropdown. Click on Show Values as and look at your options. If the one I've attached isn't exactly correct try other Show Values As or Summarize Value As options.

**Cheeky Charlie** · 09-08-2010, 03:14 AM

Tweaking Marvin's upload, I think you need to:
right-click a status
go to field settings
put a tick in 'show items with no data'

or (probably more useful for charting) drag date received to the column headers area

You would need to replicate your data from the pivot table with simple formulae like:
G15=G3 copied down and across to give you more control over the formatting of the chart than pivot charts will allow

hth

**pmdprt** · 09-08-2010, 08:20 PM

Cool, thanks for the help guys!!

I made Cheeky's suggested changes to show blank value and that gave me an equal status set for each date, good.

You're correct Cheeky, I do need to break the data out but when I try to do a simple copy (or access the Total column in a formula) it results in the following function call:

PHP Code:


=GETPIVOTDATA("Count",G2,"Date Received",DATE(2010,7,18),"Status","CUSTOMER-Won")

As you can imagine this prevents me from "click-drag-replicating" the formula for all needed fields.

I finally "cooked" the pivot table down to dumb cell data by using Paste Special > Values.

I now have a lot of tweaking to do to try and find a way to chart this the way I want. Thanks for the help getting me this far, I need to learn pivot tables as they seem pretty powerful.

Thanks again,
Steve

**Cheeky Charlie** · 09-09-2010, 03:44 AM

if you select a cell in a pivot table whilst writing a formula it will give you the getpivotdata syntax (this can be quite handy) if you just type in the cell address you can treat it like a regular drag-across formula

If you give a picture of qhat you actually want to see (paint is your friend) we might be able to make it simpler.

hth