#### Finding Min In Calculated Pivot Table Formula

```I didn't have much luck on another list, so I thought I'd try this one.
Any thoughts on the below would be appreciated.

I have a pivot table with a calculated field for which the equation is
[Sum of Dollars / Count of Instances].  So in turn I'm rendering the
average cost for a list of items in a group.  The table is set up such
that each column contains a week number and the rows contain a list of
items within a grouping.  For example, I might be listing average cost
of apples, oranges, and peaches for each week under a grouping called
fruit.  The next grouping is bread, where I'd display the average cost
by week for wheat, Italian, and rye.

My first issue:  I'd like to be able to find the *range* within a
category.  So for week one, what is the max cost of a piece of fruit
(regardless of type) minus the min cost of fruit?

My second issue: When calculating the range, I'd like to exclude any
zero values.  Is this possible?

I suspect that I'll need to build a helper sheet to import data from
the pivot table, strip out any zero values, and perform the min & max
calculations, but if there's a way to perform this *within* the pivot
table I'd obviously prefer it.

If anyone knows this to be possible I'd love to hear it.

Jim

```
 0
8/29/2006 6:47:48 PM
excel 39879 articles. 2 followers.

0 Replies
542 Views

Similar Articles

[PageSpeed] 3

Similar Artilces:

finding process wait time
Hi, I have a VC++ program having two threads, out of which thread 1 is cpu bound and thread 2 is IO bound. There are two cases for the process case 1) where both thread needs almost same execution time (say t1) case 2) Where thread 1 finishes very early then thread 2. I have a 2 core machine with win XP OS (while doing test I closed all other exe's) Ideally in case 2 the time to finish the process (process end time - start time) should be almost equal to /better then case 1. But its not, and the process runs 10% slower in csae 2. I suspect that since there are no ready to run thread i...

Poweruser can't open SmartList Builder calculations
We are running GP10 - SP1 and I am set up as Poweruser. However, when I click on the Calculations button on the SmartList Builder window, I receive the message, "You do not have security privileges. Contact your system administrator for assistance." This doesn't help because I am the system administrator. The SmartList Builder manual says, "Users in the POWERUSER role have access to all SmartList Builder operations." If, however, I log on as "sa" I can access Calculations. Any suggestions as to what I need to do to get a POWERUSER to have access to Sma...

Sum Formula
Dear All, I am looking for a possibility to calculate the following function: Sum (1 to n) of 55-1,23^n e.g.: for n = 3: 55-1,23^1 + 55-1,23^2 + 55-1,23^3 = 160,39 Is it possible to 'program' such a formula in Excel and may anyone help me? Thank you and all Best Danny Danisch =SUM(55-1,23^ROW(A1:A3)) array entered (Ctrl-Shift-Enter) Jerry Danny Danisch wrote: > Dear All, > > I am looking for a possibility to calculate the following > function: > > Sum (1 to n) of 55-1,23^n > > e.g.: for n = 3: > > 55-1,23^1 + 55-1,23^2 + 55-1,23^3 = 16...

Formula #6
Can you help me with a formula for the following: Regarding Stocks: High Low Avg Shares Value I have to get the average and value amount knowing the high, low & shares. Thank you. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ One way: Assuming High in column A, Low in column B, etc, and assuming the value should be taken from the Average: C2: =AVERAGE(A2:B2) E2: =C2*D2 In article <Fran.udbtn@excelforum-nospam.com>,...

Find first and last cells in range, return info beside
Hi All, I hope someone can help with a tricky formula. Given this type of sample data: A B C D E F G 1 Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10 Jul-10 2 x x x 3 x x x x 4 x x 5 x x x 6 7 Looking at row 2 - In cell A6, find the first (left most) x and return the month-year in the row above it. This should give Apr-10. In cell A7, find the last (ri...

Pivot Chart Formatting
Using Excel 2002 (SP2), whenever I change a pivot field to update a pivot chart, the formatting on the chart reverts to its default setting regardless of what I change it to. Has anyone else had this problem? I will answer my own question...it appears that this could be a bug in excel. One way to get around it is to create a macro that applies all of the formatting. If you want this macro to be driven automatically, call the macro from the worksheet's change handler (the one that contains the pivot table). This way, whenever the worksheet changes, which is whenever the pivot ...

form with a table in it?
Hello I am having a hard time trying to design a form like the one on the Issues Template. when I create a split form and try to delete the text boxes to leave just the table, it also deletes the fields on the table. Help please. Instead of using split form view, use datasheet view. -- Daryl S "help!!@\$!!" wrote: > Hello I am having a hard time trying to design a form like the one on the > Issues Template. when I create a split form and try to delete the text boxes > to leave just the table, it also deletes the fields on the table. Help please. ...

Finding Macros
Tried posting this earlier but.. I have a workbook taht comtains macros/vba code. Worlks great on my pc but not pn othre peoples PC's. Macro security is set to moderate. How can I get the macros/code to work on other pc's? Option Explicit Sub Main() Call RawFilter Call GetScenarioTurns Call FormatWorkingData Call ProcessData End Sub Sub RawFilter() Sheets("Working Data").Select Cells.Select Selection.Delete Shift:=xlUp Range("A1").Select Sheets("Raw data").Cells.AdvancedFilter Action:=xlFilterCopy, CriteriaRange...

How to find missing indexes on GP tables
We've been having some performance issues on customer lookups. Turns out that 7 of the standard indexes for RM00101 were missing, including #2 which indexes by customer name. I don't know when or how these disappeared but I'm now concerned that other standard indexes may be missing from other tables. Is there any automated way to check this and/or to recreate the missing indexes? We're on GP9, SP2 using SQL2000. -- Jim@TurboChef you might be able to recreate these indexes by running the following command in Query Analyzer. DBCC DBREINDEX RM00101 -- Richard L. Wha...

Time calculation question
I am using a spreadsheet to calculate the total amount of hours worked. The time is recorded in my cells as military time. The total time is then figured in the cell labeled "total hours". example: work begin | lunch start | lunch finish | work end | total hours | excess time 0715 _______ 1130 ______ 1200 ______ 1600 ______ 0800 _____ ???? I need the total hours not to exceed 0800. If total hours exceeds 0800 then enter 0800 in total hours cell and place excess time (overtime) in a seperate cell labeled excess time. Also, is there a way to enter ...

Pulling data from remote pivot table
I have had some problems pulling data from a pivot table that resides in another file. It seems that the data file must be open for the pulling document to display the updated values. If I update and the source file is not open, I get a #REF error. Anyone seen this? What are my options here? I don't want to have to open the source file every time I need to update. Thanks! -Chris ...

how do I get a function formula cell to format as a number?
I put this formula "=IF(OR(\$C\$9="Check",\$C\$9="Decrease Adjustment"),\$G\$8,"0")" and the cell formats as a number but when I put this formula "=(IF(\$C\$8="Enter Transaction",IF(OR(\$C\$9="Withdrawal",\$C\$9="Increase Adjustment"),\$G\$8,""),""))&(IF(\$C\$8="Enter Receipt",IF(OR(\$C\$9="Check",\$C\$9="Decrease Adjustment"),\$G\$8,""),""))" it doesn't. I have gone into the fomatting of the cell and it shows that it should be viewing appropriate...

using subtotals with other data to calculate
I need help!! I'm trying to use the subtotals of one worksheet to paste into another to calculate out costs but when I paste them over, I get all of the cells including the subtotals. I only want the subtotals & to be able to match them to another group of data for calculations. Can anyone help me? Doug To copy just the subtotal amounts: 1. Collapse the Subtotals, and select the range of cells that you want to copy. 2. Choose Edit>Go To, click Special 3. Select Visible cells only, click OK 4. Choose Edit> Copy 5. Select the cell where the paste should start 6. Choos...

Conditional Formatting and Formulas 03-22-10
Hi. I am using Excel 2003 to do some conditional formatting, but there are only three conditions to manipulate and I need four. I think though that I can comine two things together in a formula in one of the conditions - so that it would do the same formatting if either of two conditions were satisified - using the or condition. However, I am not sure how the OR function works or whether you can get round the three conditions issue in conditional formatting by doing this. The two things that I want to combine in an or function are: ERROR.TYPE(I30=7) (I30="") ...

Delete a table using VBA
I have some code that opens a couple of make-table querys, and then executes some more code. I would like to insert the line 'DoCmd.DeleteObject acTable, "Table to Delete"' to delete those tables that are created, but it doesn't work. The tables don't delete. I'm assuming it has something to do with the loop, but I can't figure out where to put the deleteobject code to make it work. Here's the code that I have now: Private Sub btnEmailVolunteers_Click() DoCmd.OpenQuery "Community Project1" DoCmd.Close acQuery, "Community Projec...

Where do I find the "shrink to fit" command?
I'm trying to reduce a calendar and I can't find the "shrink to fit" command in Publisher 2000. You need to have the text box selected, then right click. Choose edit text box (or something similarly phrased. I don't have Pub open right now.) -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "McFromm" <McFromm@discussions.microsoft.com> wrote in message news:C5DB55F4-02E7-4627-968C-C9FAEC07CDF6@microsoft.com... > I'm trying to reduce a calendar and I can't find the "shrin...

Where is pivot table data?
I created several pivot tables months ago, all linked to one master data sheet. Now it's time to update and I can't find the master. The Field List and Filter Pane appear, so they're still linked. Where can I find the address of the master sheet? Right click the pivot and select Wizard. Use the Back button to move to the dialog where the data range was specified. Note that it is possible to delete the source data range or sheet and the pivot table will remain intact. The pivot ultimately has it's own memory known as the pivot cache. -- HTH... Jim Thomlinson...

Re. Find which numbers sum up to given total
Received solution from Herb which worked perfectly. He provided a formula which I will allow him to explain as it is well above my head. Many thanks to all those who helped. Keith ...

table lookup
My head hurts from trying to figure out how to do this. I want to specify a row title in which to find the closest number specified. For example, if my row titles are W,X,Y,Z....maybe I would specify 5.5 in row X. The value returned should be 6. W 3 5 7 X 2 4.5 6 Y 1 1.5 3 Z 0 1 2.5 Thanks, Assuming your data in A1:D4 =MIN(IF((A1:A4="x")*(B1:D4>=5.5),B1:D4)) ctrl+shift+enter, not just enter "engineer" wrote: > My head hurts from trying to figure out how to do this. I want to specify a > row title in which to find the closest n...

How to display = in the formula bar by default
HI, Before in Excel's formula bar, there was an = displayed by default in the formula bar, which is not visible in Office 2003. How can I defult an = (sum) in the formula bar? Thanks, A. Hakim You can't but you can use Tools|Customize to add an equal sign in a toolbar. It is NOT a 'sum' sign. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "A. Hakim" <A. Hakim@discussions.microsoft.com> wrote in message news:C40B87D3-BE60-45BF-BA2D-513E533F7341@microsoft.com... > HI, > > Before in Excel's formula bar, the...

How do I add a Formula Auditing Toolbar in Excel
Which version of Excel? On Sat, 19 Feb 2005 13:41:09 -0800, pinkspyce <pinkspyce@discussions.microsoft.com> wrote: ...

Budget import
I'm attempting to insert budgets directly into the GL00200 and GL00201 tables via SQL. Are there any issues I should be aware of in doing this? Does the API do anything special that a direct table solution would miss? Problems I'm solving by doing this include: - eConnect and especially GP Web Services are brutal to install properly - The manual excel budget import will reject any lines where the account does not exist without reporting which lines failed Thanks in advance for your help, Mark petersen, You can do that. However, I'd suggest to use GL00201 only for this purp...

Count IF Formula
Hi All, I have a problem below. I need to count if the value in column C equal to value in column A then count column B. In this case "AGB" should be "2" and "HHH" should be "3". Column A Column B Column C HHH 10 AGB AGB 10 HHH HHH 10 NNN HHH 10 MMM AGB 10 BBB . . . . ...

help with formula #12
i am setting up a simple spreadsheet I want to have a cell when i put a amount under \$100.00 to in anothe cell substract 10% then if I put a amout over \$100.00 to subtract 20% test l \$95.00 l ? l test l \$125.00 l ? -- jladik ----------------------------------------------------------------------- jladika's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=910 View this thread: http://www.excelforum.com/showthread.php?threadid=27531 Hi =IF(B2<100,B2-(B2*10%),IF(B2>100,B2-(B2*20%),B2)) you didn't say what you wan...

multiple arguments within a formula
I need advice regarding a formula, I'm trying to use multiple arguments for an 'if' statement for a selection of rows. =IF('Operational Mng Practices'!C16=1,"Foundational","Non-Compliant",IF('Operational Mng Practices'!C16=2,"Fundamental","Non-Compliant",IF('Operational Mng Practices'!C16,"Developing","Non-Compliant",IF('Operational Mng Practices'!C16=4,"Mature","Non-Compliant",IF('Operational Mng Practices'!C16=5,"WorldClass","Non-Compliant...