Calculations in Pivot Tables

I've looked in most of the excel groups on google, but have not found
a direct answer to my question, so if this is a repeat, please guide
me to the correct post.

my problem:
my table has 4 columns, dept, costs, amount and Actual/budget

in the pivot table the costs are the rows, actua/budget are the
columns and the amount is the data

I now want to have a variance column that calculates the variance
between actual and budget. I tried the formulas in the pivot table and
it does not let me add a calculated field that does this simple (a-b)
type of calculation. Is there an easy way to do this using pivot table
or if there is another way to do this all suggestions are welcome.

Also I plan to add Dept as page header so that people can select their
dept and see the actuals and budgets. So my formula should be able to
account for this.

Thanks for all your help!

Sue
0
sugandha (4)
8/19/2004 4:34:30 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
684 Views

Similar Articles

[PageSpeed] 35

You should be able to create a Calculated item:

Select the Actual/Budget field button
 From the PivotTable menu, choose PivotTable>Formulas>Calculated Item
Type a name for the field, e.g. Variance
Enter the formula  =Actual-Budget
Click OK


Su wrote:
> I've looked in most of the excel groups on google, but have not found
> a direct answer to my question, so if this is a repeat, please guide
> me to the correct post.
> 
> my problem:
> my table has 4 columns, dept, costs, amount and Actual/budget
> 
> in the pivot table the costs are the rows, actua/budget are the
> columns and the amount is the data
> 
> I now want to have a variance column that calculates the variance
> between actual and budget. I tried the formulas in the pivot table and
> it does not let me add a calculated field that does this simple (a-b)
> type of calculation. Is there an easy way to do this using pivot table
> or if there is another way to do this all suggestions are welcome.
> 
> Also I plan to add Dept as page header so that people can select their
> dept and see the actuals and budgets. So my formula should be able to
> account for this.
> 
> Thanks for all your help!
> 
> Sue


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

0
dsd1 (5911)
8/19/2004 4:49:04 PM
I tried that option before posting here and what it does is give me a
list of all costs even though the costs dont exist for that dept with
a 0 value. I would then need a way to hide the 0 values.


Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<4124DA00.9090306@contexturesXSPAM.com>...
> You should be able to create a Calculated item:
> 
> Select the Actual/Budget field button
>  From the PivotTable menu, choose PivotTable>Formulas>Calculated Item
> Type a name for the field, e.g. Variance
> Enter the formula  =Actual-Budget
> Click OK
> 
> 
> Su wrote:
> > I've looked in most of the excel groups on google, but have not found
> > a direct answer to my question, so if this is a repeat, please guide
> > me to the correct post.
> > 
> > my problem:
> > my table has 4 columns, dept, costs, amount and Actual/budget
> > 
> > in the pivot table the costs are the rows, actua/budget are the
> > columns and the amount is the data
> > 
> > I now want to have a variance column that calculates the variance
> > between actual and budget. I tried the formulas in the pivot table and
> > it does not let me add a calculated field that does this simple (a-b)
> > type of calculation. Is there an easy way to do this using pivot table
> > or if there is another way to do this all suggestions are welcome.
> > 
> > Also I plan to add Dept as page header so that people can select their
> > dept and see the actuals and budgets. So my formula should be able to
> > account for this.
> > 
> > Thanks for all your help!
> > 
> > Sue
0
sugandha (4)
8/20/2004 5:58:06 PM
If you have Excel 2002, or later version, you can use a macro similar to
the following, to hide calculated items that are zero:

'=============================
Sub HideZeroCalcItems()
'hide rows that contain zeros for calculated items
'by Debra Dalgleish
Dim r As Integer
Dim i As Integer
Dim pt As PivotTable
Dim pf1 As PivotField
Dim pf2 As PivotField
Dim df As PivotField
Dim pi As PivotItem
Dim pi2 As PivotItem
Dim pd As Range
Dim str As String
Set pt = Sheets("Pivot").PivotTables(1)
Set df = pt.PivotFields("Units") 'data field
Set pf1 = pt.PivotFields("Item") 'column field
Set pf2 = pt.PivotFields("Rep") 'row field
Set pi = pf1.PivotItems("YearVar") 'calculated item

