Solved

Calculated Field Formula Help

Posted on 2014-09-25
2
128 Views
Last Modified: 2014-09-25
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
Comment
Question by:akatz66
2 Comments
 
LVL 27

Accepted Solution

by:
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:
Calculated FieldThe 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

by:akatz66
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question