Excel - countif greater than a value

=COUNTIF(AC2:AC9480,">31")

Excel - month and weekday from date

For month (or day), formula is =MONTH (A1) [or =DAY(A1)]

For weekday name, the formula is =WEEKDAY(A1) then use custom format of DDD to show the day name

Excel - fill in blank cells in row area in pivot tables

From http://www.mrexcel.com/articles/fill-pivot-blank-cells.php

Trick #1. Selecting all of the blank cells in that range.

• Hit Ctrl+G, Alt+S, K and then enter. huh?
• Ctrl G brings up the GoTo dialog
• Alt+S will pick the "Special" button from the dialog box
• The Goto-Special dialog is an awesome thing that few know about. Hit "k" to pick "blanks". Hit enter or click OK and you will now have selected just all of the blank cells in the pivot table outline columns. These are all of the cells which you want to fill in.

Trick #2.

Hit the equals key. Hit the Up arrow. Hold down Ctrl and hit enter. Hitting equals and the up arrow says, "I want this cell to be just like the cell above me." Holding down Ctrl when you hit enter says, "Enter this same formula in every selected cell, which, thanks to Trick #1 is all of the blank cells which we wanted to fill in.

Trick #3.

You now need to change all of those formulas to values. Select all of the cells in A3:B999 again, not just the blanks. Hit ctrl+C to copy this range. Hit alt+e then sv (enter). to Paste Special Values these formulas.

Mac: Cleaning the Mighty Mouse

Over a period of time, the mighty mouse was far less inclined to scroll upwards. Down was fine, but up not.

My usual trick of pushing the mouse button down and moving it around was doing nothing, so I Googled this article, which - although fiddly - worked and is still working now!



http://www.linklink.co.uk/apple-computers/cleaning-inside-and-dismantling-the-apple-mighty-mouse/

STEP ONE
You need to open the mouse. Don’t panic, it is pretty easy. You DO have to break the glued seal, but it’s non-destructive and mine looks like it did from the factory now it’s back together.
In Fig 1, I’ve circled the start point. It’s near the cable end, and you need to be prising the ’skirt’ off. Insert the blade here and you’ll hear a crack! Work the blade gently all around. The skirt should come off now. Put it safely to one side.

STEP TWO
Near the cable end are two ‘tabs’ which hold the outer shell to the base. I found that by gently flexing the tab inwards towards the innards of the mouse, I was able to free them easily. (tab shown if Fig 2). The outer shell is now released at one end and pivots on two lugs at the opposite end (fig 3) and these lugs can be easily released by gently flexing the outer shell open at the pivot points. The two halves now come apart and are held only by two VERY delicate ribbon cables (fig 4). I didn’t find it necessary to remove the ribbon cables, as in past experience it’s tricky to put them back!

STEP THREE
Inside, you’ll see the little black housing which holds the ball, secured by three small screws. Undo them, and put them safe. (fig 5).
In Fig 6, you can see the ball housing. I marked with little black dots the orientaton of the little magnetic rollers inside. This will help when putting it back together. Also, mark the white plastic retainer AND the black housing together (fig 7) so you know which way to put them back together!

STEP FOUR
Carefully remove the white retainer by lifting the end shown in Fig 7, nearst the ribbon cable. The white retainer comes free. With it should come the ball and four little white pins with black collars—these are the magnetic rollers which feed the data to the mouse as to which direction is being scrolled. They are also the dirty bits


STEP FIVE
Dismantle the little unit, remembering the orientation of the little rollers (the diagram should help!). Fig 9 shows a really dirty roller! Now clean the ball using alcohol and the lint rag.
Carefully clean the little rollers with cotton buds.

STEP SIX
Once the ball and rollers are nice and clean I blew all crud out of the inside of the mouse with an air duster (a compressed air tin) and used a small pointed tool to take all the muck out of the white retainer (fig 10).

STEP SEVEN
Re-assembly is fiddly… first put the ball back in the retainer.Hold the retainer as in Fig 8 and re-insert the rollers. You can’t put the assembly down flat from now on, as it will fall apart. See in the photo fig 8 that the black marked part of the white retainer is facing outward. (i’ve circled it on the picture).
Next, keeping the white retainer assembly upright, bring over the black part and offer it down over the white retainer assembly, with the black mark you made nearest the ribbon cable.
The whole thing goes together with a simple click of pressure. Turn it over and make sure it all looks OK, and that all the little black wheels are the same orientation as Fig 6.
Being careful to make sure the ribbon cable isn’t twisted, screw the ball assembly back into the upper shell.

STEP EIGHT
Keeping an eye on the ribbon cables, bring the outer and lower shell back together, starting with the pivot lugs. They snap back in really easily.
Once they are in, click the upper tabs back in, making sure the USB cable retainer is oriented back in it’s hole.

STEP NINE
Nearly there… place the mouse facing upward and get the skirt ready by placing it face down (with the little tabs facing up) in a clear area.
You now need to dab VERY SMALL dots of superglue on it. I’ve circled the dots I did in Fig 11… they are basically in the same place as the original glue.
Bring the mouse body over and lower it onto the skirt, making sure the USB cable is at the right end.
Press down a few seconds and let the glue take, then lift it all up and rubber band it tightly and leave for a while.

Excel: Convert forced text to numbers

Enter the number 1 in a cell somewhere away from the range of numeric values. Copy that cell to the Clipboard. Highlight the entire range of text numbers and select Paste Special from the Edit menu. Check the box titled Multiply, then click on OK. This will replace the value of each cell in the range with each value multiplied by 1.
If the cell held a text number, it will be converted to an actual number. Cells containing non numeric text will not be changed. Note that you can use the same technique to multiply, divide, add, or subtract the values in the target range by the value in the clipboard. For example, you can use this method to raise or lower a list of prices by 10 percent.

