Solved

Calculated Field Formula Help

Posted on 2014-09-25
2
132 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
[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
  • Learn & ask questions
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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

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;…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

630 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