Some EXCEL Spreadsheet Stuff

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:
to get parameters from various curve fits:

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)

and so on.
-----------------------------------