Where my Excel peeps at?

[thebratqueen]

Anybody out there in possession of l33t Excel skills? Specifically involving pivot tables? If so, I am in need of assistance, please!



I've only just started playing around with pivot tables so I am a total n00b here. Feel free to put replies in captain dummy speak. Also I'm using Excel 2003.

Basically, I'm having problems getting a pivot table to do the average of a calculated field. It can sum the field no problem, but if I change the field to average there's no result for any calculated field.

Here's an example. Let's say I'm an FBI agent working with a con man by the name of Neal(...as for example. Ahem.), and my boss has asked me to keep track of what he's costing us. Specifically he wants to know how many hours Neal was there each day, how much did Neal spend on food both per meal and in total, and something called NealNOM which he tells me is the result of taking the total cost of food and dividing it by the number of hours.

I plug in the raw data, make a pivot table, and said table looks like this (labels in bold indicate calculations):

Day	Hours	Breakfast	Lunch	Dinner	Total Meals	NealNOM
Monday	8	$5	$15	$85	$105	13.1
Tuesday	10	$6	$20	0	$26	2.6
Wednesday	8	$5	$10	$100	$115	14.4
Thursday	7	$9	0	$90	$99	14.1
Friday	8	0	$10	$200	$210	25.3
Total	41	$25	$55	$475	$555	69.5
Average	8.2	$5	$11	$95

So when I put in the Total field Excel has no problem plonking in sums of those columns. But when I try to have it do an Average field, the custom calculated fields that I put in vanish. It leaves blanks, just like you see above.

Why is this, and how can I fix it?

Thank you in advance!! =)

Comments

[justhuman]

oh.

I'm an engineer; we don't pivot. Why don't you ask about the statistical functions instead. I can make you a nice histogram :-D

*cough*

I actually had pivot tables in my Excel class last year. I'll try to look at the course book tomorrow.

[thebratqueen]

Thanks! I'm wondering if this is one of those things where it's either a step I don't know about since I'm teaching myself, or if it's one of those "Yeah, it doesn't do that" moments.

Either way, I need someone more skilled than I to help me out.

[reginagiraffe]

You can do it the dirty way. Say the "Total meals" column is F and the "Total" row is 7. In the F8 cell, type in "=F7/5" because there are 5 days. And then you can Copy,Paste that cell to G8 and it will automatically calculate the average NealNOM (or you can just type in "=G7/5").

I suspect there's a better way to do it in pivot, but I don't know how.

[thebratqueen]

Unfortunately I've tried that and it's not doing what I ultimately want, which is to change the days displayed (e.g. change from Feb to March) and still calculate the correct average.