Free Microsoft Excel 2013 Quick Reference

Calculate change in blood pressure

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


I work for a school system. Schools order custoidal supplies from a catalog
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.

Hi,
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 !

I have a calculated field in a pivot table (Excel 2007) that determines PROFIT as follows:

= 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.

I enter my blood pressure readings for the month in one column. Is there a
formula to average them? i.e.
120/80
130/76
128/77
______
126/78 is avg
Thanks, Harry

I have a pivot table that uses a calculated field in the data items section.
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.

Hi,
.
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

I want enter a formula in a column where the formula take the product of any other two cells, calculate and then go down the same column calculate dynamically again the same product from the same cells .
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?

Hi Everyone,

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:
	
 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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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.

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!!

Hi there,

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

Hi!

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 Sub
However, 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

I have a very large 22.4 spreadsheet which I use to manage risk in my trading
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?

Thanks Max, that did the trick. I was concerned because I actually needed
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.

Hi,

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.

I would like to suggest that the calculation setting in Excel is changed so
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

Hi,

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

if your values are continuous with no blanks in the middle then
=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

Excel 2007 and I want to see the change in this year versus last year customer purchases per customer but can't figure out how to do so.
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

I am creating some calculated fields in a pivot table. This will calculate some historical data, where there are some blanks in the data. This results in a "#DIV/0!" error. So, I changed the formula from " =SumOfSumOfActives/SumOfSumOfTrials " to a fomula using the IF(ISERR function, " =IF(ISERR(SumOfSumOfActives/SumOfSumOfTrials),"-",(SumOfSumOfActives/SumOfSumOfTrials)) ". But now, it gives me the " #VALUE! " error. I think the issue is that this is a numerical field, and if I subsitute the dash in the formula (-) with a zero, it works and shows 0%. The problem that I have is that it is showing 0% for the ones with the error, and also 0% for a few cells where the actual value should be zero. I would prefer to the error outcome be something other than zero. Suggestions anyone?

Thanks!

i have a userform. In that userform there are some optionbuttons and checkboxes. And i have a textbox in which i see the results according to above mentioned buttons.

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

I have a sheet that we are tracking the elevations of 13 pins weekly to show the total amount of settlement of a building pad. I want to show the maximum change in elevation only. I have columns showing each of the weekly elevations.

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?

Hello!

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?

I am subtracting two times to get the difference, which I want to use in a
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

I would like to suggest that the calculation setting in Excel is changed so
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