**A good cell format (enter into custom formats)**

##0.0E+0

This will give you engineering format:

1.0E+0

10.0E+0

100.0E+0

1.0E+3

10.0E+3

100.0E+3

1.0E+6

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

**Create sorted data on the fly**:

Period years | amplitude | Offset months | |

1 | 0.5012 | 0.036036 | 8.3 |

2 | 0.8395 | 0.006433 | 2.9 |

3 | 0.8910 | 0.011222 | 1.23 |

Data in array A1:D12

1st column is order required,

2nd column is "sorted" using =SMALL(A$1:A$12,z1) data to sort in A1 to A12 Z1 is order (1st col)

3rd ... nth col is data from rest of unsorted area. =VLOOKUP(AA1,A$1:D$12,n,FALSE) 'n' is the offset for required data. e.g amplitude n=2, offset months n=4

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

**Use "spinners" to increment decrement variables**

Find spinners in Developer tab

Then click Insert

Then click [spin button (form control)] 4th icon in top row

on sheet drag a rectangle for the button size

on new formed icon right click and choose form control

select the cell you want to change in "Cell Link"

The spin button will change this cell by +-1 every click from 0 to 30000

you then need to size this variable to suit e.g. subtract 15000 and you will get +-15000 range

You can adjust the increment in the form control to suit.

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

**A really useful smoothing algorithm**

Does not offset data as the built in trendline moving average

Does not lose peaks and troughs

Is free (although you can donate)

Hodrick Prescott filter from

http://www.web-reg.de/hp_addin.html

Simple to use but filter level is a bit random! (choose a level that removes as much noise as required)

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

**A really useful band pass filter**

seems well behaved

limited to around 4000 samples

Is free (although you can donate)

Band Pass filter from

http://www.web-reg.de/bp_addin.html

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

**Manually create a plot changing 1 variable**

e.g. you have monthly data for 150 years temperature. You want to produce a plot of average output from a band pass filter with a changing centre frequency (using the above filter)

You therefore need to set the filter frequency and obtain an amplitude output for desired bands.

Z1=centre frequency for band pass calc

J6:J77= output array

Set up a column with the desired centre frequencies A1 to An

in the plot data column B1 to Bn =IF(A1=Z$1,MAX(J$6:J$77),B1)

The plot data column will retain its original data if the corresponding 'An' column does not equal the frequency in Z1. When 'An'=Z1 then the plot data will update.

So you type into Z1 each 'An' value in turn.

You end up with a table which can be plotted

% opaque cloud cover | 3rd quart DLWIR as % of ULWIR water vapout content g/m^3 | 1st Quart DLWIR as % of ULWIR water vapout content g/m^3 | Results returned |

0 | #N/A | #N/A | 0 |

0.5 | #N/A | #N/A | 0 |

1 | #N/A | #N/A | 0 |

1.5 | 53.54 | 52.62 | 6 |

2 | 59.15 | 53.59 | 17 |

2.5 | 62.40 | 56.92 | 36 |

3 | 61.75 | 57.47 | 35 |

3.5 | 60.43 | 57.18 | 37 |

4 | 62.78 | 58.81 | 53 |

4.5 | 63.45 | 58.65 | 46 |

5 | 64.53 | 59.42 | 50 |

5.5 | 66.48 | 60.41 | 31 |

Col 1 is variable 2nd 3rd and 4th cols are derived data for the variable

(This can be automated!)

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

**Update the title of a graph automatically**

in a suitable cell e.g. A1 use concatenate to generate the title e.g.

=CONCATENATE("Temperature vs Abs Humidity "," - Op Cloud ",G18," to ",G19,"% - Months ",G6," to ",G7," Temp ",G9," to ",G10," - AH ",G12," to ",G13,"g/m^3 - hours ",G15," to ",G16)

Add TITLE to graph and in formula space type " =A1" no quotes!

in this example the title becomes:

Temperature vs Abs Humidity - Op Cloud -100000 to 9% - Months 5 to 5 Temp -100 to 100 - AH 0 to 99g/m^3 - hours 9 to 11 |

You cannot use concatenate in the title formula

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

From D. Appel

**Formulae made readable**by using 10^6 instead of 1e6 (which becomes to 1000000 in formulae)

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

**Vlookup and Hlookup**

2 really useful features but they suffer with a direct reference to a column number 'n'

VLOOKUP([search value],[array with 1st col containing search valoe],n,FALSE)

if you move or delete things the 'n' does not update requiring manual intervention.

Solution

name the columns

Then number the columns by using =column()

e.g. col 3 name = "current"

then select the numbers + title array

click formulas tab

click define names section [create from selection]

select only (in this case) bottom row then click [ok]

the cells in the number row will now be named as in the name row

3 | 4 | 5 | 6 |

current | Bleng | BBraid | Bdia |

Name the whole table to search also shows where the data is obtained this then gives the vlookup command as:

VLOOKUP([search value],sizedata,Bdia,FALSE) the array is "sizedata" and the column to return data is Bdia

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