For Each pi2 In pf2.PivotItems
    pi2.Visible = True
Next pi2

i = pf2.PivotItems.Count
For r = i To 1 Step -1
    On Error Resume Next
    str = Cells(r + 5, 1).Value
    Set pd = pt.GetPivotData(df.Value, pf1.Value, _
      pi.Value, pf2.Value, str)
    If pd.Value = 0 Then
      pf2.PivotItems(str).Visible = False
    End If
Next r

End Sub
'==============================

For earlier versions, you could use the following code to hide the rows:
'==========================
Sub HideZeroRows()
'hide worksheet rows that contain all zeros
'by John Green
Dim rRow As Range

For Each rRow In ActiveSheet _
    .PivotTables(1).DataBodyRange.Rows
    If Application.Sum(rRow) = 0 Then
      rRow.EntireRow.Hidden = True
    Else
      'DD --I added this to unhide
      'any previously hidden rows
      rRow.EntireRow.Hidden = False
    End If
Next rRow
End Sub
'=========================

Su wrote:
> I tried that option before posting here and what it does is give me a
> list of all costs even though the costs dont exist for that dept with
> a 0 value. I would then need a way to hide the 0 values.
> 
> 
> Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<4124DA00.9090306@contexturesXSPAM.com>...
> 
>>You should be able to create a Calculated item:
>>
>>Select the Actual/Budget field button
>> From the PivotTable menu, choose PivotTable>Formulas>Calculated Item
>>Type a name for the field, e.g. Variance
>>Enter the formula  =Actual-Budget
>>Click OK
>>
>>
>>Su wrote:
>>
>>>I've looked in most of the excel groups on google, but have not found
>>>a direct answer to my question, so if this is a repeat, please guide
>>>me to the correct post.
>>>
>>>my problem:
>>>my table has 4 columns, dept, costs, amount and Actual/budget
>>>
>>>in the pivot table the costs are the rows, actua/budget are the
>>>columns and the amount is the data
>>>
>>>I now want to have a variance column that calculates the variance
>>>between actual and budget. I tried the formulas in the pivot table and
>>>it does not let me add a calculated field that does this simple (a-b)
>>>type of calculation. Is there an easy way to do this using pivot table
>>>or if there is another way to do this all suggestions are welcome.
>>>
>>>Also I plan to add Dept as page header so that people can select their
>>>dept and see the actuals and budgets. So my formula should be able to
>>>account for this.
>>>
>>>Thanks for all your help!
>>>
>>>Sue
>>


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

