I have a pre- and a post-test blood pressure value. The table also has

other pre/post values, so I don't think that splitting systolic/diastolic

values into 2 cells is feasible. How do I calculate the change b/w the two

compound values?

Thank you for your help.

pre post

122/85 130/70

other pre/post values, so I don't think that splitting systolic/diastolic

values into 2 cells is feasible. How do I calculate the change b/w the two

compound values?

Thank you for your help.

pre post

122/85 130/70

- Calculate % change in price for each of 200+ products
- Calculate a Specific Sheet when something change in the workbook
- Change summary for calculated field in pivot table
- Formula for average recorded blood pressure readings in 1 column .
- Unable to change field settings in calculated field in a pivot tab
- Formula to calculate/compare changes in 1 to 10 scale!......
- Calculating down in a column product of two other cells W/O changing calculated value
- Trying to use worksheet_change to trigger on change in linked cell of Scroll Bar (FC)
- Calculate sales using incremental changes in percentage factor
- Calculated fields in pivot to extend sourcetable of 180 columns
- Slow Calculation speeds in big spreadsheet
- Editing Calculated Fields in Pivot tables?
- Insert a blank row after change in data
- Calculated Field in A Pivot
- Calculation Setting in Excel
- Excel 2002 -> Problem with calculated fields in Pivottable
- Get aveages for last few days:blood pressure
- Pivot Table to do change in sales from year
- Calculated Field in a pivot table
- Urgent help needed - Change in userform...
- Monitoring Changes in Elevation
- Subtotaling by Half-Change in Category
- How do I make a change in time into a number I can use to calcula.
- Calculation Setting in Excel

of 200+ items. Some items are ordered monthly, others periodically. I have

a spreadsheet of ALL the orders for each product type, it is 9000+ lines.

Prices have changed several times for many products. I want to summarize the

list of each product, identify the highest and lowest price paid and the % of

change in the price for each item.

Item Hi Low % Incr

Paper towels $4 $3 33%

5 gal Plastic bags $6 $4 50%

etc.

I have build an add-in To Calculate Specific Sheet when something change in the workbook. For this I am unselecting the "automatic calculation" option and check "Manual". Now with every change in a cell i would to call AUTO_CALCULATION_PI that is calculating only the sheet that the user have been selected.

The problem when i change a cell Workbook_SheetChange is not stiggered and nothign happen. I dont understand why ?

The add-in is doing the following. Create a Menu "PI Options", this open a user form where you can select the worksheet to calculate, the choices is store in hidden worksheet called "AUTO-CALCULATION-PI". When a cell is change the Macro AUTO_CALCULATION is called, and calculated the sheets that the user have selected.

Thank you so much ! If my explanation are not clear please let me know !

= REVENUE - EXPENSE

I need to create another calculated field that determines the "per customer" profit. I thought I could just add the PROFIT field I just created to the pivot table again and change the summary function to AVERAGE since this would give me the PROFIT divided by the number of records (in this case, the number of customers). However, it isn't working. I just get the SUM of the PROFIT calculation no matter which summary method I choose. What am I doing wrong? Is there a work around for this?

Thank you.

formula to average them? i.e.

120/80

130/76

128/77

______

126/78 is avg

Thanks, Harry

This is just a simple calculated field that divides one column by the other

from the data list.

The calculated field works fine, except that I need the data averaged and

not summed as it is defaulting to. In the field settings for the calculated

field, the ability to change the "summarized by" block is greyed out and I

can't change it to average.

I know of a work around to this by just adding another column to my data

list but I would rather not do it that way.

Is it possible to change the "summarized by" setting for a calculated field?

Thanks in advance for any help.

.

I want to compare multiple cells value with each other and track any changes from 1 to 10 scale.

.

Example:

I have 8 cells and value of those cells change every day.

.

I want to compare each cell's value with other 7 cells and record the changes in 1 to 10 scale. I don't know how to calculate in scale. I want to populate result in Colmn C1.

.

Need formula how to calculate. Any help would be appreciated....... Thanks,

