PivotTable Formulas

I have set up a pivot table.  I have been asked to add a column at the
end of the pivot table that calculated the %variance of sales from this
year versus last year.
I tried to create the formula (Calculate Field) after I had grouped my
dates for Months & Years.  I had to ungroup these to be able to create
the formula.

I do not see how when I have ungrouped the date to distinguish between
2003 & 2002 in my Pivot formula.  Is this possible of am I flogging a
dead horse here?
Any suggestions would be most help ful



Regards GarethG



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
9/12/2003 1:29:48 PM
excel 39879 articles. 2 followers. Follow

1 Replies
267 Views

Similar Articles

[PageSpeed] 46

You could add a column to the source table, in which you calculate the 
year for each date. For example, =YEAR(B2)

Add this field to the pivot table, and create a Calculated Item for this 
field, e.g.:  ='2003'/'2002'-1

GarethG wrote:
> I have set up a pivot table.  I have been asked to add a column at the
> end of the pivot table that calculated the %variance of sales from this
> year versus last year.
> I tried to create the formula (Calculate Field) after I had grouped my
> dates for Months & Years.  I had to ungroup these to be able to create
> the formula.
> 
> I do not see how when I have ungrouped the date to distinguish between
> 2003 & 2002 in my Pivot formula.  Is this possible of am I flogging a
> dead horse here?
> Any suggestions would be most help ful

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

0
dsd (439)
9/12/2003 10:05:03 PM
Reply:

Similar Artilces:

IRERR nested formula help please
hi just wondering if someone can help with the following nested ISERR formula. I want to be able to show in J3 that if the value in G3 is "0" then show as "-100%" OR if the value is "NULL" then show as "0" but if the value is >0 then I3/G3*100 it is working to a degree but if value in G3 is NULL then still shows as -100% I want it to show as 0% if NULL and -100% if 0 - hope it makes sense.... currently have following in J3 =IF(ISERR(I3/G3),-100,I3/G3*100) G3 = Order I3 = Profit thanks in advance Depending on how I interpret NULL =IF(ISBLANK(G3)...

Internationalization for formulas & macros
Hi all, I have a relative who send me some Excel spreadsheet (97, 2000 or XP that contains formulas and macros written in english. However, I have a french version of Excel. Because of this, I can' open these spreadsheets. My question is quite simple. Is there a tool or an easy way to convert all formulas & macros withi a spreadsheet from english to french? Any help will be appreciated. Thank' -- micto ----------------------------------------------------------------------- mictou's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1637 View this th...

counta formula
I have the formula =10/counta(E6:E15 ). This gives me 10/10 if E6 through E15 are filled. How do I make this formula: =10/counta( E6:E15 but exclude the cells from this range that equal any of the numbers in B5:B8) Example: =10/counta(E6:E15) would be 10/10 if E6:E15 are filled but if E12 = B5, excel would give me 10/9 because E12 would be excluded This is an array formula, confirm it by pressing CTRL-SHIFT-ENTER: =10/COUNTIF(E6:E15, "<>" & B5:B8) Does that help? -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScien...

Creating a Formula to Format Column automatically? #4
Does it matter what order the conditions are in? Some of them worked some didn't -- bludovic ----------------------------------------------------------------------- bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480 View this thread: http://www.excelforum.com/showthread.php?threadid=26434 If you have 3 conditions, the order does not matter. What matters is the content of the cell. Not case-sensitive, but do you have any extra spaces if text? If looking for numbers, are they real numbers or text that looks like numbers? You used Joe Blow and...

Subtotal
OK, so I have a list of orders. Some orders have just one row of data, some have two rows of data, some have three, some have four, etc. Each row represents either a delay or an activity. (So, obviously, an order can have more than one of either.) I need to aggregate into one row: Order | Type of Order | Num. of Delays | Total Length of Delays HERE'S THE MANUAL WAY I'M DOING THIS NOW: ====================================== My initial columns are: Order Number (A) | Type of Order (B) | Action Code (C) | Action Length (D) I added a "marker column" - Dela...

formula fill not working correctly in 2007
If I use the fill handle to copy a formula, the formulas are well copied, but the results are not. Exemple: The formula in A3 is =A1+A2. If I copy the formula to B3 it reads =B1+B2, which is correct. The result given in B3 however is not B1+B2 but the same result as in A3. I have to recalculate the formule in B3 for the correct result. In previous versions Excel did this automatically. How can I fix this problem or is it a bug? Hi It sounds as though calculation is set to Manual. Tools>Options>Calculation>and select Automatic -- Regards Roger Govier "Jante" <J...

Formula to sum values extracted from string
Hi all I'll preface this by pointing out that I do NOT want to use text to columns, nor a VBA solution. I'm looking for a formula alternative - maybe an array formula. I have a single column of data extracted from a string, which represents numeric values, single space-separated. The data looks like this: 8.56 2,514.12 3.18 0.35 What I'd ideally like is a formula that sums each of the four values, which in the above example would be 2,526.21. Any suggestions appreciated. >8.56 2,514.12 3.18 0.35 Assume that string is in cell A1. Create this named...

Excel Formula depending on number in cell..
Hello everyone! I have a question that I thought some of you could answer: I have a number in column D and based on if it is $0-$100 I want i multiplied by 0.10 and then rounded up to .95. So, if it is 20.49 + 10% = $22.54 and then rounded up to $22.95 Now this is where it gets tricky.. If it is between 100.01 to 200.00 + 7% and then rounded up to a .95. So for example 129.44 + 7% = $9.08 = $138.50 then rounded up to .95 $138.95. I need to do this for a range of numbers like 200.00 to 300.00 an 300.00 to 500.00 etc, etc.. Could someone point me in the right direction? I appreciate a...

formula within same cell
I don't have to much experience in Excel but I need to find out how to run a formula in a specific cell, for example if I enter 100.00 in A1 then come back and want to enter 250.00 then press enter can I get it to answer 350.00? Mike To show 350 as the answer, type the following, followed by enter =100+250 If you will be doing a lot of this, you might consider entering 100 in cell A1, 250 in cell A2 and =SUM(A1:A3) in cell A4 (leaving cell A3 for an additional value). Stephen http://groups.msn.com/HighlandsVillage where Villagers help each other "Mike" wrote: > I don...

formula bar #7
I'm calculating simple percentages. I enter 5/10*100 and 50 appears in the cell. When I go back to that cell, what shows up in the formula bar is ..5*100. This makes it impossible for me to check to make sure I entered the correct numbers to begin with. Is there a way to make it keep the original formula in the formula bar when you return to the cell? Peter, If the cell is formatted for Percentage then enter =5/10 (equals fiv divide by 10) to produce 50% in the cell and =5/10 in the formula bar. The formula you are using suggests the cell is formatted to Number, bu if you enter =5/...

Formulas with a variable # of worksheets
I am building a workbook that is intended to capture information on new feature requests for a system. The workbook will then consolidate information from each new feature request and perform different kinds of analysis to facilitate portfolio management (ranking views of each feature to make a determination as to which features will be funded for the next release). Analysis (and input for each feature) will include things like value to the customer, value to the company, development cost, marketing risk, development risk, deployment/support risk, company strategy supported, etc. I think I ...

formula in a pivot table?
Hello guys, is that true that excel-pivot is only able to sum (meanvalue is no selectable) from a formula made table. usually you put by drag and dro the data field into your table. but you also can put by hand a formula which is quit useful sometimes. after lunching the data-field in your pivot you can say pleasy show me the minimum or the meanvalue. It looks like that is not possible when you create a data-field using formula. then excel is just able to sum that ****... do you have any other idea?? cheers, filipu -- filipu ------------------------------------------------------------...

How do I correct formula display errors?
I use Word 2003, and recently formulas created by my professor do not display properly on my laptop. For instance, the ampersand symbol seems to appear where some superscripting or ' characters should be. What is the problem and solution? Hello Brian brian wrote: > I use Word 2003, and recently formulas created by my professor do not display > properly on my laptop. For instance, the ampersand symbol seems to appear > where some superscripting or ' characters should be. What is the problem and > solution? what version of Word has your professor been...

IF formula & COLOURED CELL
I would like to use a formula if A1 (e.g.$15) is filled in yellow then A1*2%, if in green then A1*1%.....Is there any way to do that. TIA. -- littleps ------------------------------------------------------------------------ littleps's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25482 View this thread: http://www.excelforum.com/showthread.php?threadid=391785 Hi, Check out this site: 'OzGrid' (http://www.ozgrid.com/VBA/ReturnCellColor.htm) Regards, -- aristotl ----------------------------------------------------------------------- aristotle...

Array Formulae Problem
I have the following setup A B C 1 Job Priority Satus 2 1 1 COMPLETED 3 2 1 CANCELLED 4 3 2 CANCELLED 5 4 3 WIP 6 5 1 WIP 7 6 4 WIP 8 7 1 COMPLETED I am trying to calculate the number of priority 1 jobs that have been completed and cancelled (the answer should be 4). I have tried looking at Array formulas, but I have not been able to figure them out. I would be grateful if anyone could offer any pointers. Best regards Neil =SUMPRODUCT((B2:B100=1)*(C2:C100={"Completed","Cancelled"})) I make it 4 -- HTH RP (remove nothere from the email address if mailing direct...

Changing a Parameter Of A Formula
Hi, I would be grateful if someone could help me with the problem have got. I have written a macro, with my user defined functions. One of my user defined functions is called GetData and it has two parameters Reference and Last_Value, eg GetData(Reference, Last_Value). I call GetData by assigning this to a cell like so (which works fine): Cell.Formula = GetData(Reference, Last_Value) Now within the GetData function I use the Reference Parameter to get a New Value, which will be returned to the cell (which works fine too). But what I wanted to know is how can I change the Last_Value ...

counta formula
I want to divide a number by counta for certain cells (which would yield a number). For example, =10/counta(A1:A4) would equal 10/4 if cells A1 to A4 are filled. What I am stuck on is this: =10/counta(A1:A4 but exclude the cells from this range that equal any of the numbers in in the range B5:B8) Example: =10/counta(A1:A4) would be 10/4 but since A2 = B7, the formula gives me 10/3 because A2 is excluded from counta What should happen if all the numbers match? Try something like this: =10/SUMPRODUCT(--(ISNA(MATCH(A1:A4,B5:B8,0))),A1:A4) -- Biff Microsoft Exce...

convert text to formula using VLookup
My question; I've seen somewhere that you can convert a text string into a formula using the INDIRECT Function. What I am constructing is a maintenance Schedule where some items need to be serviced on a weekly basis, others monthly, quarterly, bi-annually, and yearly. (Those are the only five rotations I require at the moment.) Once constructed the format will take on the appearance of a calendar with conditional formatting to highlight the dates the service falls on. Each of the rotations require (I think) different formulas, so what I want to do is have a list of the f...

Help with Formula Scenarios from yesterday
Repost with responses Hi AlfD Well, here's the problem, all of the cells which are being averaged contain formulas which until other cells are filled weekly, will contain zeros. So, I wrote those to ignore the zeros. I've instructed the person who will be populating the weekly, currently empty cells to use "n/a" or "---" to show that the cell has been addressed but will not contain a value, so I'm in a quandry. I think it's best to ignore the zeros, but what's making my averages come up wrong. So, I think leave that condition alone. What I need, ...

Formula which includes new sheets too?
Hi all, I don't know if this is possible, but any suggestions would be much appreciated! I have a spreadsheet with a few sheets, 1 for each site and then a Summary sheet which displays totals. eg. Site 1 shows how much has been fee'd for that site in each month, and the Summary Sheet shows how much has been fee'd for all sites in that month, by using a formula which takes the value's from each sheet. Is there a way of editing the formula so it will automatically include cell H3 for any sheet, including any new sheets added, without me having to amend the formulas? T...

Reference a cell value in a formula
I have the following formula: =SUMPRODUCT((MONTH(Summary!$F$7:$F$15000)=1)*1) I want the "15000" to be replaced by the varaible value of a cell in an other worksheet ("Summary"). I tried using INDIRECT but it didn't work. Thanks in advance =SUMPRODUCT((MONTH(OFFSET(Summary!$E$7,0,0,C4,1))=1)*1) C4 in this case is the cell that holds the number of items to be used in the calculation -- If the post is helpful, please consider donating something to an animal charity on my behalf ..... and click Yes "Leon" wrote: > I have the f...

cell variables in IF formula
i have this formula (Thanks Biff) =IF(J13="","",IF(J13=0,"Stock not rated",IF(OR(J13={1,2}),"Sell",""))) now i want to add in further variable - if J13 has "cmt" or "divs" then the cell is blank, then if any of the above... thank you =IF(OR(J13={"","cmt","divs"}),"",IF(J13=0,"Stock not rated",IF(OR(J13={1,2}),"Sell",""))) -- Return email address is not as DEEP as it appears "Micayla Bergen" <MicaylaBergen@discussions.microsoft.com> wro...

Formula Help #20
Need to to calculate the following Cell A1 had a figure calculated .....example 174 I then need a formula below in B1 to calc multiples of 97 and retur the figure that is greater than 174 i.e 174 in A1 194 in B1 (97*2) 20 in C1 (difference -- Message posted from http://www.ExcelForum.com B1 =97*2 C1 =B1-A1 -- Regards, Peo Sjoblom "sosteffo >" <<sosteffo.16i5o0@excelforum-nospam.com> wrote in message news:sosteffo.16i5o0@excelforum-nospam.com... > Need to to calculate the following > > Cell A1 had a figure calculated .....example 174 > > I t...

calculation formulas
Since today I am facing some peculiar change in my excel files. When I drag a formula from one cell to another cell, result of the first cell itself are reflected in the subsequent cells. Then I have to press F9 key so that all cells give proper calculation of the formulas. E.g Product Price Qty Value A 2 10 20 B 5 15 20 C 10 20 20 D 4 2 20 E 6 5 20 I did the above table in exel. Cell D2 was the result of multiplication of B2 & C2. Thats is correct. The I dragged the formula from D2 to D6. However in this res...

Formula Bar font color
Hi All! I don't know how I did this, but the information in the formula bar is showing up in white font... Please help! ... I want to change it back to black... TIA! LavaDude Try this (Think it's a windows setting) Right-click on any empty space in the desktop > choose Properties Select Appearance tab > Advanced button (in Win XP) In the Advanced Appearance dialog: Click on "Item" dropdown menu > choose Message Box Under "Color" for Font (in the line *below* "Item") Choose "Black" color > OK > Apply (and don't do i...