0
dsd1 (5911)
8/20/2004 6:46:12 PM
Thanks for your help! That works great!!!


Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<412646F4.6090201@contexturesXSPAM.com>...
> If you have Excel 2002, or later version, you can use a macro similar to
> the following, to hide calculated items that are zero:
> 
> '=============================
> Sub HideZeroCalcItems()
> 'hide rows that contain zeros for calculated items
> 'by Debra Dalgleish
> Dim r As Integer
> Dim i As Integer
> Dim pt As PivotTable
> Dim pf1 As PivotField
> Dim pf2 As PivotField
> Dim df As PivotField
> Dim pi As PivotItem
> Dim pi2 As PivotItem
> Dim pd As Range
> Dim str As String
> Set pt = Sheets("Pivot").PivotTables(1)
> Set df = pt.PivotFields("Units") 'data field
> Set pf1 = pt.PivotFields("Item") 'column field
> Set pf2 = pt.PivotFields("Rep") 'row field
> Set pi = pf1.PivotItems("YearVar") 'calculated item
> 
> For Each pi2 In pf2.PivotItems
>     pi2.Visible = True
> Next pi2
> 
> i = pf2.PivotItems.Count
> For r = i To 1 Step -1
>     On Error Resume Next
>     str = Cells(r + 5, 1).Value
>     Set pd = pt.GetPivotData(df.Value, pf1.Value, _
>       pi.Value, pf2.Value, str)
>     If pd.Value = 0 Then
>       pf2.PivotItems(str).Visible = False
>     End If
> Next r
> 
> End Sub
> '==============================
> 
> For earlier versions, you could use the following code to hide the rows:
> '==========================
> Sub HideZeroRows()
> 'hide worksheet rows that contain all zeros
> 'by John Green
> Dim rRow As Range
> 
> For Each rRow In ActiveSheet _
>     .PivotTables(1).DataBodyRange.Rows
>     If Application.Sum(rRow) = 0 Then
>       rRow.EntireRow.Hidden = True
>     Else
>       'DD --I added this to unhide
>       'any previously hidden rows
>       rRow.EntireRow.Hidden = False
>     End If
> Next rRow
> End Sub
> '=========================
> 
> Su wrote:
> > I tried that option before posting here and what it does is give me a
> > list of all costs even though the costs dont exist for that dept with
> > a 0 value. I would then need a way to hide the 0 values.
> > 
> > 
> > Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<4124DA00.9090306@contexturesXSPAM.com>...
> > 
> >>You should be able to create a Calculated item:
> >>
> >>Select the Actual/Budget field button
> >> From the PivotTable menu, choose PivotTable>Formulas>Calculated Item
> >>Type a name for the field, e.g. Variance
> >>Enter the formula  =Actual-Budget
> >>Click OK
> >>
> >>
> >>Su wrote:
> >>
> >>>I've looked in most of the excel groups on google, but have not found
> >>>a direct answer to my question, so if this is a repeat, please guide
> >>>me to the correct post.
> >>>
> >>>my problem:
> >>>my table has 4 columns, dept, costs, amount and Actual/budget
> >>>
> >>>in the pivot table the costs are the rows, actua/budget are the
> >>>columns and the amount is the data
> >>>
> >>>I now want to have a variance column that calculates the variance
> >>>between actual and budget. I tried the formulas in the pivot table and
> >>>it does not let me add a calculated field that does this simple (a-b)
> >>>type of calculation. Is there an easy way to do this using pivot table
> >>>or if there is another way to do this all suggestions are welcome.
> >>>
> >>>Also I plan to add Dept as page header so that people can select their
> >>>dept and see the actuals and budgets. So my formula should be able to
> >>>account for this.
> >>>
> >>>Thanks for all your help!
> >>>
> >>>Sue
> >>
0
sugandha (4)
8/23/2004 9:02:47 PM
Debra...after all that, the whole set works great...but now the end
user decided to change the format of the data....

instead of showing both the Actual and Budget data by month, I now
want to only show the actual by month with a grand total. This is easy
enough to do, by just hiding the budget fields for the month.

the problem i'm having, is i also need a second grand total column
displayed for the budget amount. I then have to calculate the variance
between the Actual grand total and the budget grand total. is there a
way to display just the grand totals without actually displaying the
budget data for the months? and also how do I calculate the variance
between the 2 grand totals!

Thanks for your help!

Su