Excel: Row based page breaks

Suppose you want page breaks every 40 lines. Select the first few cells in the 40th line and change the background color, so it will stand out even when reduced in size. From the File menu, select Print Preview and note where your 40th line falls on the page. Click the Setup button and increase the bottom margin by an inch or two, then click OK. Note where the marked line falls. Go into Setup as many times as needed, adjusting the bottom margin until the marked line is the last line on the page. Click the Page Break Preview button to view your worksheet with all page breaks marked and scan through it to verify that the data is laid out as you wanted.

Excel: Highlight formula cells

First, select the entire range of cells and apply the format you want to use for cells without formulas. With the range still selected, choose Go To from the Edit menu and click on the Special button. In the Go To Special dialog, check the Formulas box, and click on OK. All cells containing formulas will be highlighted, regardless of whether they form a continuous area. Right-click on any one of them, choose Format Cells, and apply the desired formatting. You must repeat this process if you change the contents of the cells.

Excel: Find unique rows

Problem: Each row contains info in a consistent format and layout for a record in a database, with a unique ID in the first column.

Resolution: Assuming the data starts in cell A1, click in that cell and choose Format | Conditional Formatting from the menu. Change the drop-down to Formula is. Enter the formula =COUNTIF(A:A,A2)=1. Click the Format button, click the Pattern tab, and select a color, say, green. Click OK, OK. Now click the Format Painter button on the toolbar and highlight all the rest of the data in column A. Any ID that appears only once will now be green.

In Excel 2007, highlight all the cells in the unique ID column and click the big Conditional Formatting button on the ribbon. Select Highlight Cells Rules and choose Duplicate Values. In the little window that appears, change Duplicate to Unique and select the desired formatting.

Excel: Fill functions

CUSTOM LISTS
As with the months of the year and days of the week, you can create your own custom fill lists. Click the Office button and choose Excel Options | Popular | Edit Custom Lists to display the Custom List dialog. You can copy a list from a range on the worksheet or click New List and type a list of entries in the aptly named List entries box, one per line. Click Add to add the list and OK twice to finish. Now type any entry from your list in a cell and drag the fill handle to create the sequence.

MORE-COMPLEX SERIES
To create a nonlinear series, type the first value in the series, right-click, and drag on the fill handle. When the Context menu appears, select Series to open the Series dialog. Here you specify a Growth series, for example. To test this, type number 1 in a cell, right-click it and drag, choose Series | Growth and set the step value to 2, and click OK. This creates a sequence in which each number is followed by itself multiplied by two.

DROP-DOWN EASE
A drop-down list is handy when you have a set of options you want your user to choose from—and it conveniently averts typos. On a new sheet, type a list of entries, select it, and type a name for the list in the Names box on the left of the Formula bar. On a new worksheet, select the cells in which the data is to appear. Click the Data tab on the Ribbon and choose Data Validation. From the Settings tab choose Allow | List and in the Source box type = and the name you gave to the selected list (for example, =employees). Click OK. Now when you click a cell you can select the cell entry from the drop-down list.

FORMS
The Form button is not on the Excel 2007 Ribbon, but you can add it to the Quick Launch bar. Click the Office button, choose Excel Options | Customize, and in the Choose commands from list pick Commands Not in the Ribbon. Locate and select the Form command. Click Add and then OK. To display the form, click in a table of data and click the Form button. You can now enter data into your table using the Form dialog, which is quicker and more convenient than navigating around a very large table.

Excel: Enter date and time formats

To set up a worksheet so that you can enter dates as MMDDYYYY with no slashes between, first click the column header for the column that will contain the dates. Now right-click the column header and select Format Cells from the pop-up menu. On the Number tab choose Custom and enter the following custom format in the Type box: 00\/00\/0000. The forward slash has special meaning in a custom format, so you must precede it with a backslash to tell Excel that you want the literal character, not its special meaning.
Now insert a column just after this column to hold the actual date for use in calculations. Let's say the eight-digit dates were in column A, starting in cell A2. Then you would enter this formula in cell B2 and copy it down the whole column: =DATE(MOD(A2, 10000), TRUNC(A2 / 1000000), MOD(TRUNC(A2 / 10000), 100)). The MOD function returns the remainder when dividing its first argument by its second. Thus the first sub-expression returns the last four digits of your number—the year. For the month, we divide by 1,000,000 and use the TRUNC function to throw away any fractional part. And for the day, we do a combination of the two.
To enter HH:MM time values without the colon, you'll simply use a different custom format. The format 0\:00 will give one- or two-digit hours, while the format 00:\00 will always give you two-digit hours, left-padding with a zero if necessary. A formula like this one would convert the typed value into an actual time value for use in calculations: =TIME(TRUNC(A2/100),MOD(A2,100),0).

Recovering from virus / malware

Foolishly pasted a link into my browser which turned out to be malware. Nasty.

But recovery back to previous state was helped tremendously by the following:

• SQLyog queries being batch backed up using XCOPY -

xcopy "C:\Documents and Settings\steve\Application Data\SQLyog\Favorites" "S:\SQL\SQL Queries" /L /Y

• Outlook rules exported
• Use of FEBE in Firefox
• Shortcuts previously copied to network (so can be copied back)
• Use of del.icio.us and Google Bookmarks

Things to bear in mind for future:

1. Don't click on things that look dodgy!
2. Back up the internals of Firefox extensions, such as Tab Mix Plus preferences, sites held in Morning Coffee