This might be someone's homework, but it's not my homework

May 8, 2009 12:18 PM

I'm trying to teach myself to be an Excel guru, and I'm having trouble with pivot tables. I'm using data from exercises in a textbook (which is about how information systems are used in business but randomly has a bunch of Excel exercises in the back). I can come up with a solution, but I don't know if I'm doing it the most efficient (or most elegant) way.

The exercises seem to build off one another, so this example might also use goal seek or problem solver. The sample data is transport invoice costs from 5 transport companies to several different states. The columns are Business Name, State, Shipping Weight, Invoice Amount, and Shipping Costs per Pound. The exercise is to analyze the data and recommend 2 carriers to stick with.

To get started, it says to calculate:
average cost per carrier
total shipping costs per state
total shipping weights per state
average shipping costs per pound

I get that I can put all of that stuff into a pivot table, but doesn't that seem to be way more information than is needed? If I have average shipping costs per pound per carrier per state (whew!), can I multiply that by total shipping weight per state (for all carriers) within a pivot table? I can see how to add a calculated field, but not one that will give me the calculations I want.

If anyone is curious, I can provide the sample data and a .pdf of the exercise text.

posted by amarynth to Computers & Internet (5 answers total)

I've done some work with Excel pivot tables and would suggest you skip them in favor of learning Microsoft Access. The pivot tables in my experience are more trouble than they are worth. With Access you can accomplish the same tasks with less effort and there is a lot more documentation and tutorials.

Granted I've only don't two or three pivot table projects, but they were enough to make me swear them off. My employers thought they would be able to do complex calculations like a full-blown database which is simply not the case. If you need to manipulate the data instead of simply organizing it then you'll probably want something more robust. People underestimate the breadth of Access's features. When Access stops being enough then it is time to move on to HTML, MySQL, and PHP imo.

One thing to look out for with pivot tables comes in the form of known bugs. There are a few actual, bonafide bugs within Excel that I lost hours to thinking I was doing something wrong. I was using Excel 2003, maybe things have changed since then.
posted by Gainesvillain at 12:41 PM on May 8, 2009

Paragraph 2: don't should read done. Lo siento.
posted by Gainesvillain at 12:42 PM on May 8, 2009

Best answer: I"m assuming cost per pound is your invoice divided by weight.

for average cost per carrier, drag the business name into the row label, drag the invoice amount into the values area, then right click on the "sum of invoice amount" cell, choose value field settings, and change it to average.

total shipping costs per state would be states in the row label area, invoice amount in the values area, and the field setting on totals

Total shipping weights per state would be states in the row label, shipping weights in the value area, field setting on totals

for average shipping cost per pound, you want to change that field setting again, from total to averages, with whatever labels you want in the row label area.

I love love love pivot tables. They are definitely worth taking the time to practice with. this is not an Access job--its all about the numbers, and that is not the best use of Access.

Hope that helps. If I'm making bad assumptions, I might have to see the data to get the picture. the ability to drag and drop the field names into the boxes and drag them back out is a very quick and easy way to work these out, and being able to change the calculations with a right click is also handy. In some cases, you can use a field as both a label and as a value (if, for instance, you wanted to calculate the number of times a state showed on the list).
posted by midwestguy at 1:36 PM on May 8, 2009 [1 favorite]

I like pivot tables because then I don't have to fire up Access and/or import my data just to get a simple summary, that I will then need to export back to Excel. The trick is knowing when a pivot table won't be powerful enough to do what you need.

doesn't that seem to be way more information than is needed?

In that particular example, it probably is more info than you need but they are using it as a learning tool. When you have to do a similar thing IRL, you may need the info at that level of detail. Perhaps there is a certain carrier that is the cheapest in some states and not in others, and there is "miraculously" another carrier that is the cheapest in those other states.

I don't know if I'm doing it the most efficient (or most elegant) way.
There are often several different ways to get to the same solution in Excel. In the end, if you are confident in the methods you used and get a matching answer, don't worry if you used five steps and someone else only used three.
posted by soelo at 2:55 PM on May 8, 2009

Response by poster: Hmm. I think that I was just thinking about it the wrong way -- if I was doing this as an actual assignment, it would be to write a report including pivot tables and charts, so I thought there was one magical pivot table solution that would show everything one would need to know, but it seems to me (and going from what midwestguy said) that the whole point of pivot tables is how quickly you can adapt them to different views.
posted by amarynth at 4:16 PM on May 8, 2009