sugandha@hotmail.com (Su) wrote in message news:<5f415542.0408231302.13316b64@posting.google.com>...
> Thanks for your help! That works great!!!
> 
> 
> Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<412646F4.6090201@contexturesXSPAM.com>...
> > If you have Excel 2002, or later version, you can use a macro similar to
> > the following, to hide calculated items that are zero:
> > 
> > '=============================
> > Sub HideZeroCalcItems()
> > 'hide rows that contain zeros for calculated items
> > 'by Debra Dalgleish
> > Dim r As Integer
> > Dim i As Integer
> > Dim pt As PivotTable
> > Dim pf1 As PivotField
> > Dim pf2 As PivotField
> > Dim df As PivotField
> > Dim pi As PivotItem
> > Dim pi2 As PivotItem
> > Dim pd As Range
> > Dim str As String
> > Set pt = Sheets("Pivot").PivotTables(1)
> > Set df = pt.PivotFields("Units") 'data field
> > Set pf1 = pt.PivotFields("Item") 'column field
> > Set pf2 = pt.PivotFields("Rep") 'row field
> > Set pi = pf1.PivotItems("YearVar") 'calculated item
> > 
> > For Each pi2 In pf2.PivotItems
> >     pi2.Visible = True
> > Next pi2
> > 
> > i = pf2.PivotItems.Count
> > For r = i To 1 Step -1
> >     On Error Resume Next
> >     str = Cells(r + 5, 1).Value
> >     Set pd = pt.GetPivotData(df.Value, pf1.Value, _
> >       pi.Value, pf2.Value, str)
> >     If pd.Value = 0 Then
> >       pf2.PivotItems(str).Visible = False
> >     End If
> > Next r
> > 
> > End Sub
> > '==============================
> > 
> > For earlier versions, you could use the following code to hide the rows:
> > '==========================
> > Sub HideZeroRows()
> > 'hide worksheet rows that contain all zeros
> > 'by John Green
> > Dim rRow As Range
> > 
> > For Each rRow In ActiveSheet _
> >     .PivotTables(1).DataBodyRange.Rows
> >     If Application.Sum(rRow) = 0 Then
> >       rRow.EntireRow.Hidden = True
> >     Else
> >       'DD --I added this to unhide
> >       'any previously hidden rows
> >       rRow.EntireRow.Hidden = False
> >     End If
> > Next rRow
> > End Sub
> > '=========================
> > 
> > Su wrote:
> > > I tried that option before posting here and what it does is give me a
> > > list of all costs even though the costs dont exist for that dept with
> > > a 0 value. I would then need a way to hide the 0 values.
> > > 
> > > 
> > > Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<4124DA00.9090306@contexturesXSPAM.com>...
> > > 
> > >>You should be able to create a Calculated item:
> > >>
> > >>Select the Actual/Budget field button
> > >> From the PivotTable menu, choose PivotTable>Formulas>Calculated Item
> > >>Type a name for the field, e.g. Variance
> > >>Enter the formula  =Actual-Budget
> > >>Click OK
> > >>
> > >>
> > >>Su wrote:
> > >>
> > >>>I've looked in most of the excel groups on google, but have not found
> > >>>a direct answer to my question, so if this is a repeat, please guide
> > >>>me to the correct post.
> > >>>
> > >>>my problem:
> > >>>my table has 4 columns, dept, costs, amount and Actual/budget
> > >>>
> > >>>in the pivot table the costs are the rows, actua/budget are the
> > >>>columns and the amount is the data
> > >>>
> > >>>I now want to have a variance column that calculates the variance
> > >>>between actual and budget. I tried the formulas in the pivot table and
> > >>>it does not let me add a calculated field that does this simple (a-b)
> > >>>type of calculation. Is there an easy way to do this using pivot table
> > >>>or if there is another way to do this all suggestions are welcome.
> > >>>
> > >>>Also I plan to add Dept as page header so that people can select their
> > >>>dept and see the actuals and budgets. So my formula should be able to
> > >>>account for this.
> > >>>
> > >>>Thanks for all your help!
> > >>>
> > >>>Sue
> > >>
0
sugandha (4)
8/25/2004 1:29:15 PM
You can do those calculations on the worksheet to the right of the pivot 
table.

I don't think you'll get the results you want within the pivot table.