**Plotting blanks**

**a really annoying "feature"**

if data in a table is invalid then it is a simple matter to put a conditional statement into the calculation that returns a cell showing no entry:

=if(a1=9999,"",a1*3) should return a blank if cell if a1=9999 or a1*3 if valid.

Unfortunately the plotting section does not recognise the cell contents as blank and will istead plot a value 0

Solution is to change the conditional statement to

=if(a1=9999,na(),a1*3)

invalid data will now show as N/A and will not plot.

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

**To change a cell with a click (double)**

first open a code page

right click a tab and click "view code"

in VB editor now open enter :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)

calc = Application.Calculation

Application.Calculation = xlManual

If Not Intersect(Target, Range(ActiveWorkbook.Names("axisx"))) Is Nothing Then

Range(ActiveWorkbook.Names("axisx")).ClearContents

ActiveCell.FormulaR1C1 = "x"

End If

Cancel = True

Application.Calculation = calc

End Sub

This turns off instant calculation (not needed if a simple sheet - lenghty calculation do not give a fast response to double click)

looks for a cell being clicked in an area named "axisx"

clears its contents then sets the cell clicked to "x"

(you an do anything here of course. BUT if you do not use names then VB will not keep track of the required clickable cell if it is moved)

At the end of the programme the calculation state is restored to its entry value.

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

**To average over cells containing blank results - result of a formula is blank**

For plotting change result to #n/a (see above)

then use

Averaging will return error

use

Averageif(a1:z1,"<>#n/a")

will then ignore the N/A cells

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

use trimmean to dispose of extremes and average rest

=TRIMMEAN(OFFSET(CH10,-$CB$6,0):OFFSET(CH10,$CB$6,0),$CB$7)

in this example cell CH10 is current active cell

$CB$6 contains a value that represents the number of cells above and below the active cell that should be includeed in the average

$CB$7 contains a number between 0 and 1 to select the % of results that are to be included in the disposal.

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

A bit of iteration

A sample:

cell C29 =IF(ISERROR(B51),1.8,IF(B51-A1>0.0005,C29+0.0001,IF(B51-A1<-0 .0005="">

cell B51 =IF(ISERROR(B51),1.8,ROUND(SUM(C49:H49),3))

Cell A1 contains the number to match

B51 is the matched value

C29 is used in some sums! results ending up in C49 to H49

Error trapping sets B51 to any valid value.

if B51>A1 by 0.0005 then C29 is increased by 0.0001

if B51

Iteration stops when ABS(B51-A51)<0 .0005="">Iteration starts when ABS(B51-A51)>0.0005

Also need to allow iteration in setup and allow enough steps and accuracy

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

Some curve fitting stuff:

Linear Trendline

Equation: y = m * x + b

m: =SLOPE(y1:y2,x1:x2)

b: =INTERCEPT(y1:y2,x1:x2)

Logarithmic Trendline

Equation: y = (c * LN(x)) - b

c: =INDEX(LINEST(y1:y2,LN(x1:x2)),1)

b: =INDEX(LINEST(y1:y2,LN(x1:x2)),1,2)

Power Trendline

Equation: y=c*x^b

c: =EXP(INDEX(LINEST(LN(y1:y2),LN(x1:x2),,),1,2))

b: =INDEX(LINEST(LN(y1:y2),LN(x1:x2),,),1)

Exponential Trendline

Equation: y = c *e ^(b * x)

c: =EXP(INDEX(LINEST(LN(y1:y2),x1:x2),1,2))

b: =INDEX(LINEST(LN(y1:y2),x1:x2),1)

2nd Order Polynomial Trendline

Equation: y = (c2 * x^2) + (c1 * x ^1) + b

C2: =INDEX(LINEST(y1:y2,x1:x2^{1,2}),1)

C1: =INDEX(LINEST(y1:y2,x1:x2^{1,2}),1,2)

b = =INDEX(LINEST(y1:y2,x1:x2^{1,2}),1,3)

3rd Order Polynomial Trendline

Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b

c3: =INDEX(LINEST(y1:y2,x1:x2^{1,2,3}),1)

c2: =INDEX(LINEST(y1:y2,x1:x2^{1,2,3}),1,2)

C1: =INDEX(LINEST(y1:y2,x1:x2^{1,2,3}),1,3)

b: =INDEX(LINEST(y1:y2,x1:x2^{1,2,3}),1,4)

C1: =INDEX(LINEST(y1:y2,x1:x2^{1,2}),1,2)

b = =INDEX(LINEST(y1:y2,x1:x2^{1,2}),1,3)

3rd Order Polynomial Trendline

Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b

c3: =INDEX(LINEST(y1:y2,x1:x2^{1,2,3}),1)

c2: =INDEX(LINEST(y1:y2,x1:x2^{1,2,3}),1,2)

C1: =INDEX(LINEST(y1:y2,x1:x2^{1,2,3}),1,3)

b: =INDEX(LINEST(y1:y2,x1:x2^{1,2,3}),1,4)

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

## No comments:

## Post a Comment