.

-----Colmn A ---- Colmn B --- Colmn C

Row1:Client ----- Average-----Change

Row2:Edd ------ 23.38

Row3:Gem ------- 26.17

Row4:Adam ------- 14.88

Row5:Usher ------- 15.95

Row6:Sam ------- 1.14

Row7:Jim ------- 99.77

Row8:Neil ------- 14.03

Row9:Chuck------- 13.15

The reference cells from where I wan to keep values will be changing but the formula cell column should not change the earlier calculated value in the upper cells. Addressing cells will be exactly the same all the time with changing values but formula cell will be going down.

Please tell me what formula shall I use in my column?

I am trying to make dyanmic scrollable charts from a dyanmic range being filled up using vlookup from a bigger range. Now for vlooking I need somthing for reference to be dyanmically filled there so that the rest of the values which will make up the chart could be calculated.

So for filling up those I wished to use worksheet_change to track the change in linked cell of that scroll bar. (Actually the scroll bar can go from 1 to 12 representing months, and each month then has different values which are then displayed in the chart).

So the code I was using is:

VB:selectedmonth is jan,feb, mar etc depending upon the value in linked cell and Noofdays is no of working days in the month. Both are named ranges in that sheet.Range) Dim i As Integer Dim m As String Dim j As Integer m = selectedmonth If Target.Address = "$H$28" Then For i = 0 To Noofdays Range("34+i:8").Value = m & (i + 1) Next i For j = Noofdays + 1 To Noofdays + 10 Range("34+j:8").Value = "" Next j End If End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

But then I learnt that A Worksheet_Change event: triggers when you change a cell (or range of cells) value manually or in a macro -- it will not be triggered from a change showing up in a formula or from a change of format. So a worksheet_change would not trigger on change made by scroll bar.

Hence I decided to go for worksheet_calculate but it then started giving all sorts of errors.

Hope I have made my problem clear and hope someone can help!!

I'm trying to model a simulation in which every percent variation in disposable income leads to a real change in my sales. As in 1% increase leads to a 100 Dollar increase in sales. Moreover, I'd like to limit this range from -35 to 35%. Is that possible or should I just not vary that much? I'm absolutely lost on this so I'd appreciate any help! Thank you!

The attached xls file shows what I've done so far. I'm using an if function to have a relation with disposable income/GDP. But I cannot exceed 7 Ifs per cell. What do I do? Example.xlsx

I have a sheet with about 180 columns showing for each month data as budget sales, last year sales, forecast sales, actual sales, price variances, volume variances, etc.

I have written a macro to create a pivottable linked to this source and would like to include calculated fields in this macro to accumulate monthly data in order to incorporate columns with Year-to-date, Rest-of-year, and Full Year data. For example a simplified version of the pivottable source (sheet2) looks like ( I don´t know how to attach spreadsheets to this post):

A1: Region

A2: North

A3: West

B1 to M1: Sales M01, Sales M02, Sales M03, Sales M04 etc

The macro is assuming YTD (Year to Date) is January - March, and looks as follows:

Code:

Sub create_pivot1() Dim PTCache As PivotCache Dim PT As PivotTable Dim Lastrow, Lastcolumn As Long Application.ScreenUpdating = True 'Delete any prior pivottables For Each PT In Sheet1.PivotTables PT.TableRange2.Clear Next Sheet2.Select Lastrow = Cells(60000, 1).End(xlUp).Row Lastcolumn = Cells(1, 256).End(xlToLeft).Column Set PRange = Range(Cells(1, 1), Cells(Lastrow, Lastcolumn)) Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address) 'Create the pivottable Set PT = PTCache.CreatePivotTable(TableDestination:=Sheet1.Range("A1"), TableName:="Pivottable1") 'Set up the row & column fields PT.AddFields RowFields:=Array("Region"), ColumnFields:="Data" 'Define field names. field1 = "Sales M01" field2 = "Sales M02" field3 = "Sales M03" field4 = "Sales M04" field5 = "Sales M05" field6 = "Sales M06" field7 = "Sales M07" field8 = "Sales M08" field9 = "Sales M09" field10 = "Sales M10" field11 = "Sales M11" field12 = "Sales M12" cfield1 = "Sales YTD" cfield2 = "Sales FY" cfield3 = "Sales ROY" Cfield1_Formula = "=('" & field1 & "')+('" & field2 & "')+('" & field3 & "')" PT.CalculatedFields.Add cfield1, Cfield1_Formula Cfield2_Formula = "=('" & field1 & "')+('" & field2 & "')+('" & field3 & "')+('" & field4 & "')+('" & field5 & "')+('" & field6 & "')+('" & field7 & "')+('" & field8 & "')+('" & field9 & "')+('" & field10 & "')+('" & field11 & "')+('" & field12 & "')" PT.CalculatedFields.Add cfield2, Cfield2_Formula Cfield3_Formula = "=('" & cfield2 & "')-('" & cfield1 & "')" PT.CalculatedFields.Add cfield3, Cfield3_Formula 'Set up the datafields With PT.PivotFields(field1) .Orientation = xlDataField .Function = xlSum .Caption = " " & field1 .NumberFormat = "#,##0_);[Red](#,##0)" .Position = 1 End With With PT.PivotFields(field2) .Orientation = xlDataField .Function = xlSum .Caption = " " & field2 .NumberFormat = "#,##0_);[Red](#,##0)" .Position = 2 End With With PT.PivotFields(field3) .Orientation = xlDataField .Function = xlSum .Caption = " " & field3 .NumberFormat = "#,##0_);[Red](#,##0)" .Position = 3 End With With PT.PivotFields(cfield1) .Orientation = xlDataField .Function = xlSum .Caption = " " & cfield1 .NumberFormat = "#,##0_);[Red](#,##0)" .Position = 4 End With With PT.PivotFields(cfield2) .Orientation = xlDataField .Function = xlSum .Caption = " " & cfield2 .NumberFormat = "#,##0_);[Red](#,##0)" .Position = 5 End With With PT.PivotFields(cfield3) .Orientation = xlDataField .Function = xlSum .Caption = " " & cfield3 .NumberFormat = "#,##0_);[Red](#,##0)" .Position = 6 End With 'deselect grand totals With PT .ColumnGrand = False .RowGrand = False End With 'Calc the pivottable PT.ManualUpdate = False PT.ManualUpdate = True End SubHowever, as said the real sourcesheet contains 180 columns, which would make the VBA scipt incredibly long if I would one-by-one set up each pivotfield as displayed in the VBA script above. On top of that, each month I would have to adjust the formula for the calculated field "YTD" (cfield1).

Summarized I am looking for the following solutions:

1) How can I avoid setting up the pivot fields one-by-one? Eg. is it possible to include a loop?

2) How can I make the calculated field cfield1 (YTD) formula dynamic, i.e. changing when the month changes.

3) Is it possible to shorten the formula for calculated field cfield2 (Full Year) ?

I know I am asking a lot, but I thought let's try though I will continue trying to find a solution among the thousands of great posts and replies on this forum. In fact I would be surprised if no-one else in a finance reporting environment has faced this issue before.

Many thanks

Emiel

portfolio. It has become very slow - between 15 to 30 seconds to complete

calculation of any change in input variable. It is 200 columns wide and

3,000 rows deep. I have tried to eliminate formulae which I believe need

more time to compute: e.g COUNTIF functions. Can anyone let me know which

the worst offenders are of the following functions:

Conditional Formatting for colour coding

(I know coloured cells use memory, but how bad are they?)

Big nested IF functions - most containing the other functions listed here