Su wrote:
> Debra...after all that, the whole set works great...but now the end
> user decided to change the format of the data....
> 
> instead of showing both the Actual and Budget data by month, I now
> want to only show the actual by month with a grand total. This is easy
> enough to do, by just hiding the budget fields for the month.
> 
> the problem i'm having, is i also need a second grand total column
> displayed for the budget amount. I then have to calculate the variance
> between the Actual grand total and the budget grand total. is there a
> way to display just the grand totals without actually displaying the
> budget data for the months? and also how do I calculate the variance
> between the 2 grand totals!
> 
> Thanks for your help!
> 
> Su
> 
> 
> sugandha@hotmail.com (Su) wrote in message news:<5f415542.0408231302.13316b64@posting.google.com>...
> 
>>Thanks for your help! That works great!!!
>>
>>
>>Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<412646F4.6090201@contexturesXSPAM.com>...
>>
>>>If you have Excel 2002, or later version, you can use a macro similar to
>>>the following, to hide calculated items that are zero:
>>>
>>>'=============================
>>>Sub HideZeroCalcItems()
>>>'hide rows that contain zeros for calculated items
>>>'by Debra Dalgleish
>>>Dim r As Integer
>>>Dim i As Integer
>>>Dim pt As PivotTable
>>>Dim pf1 As PivotField
>>>Dim pf2 As PivotField
>>>Dim df As PivotField
>>>Dim pi As PivotItem
>>>Dim pi2 As PivotItem
>>>Dim pd As Range
>>>Dim str As String
>>>Set pt = Sheets("Pivot").PivotTables(1)
>>>Set df = pt.PivotFields("Units") 'data field
>>>Set pf1 = pt.PivotFields("Item") 'column field
>>>Set pf2 = pt.PivotFields("Rep") 'row field
>>>Set pi = pf1.PivotItems("YearVar") 'calculated item
>>>
>>>For Each pi2 In pf2.PivotItems
>>>    pi2.Visible = True
>>>Next pi2
>>>
>>>i = pf2.PivotItems.Count
>>>For r = i To 1 Step -1
>>>    On Error Resume Next
>>>    str = Cells(r + 5, 1).Value
>>>    Set pd = pt.GetPivotData(df.Value, pf1.Value, _
>>>      pi.Value, pf2.Value, str)
>>>    If pd.Value = 0 Then
>>>      pf2.PivotItems(str).Visible = False
>>>    End If
>>>Next r
>>>
>>>End Sub
>>>'==============================
>>>
>>>For earlier versions, you could use the following code to hide the rows:
>>>'==========================
>>>Sub HideZeroRows()
>>>'hide worksheet rows that contain all zeros
>>>'by John Green
>>>Dim rRow As Range
>>>
>>>For Each rRow In ActiveSheet _
>>>    .PivotTables(1).DataBodyRange.Rows
>>>    If Application.Sum(rRow) = 0 Then
>>>      rRow.EntireRow.Hidden = True
>>>    Else
>>>      'DD --I added this to unhide
>>>      'any previously hidden rows
>>>      rRow.EntireRow.Hidden = False
>>>    End If
>>>Next rRow
>>>End Sub
>>>'=========================
>>>
>>>Su wrote:
>>>
>>>>I tried that option before posting here and what it does is give me a
>>>>list of all costs even though the costs dont exist for that dept with
>>>>a 0 value. I would then need a way to hide the 0 values.
>>>>
>>>>
>>>>Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<4124DA00.9090306@contexturesXSPAM.com>...
>>>>
>>>>
>>>>>You should be able to create a Calculated item:
>>>>>
>>>>>Select the Actual/Budget field button
>>>>>From the PivotTable menu, choose PivotTable>Formulas>Calculated Item
>>>>>Type a name for the field, e.g. Variance
>>>>>Enter the formula  =Actual-Budget
>>>>>Click OK
>>>>>
>>>>>
>>>>>Su wrote:
>>>>>
>>>>>
>>>>>>I've looked in most of the excel groups on google, but have not found
>>>>>>a direct answer to my question, so if this is a repeat, please guide
>>>>>>me to the correct post.
>>>>>>
>>>>>>my problem:
>>>>>>my table has 4 columns, dept, costs, amount and Actual/budget
>>>>>>
>>>>>>in the pivot table the costs are the rows, actua/budget are the
>>>>>>columns and the amount is the data
>>>>>>
>>>>>>I now want to have a variance column that calculates the variance
>>>>>>between actual and budget. I tried the formulas in the pivot table and
>>>>>>it does not let me add a calculated field that does this simple (a-b)
>>>>>>type of calculation. Is there an easy way to do this using pivot table
>>>>>>or if there is another way to do this all suggestions are welcome.
>>>>>>
>>>>>>Also I plan to add Dept as page header so that people can select their
>>>>>>dept and see the actuals and budgets. So my formula should be able to
>>>>>>account for this.
>>>>>>
>>>>>>Thanks for all your help!
>>>>>>
>>>>>>Sue
>>>>>


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

0
dsd1 (5911)
8/25/2004 7:48:39 PM
Reply:

Similar Artilces:

Table to feed form instead of lenghty code?
Hello, I a database that is kind of like an Auction type deal. If a user enters their user ID and click the button, it will automatically display their chosen private ID (or Auction ID). What I want to know is, I have 80 people in my department and the code (only way I know how to do it) will become very long. Can I set up a table with the User ID and Private ID and when the button is clicked, have it read from the table and display the Private ID that way? Below is how I am going to do it if no alternative. Private Sub cmdStatusUpdate_Click() Me.NewBid.Locked = False ...

"grouping" a table to prevent change from users
Hi, I would like to group an entire table with a wdContentControlGroup type of Content Control to prevent editing from users except in some wdContentControlRichText type of Content Control. I'm having problem in passing the right Range of the table. If I use: With ActiveDocument Set r = .Tables(1).Range Set objcc = .ContentControls.Add(wdContentControlGroup, r) End With an error "5224" "Wrong selection" arise. If I use: With ActiveDocument .Tables(1).Select Set objcc = .ContentControls.Add(wdCo...

pivot from more than 65K lines: how?
What I am trying to do: have several excel files with data and want to consolidate in a single file What I did: tried to paste all the data in a single file and create a pivot from there... the problem is that I can not copy after reaching 65K lines.... Trying to create a pivot from *multiple* excel does not give me the possibility to put in the layout the fields I want (as if I was using a single sheet)... as it consolidates all the fields in the different tables... Any urgent help please?.. MANY TIA! luis Do you have Access? If so then import all your excel sheets into a singl...

Intelligent Pivot Table with Large Data Sets
Pivot Tables are great with small datasets. Does anyone have suggestions of products that can datasource from a ODBC source and manage large datasets (1M+ rows), visualizing the dat in a crosstab/pivottable format ? Unfortunately the underlying logic in the PivotTable/MSquery link isn' too intelligent in that is always wants to read the whole darn databas table before even presenting a field list. I assume this is due to th abstraction of the data created by sticking MS/Query inbetween th Excel pivot table service and the actual datasource? Seems pretty simply from a conceptual POV: 1. ...

2 pivot tables on one tab
Is it possible to put 2 Pivot tables on the same tab in my workbook? Or does anyone have any ideas on what would be the best way to chart Capital Pircahses? I have 5 coums and 4 rows. The rows are per division, and the columns are for the disposal amount, gain or loss, book value, depreciation & purchase price. I already have one pivot table in my report. Would this be too repeatative? Thanks You can put multiple pivot tables onto a worksheet. If the second one is based on the same data, select and copy the first, select a cell some distance away, and paste it. Each pivot table...

Need help with SQL tables
After running a security reconcile sa was no longer a user in GP. I received the answer below about re-adding it, but I don't know how to add a userid in to a table. I can't find anything on knowledgebase about this. We don't have the SQL Enterprise Manager but use the SQL 2005 Management Studio Express. Can anyone help me? I just need to know the script that will add it to these tables. Thanks. Tracey D "Therefore it will be just a matter of adding 'sa' USERID into the following tables in DYNAMICS database as a minimum. Once added, use ano...

Wrong(?) calculation result in Excel
While my calculator gives the correct result for 111,111,111 X 111,111,111 (12345678987654321, when I use Excel for the same calculation, the result I get is close, but not exact--12345678987654300. Can anyone help explain what's happening in Excel? Many Thanks, Jim Hi Go to Excel help and do a search for Specifications. In there you will see that Excel only calculates to 15 digits of precision. I'm sure others will post quoting the IEE specification that Excel works to. It's very interesting and well worth a read. -- Andy. "Jim" <jlclemen@ius.edu> wrote ...

Table object
There is a little table object that shows up on the top left of a table that you can use to move the table to the left or right. But I can't seem to figure out how to get that table object to appear. You have to click around select the whole table/ deselect the table and then at some point it appears. How do you get it to appear so you can move the table? I am getting tired of trying to randomly get it to appear. Thanks, Tom Which version of Word? We support about 20... On 7/04/10 2:14 PM, in article O4J9ajg1KHA.4832@TK2MSFTNGP04.phx.gbl, "tshad"...

Calculating date difference
Hi I need to calculate the number of years of service for person(difference between Start_Date & End_Date). The result should b in terms of years, months and days (all in one single cell). Ca someone please help? Thanks ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Have a look at Chip's site: http://www.cpearson.com/excel/datedif.htm Andy. "rll" <rll.xjm6a@excelforum-nospam.com> wrote in message news:rll.xjm6a@excelforum-nospam.com... > &...

calculating dates prior to a given date
Hi. I am trying create a spreadsheet that will have dates listed in one column and have another column list the date 14 days prior to the first column's dates. I'm hoping to then organize the second column ascending from the closest to the furthest date, compared to the current date. If anyone has any help they could provide it would be much appreciated. Thank You. If you have a date in A1 and you want another date in B1 which is 14 days earlier, then put this in B1: =3DA1-14 Copy this formula down, then fix the values by selecting all those cells with the formula ...

Pivot Table Freezes
I have many pivot tables off a large database and they periodically freeze when I refresh the data. Only solution is to crash out. Any ideas, comments, solutions out there. The machine has been checked out OK. I work on a network - checked out OK. Maybe this is the problem: XL2000: PivotTable Updated Slowly with OLAP Data Source http://support.microsoft.com/default.aspx?scid=237469 Jimbo wrote: > I have many pivot tables off a large database and they periodically freeze > when I refresh the data. Only solution is to crash out. Any ideas, comments, > solutions out there....

Table calculations. Season from Date in Hunting Harvest Database.
I am trying to set up a hunting harvest database. I would like to add a column to the original data table that automatically calculates the hunting season, which is based on a specific harvest timeframe. Each harvest record entry has a specific date. For example, when a record of a deer harvested on 12-3-2007 is entered; I want the database to calculate that the date occurred during the fall 2007 season. Thus, I want 2007 to be automatically entered for this record in a separate column. However, our seasons last into the new year, so if for example a record is entered of a ...

Pivot Table Data Ranges
I just upgraded to excel2000, and am finding out that keyboard shortcuts used in 97 do not exist in 2000. Does anyone know of a KEYBOARD shortcut to modify, expand and or create data ranges in the dialog box for entering the range for the pivot. I have many data 'lists' which are added to each month, and when I want to update the corresponding pivot table, I used to simply hit "shift" + "end" + "down arrow" to automatically go to the last populated cell in the list. In 2000, I either have to enter in the cell addy's manually or use the mouse...

freight calculations
Hello: In using Sales Order Processing with GP Manufacturing, do most companies expense freight or revalue freight in cost? Also, does GP Manufacturing have the capability to calculate freight? Or, is freight manually entered into the Sales Order Processing invoice by the end user? Thanks! childofthe1980s Freight is handled a wide variety of ways by clients. Some absorbe it into the cost of sales, some list it as a separate cost of sales, some expense it. Others bill the client, pay the freight and balance the two charges. Manufacturing will not calculate real freight charges. I...

Pivot Tables show zero balance
Hi - I have a pivot table.... I want to show customers that have an outstanding balance. when i sort by month, i see everything, totals of who paid and who ha not. I want to sort to see only who is outstanding. How do I do this? CURRENT SETUP: Sort by page = date of invoice (by month) Rows = client name, then the tota -- Message posted from http://www.ExcelForum.com You could add a Status column to your data source, and calculate the account status. For example, with customer names in column C and amounts in column H: =IF(SUMIF($C$2:$C$39,C2,$H$2:$H$39)>0,"Outstanding&...

Copying fields from combo box to a table
Hi, I am creating a database for our Machine Maintence Report (access 2003). I created a combo box from table1 and I want to have the selected fields (i.e. Machine, Technician, Engineer etc...) in table1 to be copied in table2. This table2 has the same fields Machine, Technician, Engineer plus the other field that will be updated when the maintenance is done such as remarks, issue and data readings. Looking forward for your help. If you really have a need to store this information in multiple tables, you can use code in the After Update event of the combo box to push the valu...

Misfiring calculation
I'm trying to calculate a total and pull it into a query both to show the amount and for a subsequent expression. However the result i'm getting is the product of the total times the number of occurances of its index in the table i'm totaling on. I hope that makes sence. I'm sure this is quite simple. thank you in advance Show us the SQL. Open the query in design view. Next go to View, SQL View and copy and past it here. Information on primary keys and relationships would be a nice touch too. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bon...

Web table to Excel...
Could someone help me exporting the tables from the following web-page into excel ? http://www.ekdd.gr/ESDD/IH/REBBBD-esdd-epityxontwn.htm (use unicode encoding) is this impossible ? ...

MFC String-table in C# ?
Hi, maybe a bit OT for this newsgroup, but I know that people here use C#, too. So, does anyone know how the MFC string-table technique map to C#/WinForm? I initially thought about using an integer->string map in C#, but is there some built-in mechanism to manage that? Thanks, Giovanni "Giovanni Dicanio" <giovanniDOTdicanio@REMOVEMEgmail.com> ha scritto nel messaggio news:ugqw1g7LJHA.1736@TK2MSFTNGP03.phx.gbl... > So, does anyone know how the MFC string-table technique map to C#/WinForm? ....after some web search, it seems that C# has a ResourceManager class ...

Copying and Pasting Excel Tables into Word
We do our financials in Word, and copy and paste special as enhanced metafiles the tables that we do in Excel. This works perfectly in Word 2003. In 2007 however, when we past the table and print it, the table looks different (letter spacing funny, not smooth). I have tried printing to five different printers and it still happens. I'd suggest a slightly different approach. If you insist on using enhanced metafiles, try the following: 1. Select the range that you want to copy. 2. Instead of Copy, select the dropdown under Paste>As Picture>Copy as Picture. 3...

Find cell numbers in a table so I can multiply
Hello, I have a word document and in the documents header there is a table. This table a has diferent number of cells in each row like: Row one: 2 cells Row two: 4 cells Row three: 2 cells Row four: 10 cells Row five: 2 cells What I wanted to do is multipy 3 cells together thats in row four and show the total in the same row. I have a number in the 3rd cell that needs * by the number in the fith cell that needs * by the number in the seventh cell and totaled in the 9th cell. I know I have to select the ninth cell and select table/formula then what? -- Thanks, Chad I ...

Calculating averages
I have 5 cells that I am trying to average, but I only want to average those cells that contain data. Using the if statement I was able to get two cells to work, but when I tried a nested if statement I was unable to get it to work if there was only 1, 2, or 3 cells with data in them. What do I need to do to resolve this? Thanks in advance. =SUM(A1:A5)/COUNT(A1:A5) Vaya con Dios, Chuck, CABGx3 "Carl Johnson" <bjohnson@woh.rr.com> wrote in message news:hwM4b.25285$l41.7213466@twister.neo.rr.com... > I have 5 cells that I am trying to average, but I only want to average...

Sizing Data Tables on a chart
When I have a data table attached to a chart and size the chart, the data table does not size proportionately ...

Problem inserting calculated pivot fields into Pivot Table (2007)
Working with Excel 2007 and a Pivot Table. Created a Calculated field. The field shows in the Field List, but Excel will not let me drag the Calculated field into the Pivot Table areas (other than into the Values area). What am I doing wrong? Hi A calculated item or field, can ONLY be allocated to the data area. It's position within the data area can be modified, but it cannot be moved to any other area -- Regards Roger Govier wamiller36 wrote: > Working with Excel 2007 and a Pivot Table. Created a Calculated field. The > field shows in the Field List, b...

Calculate Due Date?
I need a formula that will calculate a due date 10 days from the start date (a1)but if the date falls on a weekend or holiday I need the date to be the next working day. I will have a list of holidays in J2:J50. Thanks for any help.......... -- HTH Bob Phillips "Randy" <ranmcc@msn.com> wrote in message news:1121099149.930051.258040@o13g2000cwo.googlegroups.com... > I need a formula that will calculate a due date 10 days from the start > date (a1)but if the date falls on a weekend or holiday I need the date > to be the next working day. > > I will have a...