Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculated Field Formula Help

Posted on 2014-09-25
2
Medium Priority
?
139 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 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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;…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

670 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