e.g IF(AVERAGE( OFFSET(X,-MIN(COUNTIF(),MIN(COUNTIF(),0,0))))

MIN() MAX() AVERAGE()etc

OFFSET()

RANK()

SLOPE()

MATCH()

INDEX()

SUMIF()

I have tried to limit the number of cross sheet references to only one: a

chart that plots two series of 3,000 values.

I set the workbook Manual calculation to speed things up while I am working

on the program.

I have a number of simplistic macros which are neither elegant nor properly

defined by "Dim" because I don't know how to decide which Dim to use. But

the macros do disable screen-updating when running.

I got rid of the special functions (I don't know what they are called) with

curly brackets on either end which require hitting CTRL ALT Enter (or some

such) when entering in the belief they were responsible for slowness.

And finally, when I have two consecutive versions of the workbook open at

the same time, I often get the error message that I have run out of memory.

I use Windows XP and the computer is 2.08 GHz, 512 MB RAM.

When I change a variable the Performance tab in Windows Task Manager shows

CPU usage at 100%.

Is there a way I can allocate more memory to my Excel workbook?

I would very much appreciate any tips, help or assistance, and thank any

kind soul in advance.

How can I get into an edit mode to change and existing calculated field in a pivot table?

the function to START at row 10 - not end at row ten. But perhaps I don't

understand the coding language because it did start work - starting at row 10

where my data starts. Thanks again.

"Max" wrote:

> Came across a sub below by a poster in .programming

> Modified a little to end at row 10 to suit.

> Try it on a spare copy ..

>

> Sub InsertRow_At_Change()

> Dim i As Long

> With Application

> .Calculation = xlManual

> .ScreenUpdating = False

> End With

> For i = Cells(Rows.Count, 2).End(xlUp).Row To 10 Step -1

> If Cells(i - 1, 2) Cells(i, 2) Then _

> Cells(i, 1).Resize(1, 1).EntireRow.Insert

> Next i

> With Application

> .Calculation = xlAutomatic

> .ScreenUpdating = True

> End With

> End Sub

>

> --

> Max

> Singapore

> http://savefile.com/projects/236895

> xdemechanik

> ---

> "Genghis2k3" wrote:

> > I have a spreadsheet with 300 rows of data and I need a macro that will

> > insert a blank row after a change in the data in column B. The data starts

> > in row 10. Thanks for your help.

Need help creating a calculated field in a Pivot Table

Below is an example of my Pivot including the first two rows of data.

Average of LoS DIABETIC?

OPCS_1 Diabetic Non Diabetic

A559 13.0 9.1

A578 0.0 0.9

What I want to add for each row is a third column which displays the the

difference between the two columns so I would end up with:

Average of LoS DIABETIC

OPCS_1 Diabetic Non Diabetic DIFFERENCE

A559 13.0 9.1 3.9

A578 0.0 0.9 0.9

I can do this by adding a formula in the cell to the right of the last

column of the pivot, but this is no good as the references will be thrown

out if the Pivot changes.

So how do I add this calculation as part of the Pivot table so that it

updates with a refresh?

The field giving the figures for Diabetic & Non Diabetic columns is an

Average and this seems to be causing a problem.

that rather than being an application level setting it becomes a workbook

level setting. ie a user could have several workbooks open, each with

different calculation settings (automatic, manual, etc). When a workbook is

saved and closed the most recent calculation setting would be remembered by

excel. This request stems from the following problem:

1. Lets say I have a small workbook (even a blank workbook) open with

calculation set to automatic.

2. Next I open a large workbook which runs a VBA procedure upon opening.

Part of that procedure is to set the calculation to manual because the model

is too big to leave it on automatic.

3. However, because of the 1st model being open the 2nd model wants to

perform a full recalculation before running the auto_open code.

4. This makes the 2nd model appear to be VERY slow and I usually interupt

the calculation by, say, pressing CAPS LOCK which then allows the auto_open

procedure to run.

5. Pressing CAPS LOCK works for me because I know to do that now but when

other users (who are not so experienced with Excel) use my model they don't

know to do this, so they end up with the view that the model is slow to open

which is inaccurate.

Another way of achieving this would simply be to allow auto_open macros to

run before a full recalculation is performed upon opening a model.

----------------

This post is a suggestion for Microsoft, and Microsoft responds to the

suggestions with the most votes. To vote for this suggestion, click the "I

Agree" button in the message pane. If you do not see the button, follow this

link to open the suggestion in the Microsoft Web-based Newsreader and then

click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

I have created a Pivot table and use a calculated field.

Now I want to eleminate the "0" of this calculated field.

Normally I would drag the field into the page area.

But this does not work the normal way.

I don't want to change the original database. Does anyone

know the possibilty of using a calculated field in the page

area of a pivot table?

Or another way to avoid the zeros ?

Thanks foru your help

=AVERAGE(OFFSET(INDIRECT(ADDRESS(1,1)),MATCH(9999999999,A:A)-30,,30))

will average last 30 values in column A and will give error if you have less

than 30 readings.

"Sick Puppy" <Sick Puppy@discussions.microsoft.com> wrote in message

news:4F010F7E-D696-46DB-AEE8-A45937BE3D9B@microsoft.com...

>I am making a new spreadsheet to record daily blood pressure readings.

>Each

> day's readings go into a row below the previous day's readings. I can use

> "=average" to get the average for all entries. Is there a function or

> other

> means of getting the average for, for instance, the last 30 entries, which

> would be for the last 30 days?

>

> Thanks in advance!

>

> Tim

I have the information from the accounting program in a table but I don't know how to set up the calculated field so that it shows the change.

The rows have the customer name and the columns have the date field from the table in a group mode. I want to compare the 2011 sales to the current sales date in 2012 too.

Thanks for any help.

Lee

Thanks!

I want this textbox updated whenever there is a change in optionbuttons or checkboxes (I dont want to press the OK button to make the calculations)

For example if there is such a sub like "Private Sub Userform1_Change()" , i can use it. But i could not find such a sub

Please help

Thanks in advance

for instance:

point #1 initial elev. 500

week-1 elev. 499.99 change = .01

week-2 elev. 499.97 change = .03

week-3 elev. 499.95 change = .05

on so on......................

I only want to show the number of the most changed elev. (as shown above it would be "-.05".) How can I apply a formula to this that will calculate and only show the most changed amount?

The attached file has sample data in sheet 1 and the results I am trying to achieve on sheets 2 and 3.

I would like to calculate averages of the variables "mins" and "overage" for the first half and second half of each user id group. The subtotal command computes the averages perfectly for every change in user id, but I need results for every half-change.

One potential solution would be a function or program that renames the first half of every group of identical user ids "#######a" and the second half "########b." Any ideas how to achieve this or something equivalent?

flow rate calculation, however, once I get the changes in time, I get an

error in the cell used for calculations. It seems the time factor in the

equation is throwing everything off. What I want to to is somehow use the

change in time (5:00 am from 6:00 pm which should equal 13 and not 13:00) so

it can be used in a formula

that rather than being an application level setting it becomes a workbook

level setting. ie a user could have several workbooks open, each with

different calculation settings (automatic, manual, etc). When a workbook is

saved and closed the most recent calculation setting would be remembered by

excel. This request stems from the following problem:

1. Lets say I have a small workbook (even a blank workbook) open with

calculation set to automatic.

2. Next I open a large workbook which runs a VBA procedure upon opening.

Part of that procedure is to set the calculation to manual because the model

is too big to leave it on automatic.

3. However, because of the 1st model being open the 2nd model wants to

perform a full recalculation before running the auto_open code.

4. This makes the 2nd model appear to be VERY slow and I usually interupt

the calculation by, say, pressing CAPS LOCK which then allows the auto_open

procedure to run.

5. Pressing CAPS LOCK works for me because I know to do that now but when

other users (who are not so experienced with Excel) use my model they don't

know to do this, so they end up with the view that the model is slow to open

which is inaccurate.

Another way of achieving this would simply be to allow auto_open macros to

run before a full recalculation is performed upon opening a model.

----------------

This post is a suggestion for Microsoft, and Microsoft responds to the

suggestions with the most votes. To vote for this suggestion, click the "I

Agree" button in the message pane. If you do not see the button, follow this

link to open the suggestion in the Microsoft Web-based Newsreader and then

click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc