# Minor Point

Technical notes and solutions to common problems

### Calculating Inflation in Excel

Future value of 100 dollars at 3% inflation in 10 years = $74.41:

=100/(1+.03)^10

Formula to copy in cells to calculate inflated value:

Col A Col B

0 =100*(1+.03)^A1

=A1+1 =100*(1+.03)^A2

=A2+1 =100*(1+.03)^A3

=A3+1 =100*(1+.03)^A4

=A4+1 =100*(1+.03)^A5

=A5+1 =100*(1+.03)^A6

=A6+1 =100*(1+.03)^A7

=A7+1 =100*(1+.03)^A8

=A8+1 =100*(1+.03)^A9

=A9+1 =100*(1+.03)^A10

=A10+1 =100*(1+.03)^A11

=100/(1+.03)^10

Formula to copy in cells to calculate inflated value:

Col A Col B

**Year Inflated Value**0 =100*(1+.03)^A1

=A1+1 =100*(1+.03)^A2

=A2+1 =100*(1+.03)^A3

=A3+1 =100*(1+.03)^A4

=A4+1 =100*(1+.03)^A5

=A5+1 =100*(1+.03)^A6

=A6+1 =100*(1+.03)^A7

=A7+1 =100*(1+.03)^A8

=A8+1 =100*(1+.03)^A9

=A9+1 =100*(1+.03)^A10

=A10+1 =100*(1+.03)^A11

### Microsoft VBA Get Existing Outlook Instance

Sub Get_Existing_Outlook_Instance()

Dim objOutlook As Outlook.Application

' Gets the existing instance (if Outlook is already open) regardless of early or late binding

On Error Resume Next

Set objOutlook = GetObject(, "Outlook.Application")

If objOutlook Is Nothing Then

Debug.Print "Outlook is not open, getting a new instance"

Set objOutlook = New Outlook.Application

End If

Set objOutlook = Nothing

End Sub

Dim objOutlook As Outlook.Application

' Gets the existing instance (if Outlook is already open) regardless of early or late binding

On Error Resume Next

Set objOutlook = GetObject(, "Outlook.Application")

If objOutlook Is Nothing Then

Debug.Print "Outlook is not open, getting a new instance"

Set objOutlook = New Outlook.Application

End If

Set objOutlook = Nothing

End Sub

### Microsift VBA Early vs. Late Binding Example

Private Sub Early_vs_Late_Binding_Example()

#Const LATEBINDING = False

#If LATEBINDING Then

Dim objOutlook As Object

Set objOutlook = CreateObject("Outlook.Application")

#Else

' NOTE! Early binding requires a typelib reference. In the VBE click Tools -> References

' and make sure that "Microsoft Outlook xx.x Object Library" is selected

Dim objOutlook As Outlook.Application

Set objOutlook = New Outlook.Application

#End If

Set objOutlook = Nothing

End Sub

### Move Ubuntu Unity Launcher to the Bottom of the Screen

gsettings set com.canonical.Unity.Launcher launcher-position Bottom

To reset to the left side:

gsettings set com.canonical.Unity.Launcher launcher-position Left

To reset to the left side:

gsettings set com.canonical.Unity.Launcher launcher-position Left

### Changing the window placement in Ubuntu

To see the current setting:

dconf read /org/compiz/profiles/unity/plugins/place/mode

To update the setting:

dconf write /org/compiz/profiles/unity/plugins/place/mode 1

(Try different values to understand the behavior.)

dconf read /org/compiz/profiles/unity/plugins/place/mode

To update the setting:

dconf write /org/compiz/profiles/unity/plugins/place/mode 1

(Try different values to understand the behavior.)

### Restoring the default login screen background in Ubuntu Xenial

Changing the desktop wallpaper also changed the login screen. Follow these steps to override that behavior and reset the login screen back to the default.

You can also remove the white dots by changing this key node:

cd /usr/share/glib-2.0/schemas/Look for this key node:

sudo vi com.canonical.unity-greeter.gschema.xml

<key name="draw-user-backgrounds" type="b">Change the value true to false.

<default>true</default>

<summary>Whether to draw user backgrounds</summary>

</key>

You can also remove the white dots by changing this key node:

<key name="draw-grid" type="b">Now recompile the schema with this command:

<default>true</default>

<summary>Whether to draw an overlay grid</summary>

</key>

sudo glib-compile-schemas /usr/share/glib-2.0/schemas/Log out and back in to see the change.

### Ubuntu Unity Configurations

To position the launcher at the bottom of the screen:

gsettings set com.canonical.Unity.Launcher launcher-position Bottom

To return to the default position on the left:

gsettings set com.canonical.Unity.Launcher launcher-position Left

To open new windows centered on the screen without installing compiz-conf:

dconf write /org/compiz/profiles/unity/plugins/place/mode 1

gsettings set com.canonical.Unity.Launcher launcher-position Bottom

To return to the default position on the left:

gsettings set com.canonical.Unity.Launcher launcher-position Left

To open new windows centered on the screen without installing compiz-conf:

dconf write /org/compiz/profiles/unity/plugins/place/mode 1

### Calculating Percentage Change

- Method 1: (new - old) / old
- Method 2: (new / old) - 1

Old value | 100 | |

New value | 50 | |

Method 1 | =(B2-B1)/B1 | -50.00% |

Method 2 | B2/B1-1 | -50.00% |

### Getting a list of stock symbols

Lists of stock symbols can be found at either of these links:

You can download a list of symbols from the nasdaq.com site for the Nasdaq, NYSE, and AMEX exchanges.

The FTP directory at ftp.nasdaqtrader.com gets updated every night at 3:00AM (symbols can change). Here is a quick Python script to download the nasdaqlisted.txt and otherlisted.txt files from the ftp site:

#!/usr/bin/python

# typical use: python get_symbols.py > symbols.txt

import urllib

def get_data( url ):

data = urllib.urlopen( url )

lines = []

for line in data:

lines.append( line.rstrip().split('|') )

return lines

url = 'ftp://ftp.nasdaqtrader.com/SymbolDirectory/'

files = ['nasdaqlisted.txt', 'otherlisted.txt']

for f in files:

print get_data( url + f )

You can download a list of symbols from the nasdaq.com site for the Nasdaq, NYSE, and AMEX exchanges.

The FTP directory at ftp.nasdaqtrader.com gets updated every night at 3:00AM (symbols can change). Here is a quick Python script to download the nasdaqlisted.txt and otherlisted.txt files from the ftp site:

#!/usr/bin/python

# typical use: python get_symbols.py > symbols.txt

import urllib

def get_data( url ):

data = urllib.urlopen( url )

lines = []

for line in data:

lines.append( line.rstrip().split('|') )

return lines

url = 'ftp://ftp.nasdaqtrader.com/SymbolDirectory/'

files = ['nasdaqlisted.txt', 'otherlisted.txt']

for f in files:

print get_data( url + f )

### List Comprehensions in Python

```
# create a new list
```

`>>> a = [1,2,3,4,5]`

```
>>> a
[1, 2, 3, 4, 5]
```

`# sample list comprehension expression; equivalent to b = a`

`>>> b = [a[i] for i in range(len(a))]`

```
>>> b
[1, 2, 3, 4, 5]
```

`# only even indices`

`>>> b = [a[i] for i in range(len(a)) if i % 2 == 0]`

>>> b [1, 3, 5]

# only even indices and conditionally modifies the value of a[i]

>>> b = [a[i]*2 if i > 1 else a[i] for i in range(len(a)) if i % 2 == 0]

>>> b [1, 6, 10]

### Deck of Cards Exercises

The

For each suit, perform a certain exercise:

Use the value of the card as the number of repetitions:

*Deck of Cards*exercises.For each suit, perform a certain exercise:

- Clubs = burpees
- Hearts = pushups
- Spades = jumping jacks
- Diamonds = sit ups

Use the value of the card as the number of repetitions:

- 2-10 = number of reps on the card
- Jack = 11 reps
- Queen = 12 reps
- King = 13 reps
- Ace = 15 reps
- Joker = 30/60/90 second rest -OR- 10/15/20 reps of all exercises

### Implementing ATAN2

An implementation of ATAN2 in PHP. PHP implements ATAN2 but I needed to figure out the algorithm for a microcontroller that didn't, so I prototyped it in PHP.

Here is the code:

Here is the code:

class Math { public static function atan2( $dy, $dx ) { if( $dy > 0 ) { if( $dx > 0 ) $tcl = atan($dy/$dx); if( $dx < 0 ) $tcl = M_PI - atan(-$dy/$dx); if( $dx == 0 ) $tcl = M_PI/2; } if( $dy < 0 ) { if( $dx > 0 ) $tcl = -atan(-$dy/$dx); if( $dx < 0 ) $tcl = atan($dy/$dx) - M_PI; if( $dx == 0 ) $tcl = -M_PI/2; } if( $dy == 0 ) { if( $dx > 0 ) $tcl = 0.0; if( $dx < 0 ) $tcl = M_PI; if( $dx == 0 ) $tcl = 0.0; // the 2 points are the same, default to zero } return $tcl; } }And here is an example of how to use it:

$heading = 270.0; // degrees $Ax = 0.00; $Ay = 0.00; $Bx = -7.89; $By = -0.86; // get target distance and bearing in degrees $dx = $Bx-$Ax; $dy = $By-$Ay; $dist = sqrt( pow($dx,2) + pow($dy,2) ); $dir = Math::atan2( $dy, $dx ) * 180 / M_PI; printf( "Target spotted at %0.3f degrees, distance %0.3f !!\n", $dir, $dist ); // get new heading $diff = $heading - $dir; if( $diff < -180 ) $diff += 360; if( $diff > 180 ) $diff -= 360; printf( "Turn %s %0.3f degrees\n", ($diff < 0 ? 'left' : 'right'), $diff );

### Implementing Logic Gates in a Spreadsheet

Here are some of the basic logic gates, implemented as spreadsheet formulas.

It is possible to implement any gate using only NOT and OR (NOR), or with NOT and AND (NAND) gates. For example, here is an AND gate implemented using only NOT and OR gates:

With AND, OR and NOT, we can implement a half adder, like this:

By string together two half adders and OR-ing the carry bit, we can create a full adder:

Implementing a 4-bit adder is just a matter of simply replicating the full adder logic four times:

Gate | Spreadsheet Formula |
---|---|

AND | AND(A,B) |

OR | OR(A,B) |

NOT | NOT(B) |

NAND | NOT(AND(A,B)) |

NOR | NOT(OR(A,B)) |

XOR | OR(AND(A,NOT(B)),AND(NOT(A),B)) |

XNOR | NOT(OR(NOT(OR(A,NOT(OR(A,B)))),NOT(OR(B,NOT(OR(A,B)))))) |

It is possible to implement any gate using only NOT and OR (NOR), or with NOT and AND (NAND) gates. For example, here is an AND gate implemented using only NOT and OR gates:

AND using only NOR gates | NOT(OR(NOT(OR(A,A)),NOT(OR(B,B))) |

With AND, OR and NOT, we can implement a half adder, like this:

Column A | Column B |
---|---|

A | 0 |

B | 1 |

Carry (AND) | =AND(B1,B2) |

Sum (XOR) | =OR(AND(B1,NOT(B2)),AND(NOT(B1),B2)) |

By string together two half adders and OR-ing the carry bit, we can create a full adder:

Bit | Spreadsheet Formula |
---|---|

A | 0 |

B | 0 |

Cin | 0 |

Cout | =AND(B2,B3) |

Sum | =OR(AND(B2,NOT(B3)),AND(NOT(B2),B3)) |

Cout | =AND(B6,B4) |

Sum | =OR(AND(B4,NOT(B6)),AND(NOT(B4),B6)) |

Cout | =OR(B7,B5) |

Sum | =B8 |

Implementing a 4-bit adder is just a matter of simply replicating the full adder logic four times:

8 | 4 | 2 | 1 | |
---|---|---|---|---|

Input A | 0 | 0 | 0 | 0 |

Input B | 0 | 0 | 0 | 0 |

A | =B2 | =C2 | =D2 | =E2 |

B | =B3 | =C3 | =D3 | =E3 |

Cin | =C12 | =D12 | =E12 | 0 |

Cout | =AND(B5,B6) | =AND(C5,C6) | =AND(D5,D6) | =AND(E5,E6) |

Sum | =OR(AND(B5,NOT(B6)),AND(NOT(B5),B6)) | =OR(AND(C5,NOT(C6)),AND(NOT(C5),C6)) | =OR(AND(D5,NOT(D6)),AND(NOT(D5),D6)) | =OR(AND(E5,NOT(E6)),AND(NOT(E5),E6)) |

Cout | =AND(B7,B9) | =AND(C7,C9) | =AND(D7,D9) | =AND(E7,E9) |

Sum | =OR(AND(B7,NOT(B9)),AND(NOT(B7),B9)) | =OR(AND(C7,NOT(C9)),AND(NOT(C7),C9)) | =OR(AND(D7,NOT(D9)),AND(NOT(D7),D9)) | =OR(AND(E7,NOT(E9)),AND(NOT(E7),E9)) |

Cout | =OR(B8,B10) | =OR(C8,C10) | =OR(D8,D10) | =OR(E8,E10) |

Sum | =B11 | =C11 | =D11 | =E11 |

### Radians made easy

If you are in danger of losing your Geek cred because you don't understand radians, fear not! Radians aren't bad, they're just drawn that way.

You already know some things about degrees, like there are 360 degrees in a circle and that you can measure an angle with two points on the circle's edge (see Diagram A).

Radians are just another way to measure angles. Here's how:

Start with a circle. The radius of the circle is a line from the center of the circle to a point on the edge. In Diagram A, that's the line CA. Start point A moving counterclockwise around the circle. It will trace out an arc as it travels. When the arc is as long as the radius of the circle, stop. Call that point B. You now have an arc AB, and an angle ACB. That angle at C is equivalent to one radian. So, the length of arc AB is the same as the length of the radius of the circle, which is line CA. Here's the rule:

Since C = 2πr, and since we already said the radius of the circle equals one radian, setting r = 1 means that C = 2π radians. In other words, there are 2π radians in the circumference of the circle, which is 360 degrees. So, 360 degrees = 2π radians. Knowing that, we can now convert between radians and degrees.

Since there are 2π radians in 360 degrees, we get: 2π rad = 360 deg. Diving both sides by 2π, rad = 360/2π = 180/π. And, because 360 deg = 2π rad: dividing both sides by 360, deg = 2π/360 = π/180. To summarize:

You'll see charts that tell you 90 degrees equals π/2 radians, or 315 degrees equals 7π/4 radians. How did they get that?

Remember that C = 2π, meaning that there are 2π radians in 360 degrees. To find out how many radians are in, for example, 90 degrees, we just multiply 2π by the ratio 90/360, like this:

radians = 90/360 × 2π.

Reducing the fraction, we get ¼ × 2π, which simplifies to 2π/4, or π/2. So 90 degrees = π/2 radians.

Let's do a few more:

180 degrees = 180/360 × 2π = ½ × 2π = 2π/2 = π radians.

270 degrees = 270/360 × 2π = ¾ × 2π = 6π/4 = 3π/2 radians.

360 degrees = 360/360 × 2π = 2π radians.

Try the calculations for yourself! 45, 135, 225, and 315 degrees are all common angles.

**What is a radian?**Diagram A |

Radians are just another way to measure angles. Here's how:

Start with a circle. The radius of the circle is a line from the center of the circle to a point on the edge. In Diagram A, that's the line CA. Start point A moving counterclockwise around the circle. It will trace out an arc as it travels. When the arc is as long as the radius of the circle, stop. Call that point B. You now have an arc AB, and an angle ACB. That angle at C is equivalent to one radian. So, the length of arc AB is the same as the length of the radius of the circle, which is line CA. Here's the rule:

*The lengths of the arc and the radius are equal.*

Since C = 2πr, and since we already said the radius of the circle equals one radian, setting r = 1 means that C = 2π radians. In other words, there are 2π radians in the circumference of the circle, which is 360 degrees. So, 360 degrees = 2π radians. Knowing that, we can now convert between radians and degrees.

Since there are 2π radians in 360 degrees, we get: 2π rad = 360 deg. Diving both sides by 2π, rad = 360/2π = 180/π. And, because 360 deg = 2π rad: dividing both sides by 360, deg = 2π/360 = π/180. To summarize:

*Given degrees, you get radians with rad = deg*×*180/π.**Given radians, you get degrees = rad*×*π/180.*

**Radians for common degrees**You'll see charts that tell you 90 degrees equals π/2 radians, or 315 degrees equals 7π/4 radians. How did they get that?

Remember that C = 2π, meaning that there are 2π radians in 360 degrees. To find out how many radians are in, for example, 90 degrees, we just multiply 2π by the ratio 90/360, like this:

radians = 90/360 × 2π.

Reducing the fraction, we get ¼ × 2π, which simplifies to 2π/4, or π/2. So 90 degrees = π/2 radians.

Let's do a few more:

180 degrees = 180/360 × 2π = ½ × 2π = 2π/2 = π radians.

270 degrees = 270/360 × 2π = ¾ × 2π = 6π/4 = 3π/2 radians.

360 degrees = 360/360 × 2π = 2π radians.

Try the calculations for yourself! 45, 135, 225, and 315 degrees are all common angles.

### Cheaper Gas Calculator (spreadsheet)

Is is worth driving to a more distant gas station to buy gas that's slightly cheaper? Here's a spreadsheet to find out. Just copy and paste into cell A1 in your favorite spreadsheet program, then enter your own data in cells A1 through A6. Your savings in cells A14 and A15 should be a positive number to make the trip worth while.

In cells A5 and A6 just put the one-way distance to the away station. The spreadsheet automatically calculates the round-trip cost.

Tested in Excel, OpenOffice Calc and Gnumeric.

In cells A5 and A6 just put the one-way distance to the away station. The spreadsheet automatically calculates the round-trip cost.

Tested in Excel, OpenOffice Calc and Gnumeric.

15 | tank size (gallons) |

29.55 | miles per gallon |

3.619 | local price per gallon |

3.599 | away price per gallon |

2.00 | miles to local station (one way) |

5.00 | miles to away station (one way) |

=A1*A3 | cost to fill up at local station |

=A1*A4 | cost to fill up at away station |

=A8-A9 | savings from cheaper gas |

=(A6-A5)*2 | extra round trip distance to away station |

=A12/A2 | extra gallons to drive to cheaper station and back |

=A13/A3 | extra travel cost to away station (based on local gas price) |

=A10-A14 | savings (loss) |

=A16/A1 | savings (loss) per gallon |

### Highlighting rows in Excel having specific text

We have an Excel spreadsheet and we'd like to somehow highlight cells containing specific text. For example, we have the following data in cells A1 through A10 and we want to highlight any row containing the letter 'F', in this case cells A3, A5 and A8.

ABC

BCD

DEF

GHI

EFG

JKL

CDE

FGH

KLM

HIJ

First we need a formula that allows us to identify whether a cell contains the target text. We can use the Search() function, like this: =SEARCH("F",A1). Search returns the position of the first letter in the search string if it is found, otherwise it returns a #Value error. This works well, but we are not interested in the position of the text, just whether or not it is found. We also need to deal with the #Value error when the text is not found. Here's how:

=NOT(ISERROR(SEARCH("F",A1)))

This formula will return TRUE if the text is found, or FALSE if it is not. Now all we need is a way to highlight the cells containing the search text. Enter conditional formatting!

Move the cursor to cell A1 and hit Formatting --> Contitional formatting in the menu. Change the dropdown from "Cell value is" to "Formula is". Then paste your formula in the formula box and set the background to red. This will have the effect of setting the cell background to red if the cell contains the text we are looking for. Hit OK to save.

Now hit the paintbrush button in the toolbar to copy this format to all the other cells. You should see the cells with the letter F highlighted in red.

ABC

BCD

DEF

GHI

EFG

JKL

CDE

FGH

KLM

HIJ

First we need a formula that allows us to identify whether a cell contains the target text. We can use the Search() function, like this: =SEARCH("F",A1). Search returns the position of the first letter in the search string if it is found, otherwise it returns a #Value error. This works well, but we are not interested in the position of the text, just whether or not it is found. We also need to deal with the #Value error when the text is not found. Here's how:

=NOT(ISERROR(SEARCH("F",A1)))

This formula will return TRUE if the text is found, or FALSE if it is not. Now all we need is a way to highlight the cells containing the search text. Enter conditional formatting!

Move the cursor to cell A1 and hit Formatting --> Contitional formatting in the menu. Change the dropdown from "Cell value is" to "Formula is". Then paste your formula in the formula box and set the background to red. This will have the effect of setting the cell background to red if the cell contains the text we are looking for. Hit OK to save.

Now hit the paintbrush button in the toolbar to copy this format to all the other cells. You should see the cells with the letter F highlighted in red.

### Detecting Dates in Microsoft Excel Spreadsheets

Microsoft Excel does not have an "isdate()" function, making it hard to determine whether a cell contains a date.

Excel VBA provides a function, but a formulaic solution also exists. The trick is to realize that:

Use the IsNumber() function to determine whether or not the value contained in a cell is a number, like this: =ISNUMBER(A1).

Use the Cell() function to get the format of a cell, like this: =CELL("format",A1).

If Cell() returns a result in the range "D1" to "D5", the target cell -- in this case A1 -- is formatted as a date. Therefore, we can write: =OR(CELL("format",A6)={"D1","D2","D3","D4","D5"}).

Or, more compactly: =LEFT(CELL("format",A6),1)="D".

Now we just need to combine the two formulas with an And(), like this:

=IF(AND(ISNUMBER(A5),LEFT(CELL("format",A5),1)="D"),1,0).

This formula returns one if the cell is a number and it is formatted as a date, otherwise it returns a zero.

Excel VBA provides a function, but a formulaic solution also exists. The trick is to realize that:

- Dates are stored as numbers in Excel, and
- You can use the Cell() function to return the format of a cell.

- The cell contains a number, and
- Is formatted as a date.

Use the IsNumber() function to determine whether or not the value contained in a cell is a number, like this: =ISNUMBER(A1).

Use the Cell() function to get the format of a cell, like this: =CELL("format",A1).

If Cell() returns a result in the range "D1" to "D5", the target cell -- in this case A1 -- is formatted as a date. Therefore, we can write: =OR(CELL("format",A6)={"D1","D2","D3","D4","D5"}).

Or, more compactly: =LEFT(CELL("format",A6),1)="D".

Now we just need to combine the two formulas with an And(), like this:

=IF(AND(ISNUMBER(A5),LEFT(CELL("format",A5),1)="D"),1,0).

This formula returns one if the cell is a number and it is formatted as a date, otherwise it returns a zero.

### Anonymous Inner (Lambda) Functions with PHP

`<?php`

$str = "hello world!";

/* 1. won't work with with anonymous inner (lambda) function before PHP 5.3

$str = preg_replace_callback(

'/world/',

function( '$match', 'return "michael";' ),

$str ); */

`/* 2. use create_function instead */`

$str = preg_replace_callback(

'/world/',

create_function( '$match', 'return "michael";' ),

$str );

`echo $str ; // output: hello michael!`

?>

### Reinstalling a LAMP stack on Ubuntu

This is to reinstall a LAMP stack on Ubuntu Jaunty, but it should work for pretty much any version.

Shut down Apache:

Shut down Apache:

sudo /etc/init.d/apache2 stop

Shut down MySQL:sudo /etc/init.d/mysql stop

Remove the entire LAMP stack:sudo apt-get purge apache2 php5-mysql libapache2-mod-php5 mysql-server php5 phpmyadmin

- Note the use of
*purge*instead of*remove*-- this gets rid of configuration files as well. - You will need to download everything from the archives during the reinstall.
- Stuff in /var/www will
__not__be removed.

sudo apt-get install apache2 php5-mysql libapache2-mod-php5 mysql-server php5 phpmyadmin

### How to find the last used row in a spreadsheet without using VB

I frequently copy spreadsheets between Microsoft Excel and Open Office Calc and I sometimes have a need to find the last row being used in a spreadsheet. But since Open Office does not support Visual Basic for Applications (VBA), I needed a formulaic approach.

Here is the formula: {=MAX(NOT(ISBLANK($A$1:$A$65536))*ROW($A$1:$A$65536))}

This is an

Here is the formula: {=MAX(NOT(ISBLANK($A$1:$A$65536))*ROW($A$1:$A$65536))}

This is an

*array formula*, meaning, it will iterate over a range automatically and evaluate the formula for each cell in the range. (NOTE: Don't forget to press CTRL-SHIFT-ENTER to save it after you are done editing the cell, otherwise it won't be saved as an array formula!)**How does it work?**- The array formula starts at cell A1 and works its way down to cell A65536. Along the way it performs ISBLANK() on each cell it encounters. ISBLANK() evaluates to
*true*for each blank (empty) cell. - Surrounding that, we have a NOT() function, which reverses the result from ISBLANK giving us
*false*for blank cells and*true*for non-blank cells. We now have a list of which cells are blank and which are non-blank. - We then multiply that result by the ROW() number of each cell in the range, which is the same row number you see down the left side of the spreadsheet. Since
*false*is equivalent to zero and*true*is equivalent to one, this has the effect of multiplying the row number for each blank cell by zero and each non-blank cell by one. We now have a value of zero for each blank cell, and the value of the row number for each non-blank cell. - Finally, we take the MAX() of the list, which gives us the largest number in the list. The largest number will be the last row used in the sheet.

### Protect your email address with javascript

Spam is the bane of mankind's existence. Although modern spam filters do a good job of keeping your inbox clean, you can take some additional steps to protect yourself. One is to prevent your email address from being harvested from your web site. Spammers have crawlers that troll through web pages looking for the pattern name@site.com, or something similar.

If you want to put an email contact on your web site, but also want to avoid spammers, you can use JavaScript. Here's the code. Copy it into the body of your contact web page, then change the name and site.

If you want to put an email contact on your web site, but also want to avoid spammers, you can use JavaScript. Here's the code. Copy it into the body of your contact web page, then change the name and site.

<html>

<head>

<title>How to protect your email address</title>

</head>

<body>

<script type='text/javascript'>

e = 'name' + '@' + 'site.com';

document.write( "<a href='mailto:" + e + "'>" + e + '</a>' );

</script>

<noscript>Please turn on JavaScript!</noscript>

</body>

</html>

### IE tip - use the Explorer view to organize your bookmarks

To show all your favorites in an Explorer view:

- Go to Favorites --> Organize Favorites in the menu.
- Hold the shift key down when clicking on Organize Favorites.

### Bookmarklet for Creating an Amazon Affiliate Link

Bloggers who run Amazon affiliate sites know how difficult it can be to add Amazon product links to your site. A key problem is the sheer length of the link url.

Fortunately, Amazon supports a considerably shorter format url, having the form: http://www.amazon.com/dp/ProductID/?tag=YourAssociateID. This is helpful, but you still need to construct the link, then copy/paste it into your page.

I have created a bookmarklet to help. Drag Make Amazon Link to your bookmarks toolbar to install it, then right-click, select "properties" and change the 'YourAssociateID' text to your actual associate id. That's it!

You can check the link with Amazon's link checker to be sure everything will work correctly.

Here is the bookmarklet code in a more readable format for those who might be interested:

javascript:

var t='YourAssociateID',

h=document.location.href,

p=h.split('/')[5];

(function(){

w=window.open('','','width=600,height=400');

d=w.document;

d.writeln('The Amazon.com link is:'+h+'

Fortunately, Amazon supports a considerably shorter format url, having the form: http://www.amazon.com/dp/ProductID/?tag=YourAssociateID. This is helpful, but you still need to construct the link, then copy/paste it into your page.

I have created a bookmarklet to help. Drag Make Amazon Link to your bookmarks toolbar to install it, then right-click, select "properties" and change the 'YourAssociateID' text to your actual associate id. That's it!

You can check the link with Amazon's link checker to be sure everything will work correctly.

Here is the bookmarklet code in a more readable format for those who might be interested:

javascript:

var t='YourAssociateID',

h=document.location.href,

p=h.split('/')[5];

(function(){

w=window.open('','','width=600,height=400');

d=w.document;

d.writeln('The Amazon.com link is:'+h+'

');

d.writeln('Your short affiliate link is:

http://www.amazon.com/dp/'+p+'/?tag='+t);

})();

Subscribe to:
Posts (Atom)