Pivot table calculated field using MAX of another field

How do I get a calculated field to calculate using the MAX of another field?

Say I have the following:

SIZE     COLOR     Max of Qty    Sum of Cost     Sum of QtyXCost
large      blue            8                    5                      40
             green          2                    4                      8
             red             10                  6                      60
large Total               10                 15                     300*
medium green           4                 11                      44
             red              5                   8                      40
medium Total           5                  19                     171*

I want the calculated field QtyXCost to calculate using the MAX of Qty.

*At the total lines, I want 300 to be 150, 171 should be 95.

This is a simple example. In reality, I have more row fields and therefore
many more nested levels of grouped totals.













0
tzych5645 (4)
6/5/2004 5:25:52 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
644 Views

Similar Articles

[PageSpeed] 22

The subtotals and total for a calculated field will always be a sum. 
Perhaps you could calculate the QtyXCost outside of the pivot table.

Tim Zych wrote:
> How do I get a calculated field to calculate using the MAX of another field?
> 
> Say I have the following:
> 
> SIZE     COLOR     Max of Qty    Sum of Cost     Sum of QtyXCost
> large      blue            8                    5                      40
>              green          2                    4                      8
>              red             10                  6                      60
> large Total               10                 15                     300*
> medium green           4                 11                      44
>              red              5                   8                      40
> medium Total           5                  19                     171*
> 
> I want the calculated field QtyXCost to calculate using the MAX of Qty.
> 
> *At the total lines, I want 300 to be 150, 171 should be 95.
> 
> This is a simple example. In reality, I have more row fields and therefore
> many more nested levels of grouped totals.
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
6/5/2004 6:01:23 PM
I'm not seeing how that will work. Max must be used at multiple rowfields.
How would your approach work?



"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
news:40C20A73.8010705@contexturesXSPAM.com...
> The subtotals and total for a calculated field will always be a sum.
> Perhaps you could calculate the QtyXCost outside of the pivot table.
>
> Tim Zych wrote:
> > How do I get a calculated field to calculate using the MAX of another
field?
> >
> > Say I have the following:
> >
> > SIZE     COLOR     Max of Qty    Sum of Cost     Sum of QtyXCost
> > large      blue            8                    5
40
> >              green          2                    4
8
> >              red             10                  6
60
> > large Total               10                 15                     300*
> > medium green           4                 11                      44
> >              red              5                   8
40
> > medium Total           5                  19                     171*
> >
> > I want the calculated field QtyXCost to calculate using the MAX of Qty.
> >
> > *At the total lines, I want 300 to be 150, 171 should be 95.
> >
> > This is a simple example. In reality, I have more row fields and
therefore
> > many more nested levels of grouped totals.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>


0
tzych5645 (4)
6/5/2004 7:35:17 PM
Using your sample pivot table, if the 8 is in cell C5, and 5 is in cell 
D5, then in cell F5, enter the formula:  =C5*D5

Copy down to the end of the pivot table, and it would multiply each 
MaxUnits by SumQty.


Tim Zych wrote:
> I'm not seeing how that will work. Max must be used at multiple rowfields.
> How would your approach work?
> 
> 
> 
> "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
> news:40C20A73.8010705@contexturesXSPAM.com...
> 
>>The subtotals and total for a calculated field will always be a sum.
>>Perhaps you could calculate the QtyXCost outside of the pivot table.
>>
>>Tim Zych wrote:
>>
>>>How do I get a calculated field to calculate using the MAX of another
>>
> field?
> 
>>>Say I have the following:
>>>
>>>SIZE     COLOR     Max of Qty    Sum of Cost     Sum of QtyXCost
>>>large      blue            8                    5
>>
> 40
> 
>>>             green          2                    4
>>
> 8
> 
>>>             red             10                  6
>>
> 60
> 
>>>large Total               10                 15                     300*
>>>medium green           4                 11                      44
>>>             red              5                   8
>>
> 40
> 
>>>medium Total           5                  19                     171*
>>>
>>>I want the calculated field QtyXCost to calculate using the MAX of Qty.
>>>
>>>*At the total lines, I want 300 to be 150, 171 should be 95.
>>>
>>>This is a simple example. In reality, I have more row fields and
>>
> therefore
> 
>>>many more nested levels of grouped totals.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
> 
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
6/5/2004 8:09:50 PM
thanks for replying. I was hoping for a built in method.

