Create pivot table

**nmss18** · 06-14-2011, 11:49 AM

Hello,
I am working with a speadsheet of call center data. Please see attached file. Management would like to see a chart displayed in the format seen in the tab called 'desired format'.

I am trying to accomplish this via a pivot table but am not sure its possible. Nevertheless, I am hoping that someone 'out there' might have a better idea than me, if this can or cannot be done.

Much appreciated.

-Nathan

**MarvinP** · 06-14-2011, 01:57 PM

Hi nmss18,

Find the attached. I had to rearrange your data a little. I'm not sure the Ratio makes sense now as it is the sum of ratios, but it may be correct if there aren't multiples in a single sum cell.

**nmss18** · 06-14-2011, 04:55 PM

This is really impressive and I am impressed. Its almost exactly what i am looking for but as you suspected, the ratios are off when the totals are tallied. (The totals handle ratio for each agent is correct but I cannot understand why if it is adding the sum..)
Anyway, any idea how to bypass this issue?

**MarvinP** · 06-14-2011, 05:18 PM

Hi,

The problem is that you have call numbers for two of the rows and then a percent as the other number.

I've changed the values to show as Average which may work. See the attached. Check of the numbers.

**nmss18** · 06-16-2011, 12:06 PM

I understand what you did, but unfortunately, while it resolves the ratio value it meses up the call volume value. ideally, I would like to see a sum for the call volume and a average value for the ratio. In addition, we wouldnt want to see the ratio values added up with the call volume.
Very customised and understandably it might be impossible. I will tip my hat if you can figure out a way to pull this off.

I recall an old posting I saw years ago where a valued contributor mentioned a 3rd part plugin software that allows you to customise pivit chart and tables. Are you familiar with this?

**MarvinP** · 06-16-2011, 01:02 PM

The add-in that I'm familiar with is PowerPivots but only works with 2010. http://www.powerpivot.com/

**nmss18** · 06-16-2011, 02:54 PM

Thanks. i took a look and although I don't think it could do what i am looking for it looks like a worthwhile tool. thanks for your help and assistance.