Solved

# Calculated Field Formula Help

Posted on 2014-09-25
130 Views
I work a lot in Excel, but haven't done calculated field work before.  Essentially, I need to take a populated column in a pivot table, in this case it's a count of whatever by hour of day.  So rows is the hour of the day and the data is the count of customers by hour.  I need to add a column to take that total number of customers by hour and divide it by 91, because I ran the data for a 3 month period.  It absolutely has to be part of the pivot table because I'm trying to create a dynamic graph that can be pivoted and updated. I attached a simple copy paste and formula of how I did it without the calculated field.

What I'm struggling with most is what the formula is within the calculated field to say   take the field from the main column of the pivot table and divide it by 91.

Thanks,
Andy
Experts-Exchange-Example.xlsx
0
Question by:akatz66
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 27

Accepted Solution

Glenn Ray earned 500 total points
ID: 40344710
You have the right idea of creating a calculated field in the PivotTable, just need to execute it.

1) Select a cell in the PivotTable.
2) Select Menu: PivotTable Tools (contextual tab) - Options - Fields, Items, & Sets - Calculated Field...
3) Enter a field name (ex., "Avg Daily Count"), then enter =Count/91 in the Formula box and then click the "Add" button:
The new field should automatically appear in the PivotTable; you just have to format the number to your liking (ex., 2 decimals)

I've created an example for your first five time slots.

Regards,
-Glenn
EE-Experts-Exchange-Example.xlsx
0

Author Closing Comment

ID: 40344747
My problem was that I didn't count a column called count.  I was actually counting another field in the data, so the calc wasnt working right.  I added a column name Count to my data table where all = 1 and then it worked.  Thanks Again!

-Andy
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.