Where my Excel peeps at?
Mar. 2nd, 2010 09:05 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
thebratqueenAnybody 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):
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!! =)
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!! =)