"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
news:40C2288E.50209@contexturesXSPAM.com...
> Using your sample pivot table, if the 8 is in cell C5, and 5 is in cell
> D5, then in cell F5, enter the formula:  =C5*D5
>
> Copy down to the end of the pivot table, and it would multiply each
> MaxUnits by SumQty.
>
>
> Tim Zych wrote:
> > I'm not seeing how that will work. Max must be used at multiple
rowfields.
> > How would your approach work?
> >
> >
> >
> > "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
> > news:40C20A73.8010705@contexturesXSPAM.com...
> >
> >>The subtotals and total for a calculated field will always be a sum.
> >>Perhaps you could calculate the QtyXCost outside of the pivot table.
> >>
> >>Tim Zych wrote:
> >>
> >>>How do I get a calculated field to calculate using the MAX of another
> >>
> > field?
> >
> >>>Say I have the following:
> >>>
> >>>SIZE     COLOR     Max of Qty    Sum of Cost     Sum of QtyXCost
> >>>large      blue            8                    5
> >>
> > 40
> >
> >>>             green          2                    4
> >>
> > 8
> >
> >>>             red             10                  6
> >>
> > 60
> >
> >>>large Total               10                 15
300*
> >>>medium green           4                 11                      44
> >>>             red              5                   8
> >>
> > 40
> >
> >>>medium Total           5                  19                     171*
> >>>
> >>>I want the calculated field QtyXCost to calculate using the MAX of Qty.
> >>>
> >>>*At the total lines, I want 300 to be 150, 171 should be 95.
> >>>
> >>>This is a simple example. In reality, I have more row fields and
> >>
> > therefore
> >
> >>>many more nested levels of grouped totals.
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>
> >>
> >>--
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >
> >
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>


0
tzych5645 (4)
6/5/2004 11:58:14 PM
Reply:

Similar Artilces:

PA Misc. Log Tables
Hi, How can I find the difference between Billed and Un-billed Miscelleneous Logs? Where Can I see Un-billed Misc. Logs and which tables. Hari I have a spreadsheet with the PA tables listed in it. Send an email message to charles.zarzour@intergraph.com and I will send you the spreadsheet. I do an excel spreadsheet for each Dictionary. Makes it easy to lookup tables. "Hari" wrote: > Hi, > How can I find the difference between Billed and Un-billed Miscelleneous > Logs? > Where Can I see Un-billed Misc. Logs and which tables. > > Hari > > Can I ...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

forward to: field
Hi Everyone, im looking for the AD attribute in which the 'forward to:' field of the delivery options is stored. Thank you in advance kind regards marc -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ Its in the altRecipient attribute. -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Marc Wenger" <haga(at).iesg--nspm-noa9h.gmx.ch> wrote in message news:opr64hs7z6fyi4rt@news.microsoft.com... > Hi Everyone, > > im looking for the AD attribute in which the 'forward to:' field of ...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

custom field for projects with tasks past due
I am wondering how/what formula would be needed for creating a custom field at the project level to show when a project has at least one task that is past due. I have a custom field at the task level (IIf([Finish]<[Current Date] And [% Complete]<100,1,2)) that will show when an individual task is overdue, but I want to add a custom field to the Project Center > Summary view to show when a project has any overdue tasks. The PM can then drill down from there into the project and see exactly what tasks are overdue. Once I figure out the formula for this I want to assign ...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Getting the BCC field by default.
I would like the BCC field to show by default but cannot find the setting for this in 2007. Anyone know where this is located? In a new message window, use the Options chunk to display the "Show BCC." --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, JC HARRIS asked: | I would like the BCC field to show by default but cannot find the | setting for this in 2007. Anyone know where this is located? Thanks Milly. I had...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Date Calculation
Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I w...

COUNTIF on Summary Fields
Can't use on a cell that "sums" with arithmetic operators "+". Is there a workaround, etc.? a bit more explanation? -- Don Guillett SalesAid Software donaldb@281.com "DLC" <dlcopesr@yahoo.com> wrote in message news:117v954kaf4s2f6@corp.supernews.com... > Can't use on a cell that "sums" with arithmetic operators "+". Is there a > workaround, etc.? > > ...

Calculating totals
I have two columns on a worksheet: "Project Codes" and "Totals." I would like to have columns on another worksheet that will automatically total up the different project numbers "A,B,C,etc." How do I do that? Thanks in advance for your help, Technically Handicapped Enter a *unique* list of your "Project Codes", starting in A2 of Sheet2. In B2, enter this formula: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B) And copy down as needed. -- HTH, RD ===================================================== Please keep all correspondence within the G...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Help about numeric type field. Thanks.
I created a SQL Server 2005 CE DB ( .sdf, version 3.0) with vs2005. And I created a table which has 2 fields: fld1 - int, fld2 - numeric(38,25). But I encounted an error messagebox when I tried to insert a record (4005,9000000). The msgbox said Conversion overflows. The setting for my numeric fld2 is (precision=38,scale=25). So why occur error when to insert 9000000? Thanks in advance. ...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

total group & max function
I am using Access 2003 I created a Db for Machine Mtce. The table consists of 3 tables, (1) Basic Mach Infor (ie Mach#, Mach Name, Mfg etc) - (2) Operations Table - consists of different prev mtce (PM) that need to be completed at various time frames. Ex: "Mach 100" could have 3 different operations; Oper # 100A to change the oil due every 90 days, Oper # 100B to check the belts due every 180 days, Oper # 100C to grease the machine due every 365 days. The 3rd table is the "PM History" table which includes the history of all of the PM's completed with Work Order...

item class table
I am creating SOP IM import. I need to fill the distribution fields with a rev account that is part of the item class. I would like to find a table that would hold the item class accounts. I looked in IV40400 and did not see any distribution accounts. What is the best table to pull these accounts. If the accounts have been defined on the Item Class, they will appear on the records in the IV40400 table. They're in the fields IVIVINDX, IVIVOFIX, etc - and they're just the keys to the actual account definitions in the GL00100 table. If a particular account type isn't defined ...

Adding blank "separator" Rows in a Pivot Table
Does anyone know how to insert blank rows into a pivot table (say, between groupings, etc.)? There has to be a way, since some of the various table formats that come with Excel include blank rows. Double-click the field button that you want to format Click Layout Add a checkmark to Insert blank line after each item Click OK, twice Bucko wrote: > Does anyone know how to insert blank rows into a pivot table (say, > between groupings, etc.)? There has to be a way, since some of the > various table formats that come with Excel include blank rows. -- Debra Dalgleish Contextures http...

Accessing another user's calendar OL2000/OL2003/Ex2000
Here's the situation: Running Exchange2000, v 6.0, sp4. Users are on XP/OL2003 or NT/OL2000. (we're in the process of upgrading all of the workstations to XP.) VIP user is on XP/OL2003. He can access his calendar just fine on his workstation. His secretary and other office admin type people are still on NT/OL2000. They have reviewer or editor rights to his calendar. They try to access his calendar by chosing File/Open/Other User's Folder... and put in his name and calendar. When they do this, the process hangs. In Task Manager, Outlook is "Running." End the Outloo...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

WLM transfer to another computer
Hi, I finally moved from Windows 7 RTM to Win7 Pro 64. I did it by installing the new OS on a brand new hard drive, then installed my old hard drive in a 2.5" external enclosure. I've been successful in moving most of my files and settings over, but WLM is the exception. Can someone help answer these questions for me: 1. Where are the actual mail files stored? 2. Where is the account login info stored? 3. In Outlook and OE installing on a new computer, even after moving files, prompted for a full redownload off of the POP server. Anyway to avoid this? Is ther...