Blink: The Power of Thinking Without Thinking

Audio book version:

Malcolm Gladwell is a good narrator and it adds to the experience when the author reads his own work.

A great book that succeeds in explaining the decisions we make in the BLINK of an eye and how we can make better decisions.

There are lots of examples and studies, quoted and explained.

Recommended reading for all who would like to know how we make those split second decisions.

You’ve achieved success in your field when you don’t know whether what you’re doing is work or play.

Warren Beatty

Stop a cell from displaying and printing

You can use a Custom number format to stop a cell or range from displaying and printing.

Select the cell or range

Press Ctrl + 1

Use the 1 on the keyboard not the numeric keypad. In the Number tab click the Custom option (bottom left-hand side)

In the Type box enter

;;;

Click OK

This doesn’t stop the contents of the cell from displaying in the Formula bar.

SuperCrunchers: Why Thinking-By-Numbers is the New Way To Be Smart

Interesting topic for all number crunchers: accountants, engineers or statisticians.

It discusses algorithms, large data sets and much more.

The book also brings up some new ideas on neural networks.

The difference between failure and success is doing a thing nearly right and doing a thing exactly right.

Anonymous

Counting text in a range

To find out how many cells contain a text string in a range you can use the COUNTIF function and an Excel wildcard character.

To see how many cells contain abc in column A you could use

=COUNTIF(A:A,"*abc*")

This will count abc no matter where it appears in the text eg 123abc, abc123 and 123abc123 will all be counted. It is only counted once per cell.

The * is the wildcard character.

Excel Hacks (O'Reilly)

Great book with solutions to difficult issues in Excel.
A mix of amazing formulas, macros and manual techniques to get around some of Excel’s limitations.
The authors run the OzGrid.com website which is based in WA.
Their website is a great resource for Excel users.

Just don’t give up trying what you really want to do. Where there is love and inspiration, I don’t think you can go wrong.

Ella Fitzgerald (1918-1996)

Made to Stick

This is a brilliant book.

It is relevant for many different areas. Public speaking, management, financial reporting – even telling jokes.

It covers the six critical areas that you need to focus on to get your message across so that it sticks.

Its very practical and also humorous.

You’ll never think about kidney transplants the same again (read it and you’ll understand).

Convert date to its Financial Year End Date

To convert a date to its financial year end date you can use the following formula, assuming the date is in cell A1.

=DATE(YEAR(A1)+(MONTH(A1)>6),6,30)

The (MONTH(A1)>6) part returns TRUE when the month number of the date is above 6. In Excel TRUE = 1, hence 1 is added to the year of the date.

If the month number is 6 or less it returns FALSE and in Excel FALSE = 0, so the year is left unchanged.

A shorter formula that provides the same result is.

=DATE(YEAR(A1+184),6,30)

Displaying Blank or a Zero if an error is encountered

To display a blank cell if an error is encountered use

=IFERROR(A1/B1,"")

To display a zero in a cell if an error is encountered use

=IFERROR(A1/B1,0)

The IFERROR function is available in Excel 2007 onwards.

Excel 2007 Dashboards & Reports For Dummies

A great book with lots of worthwhile suggestions for improving your use of charts, both in the creation and design phase.

Lots of little tips that alone are worth the price of the book. Highly recommend.

I am big fan of Michael Alexander.

The 7 Habits of Highly Effective People

If I was asked to recommend one self help book to improve your whole life this would be it.

Its easy to read and the suggestions can be implemented if you take then step by step.

It deserves a couple of reads, one to get an overview and another slower read to implement change.

I have also listened to the audio book version (narrated by the author), which is also very good and more up to date.

Sadly, Stephen R Covey has passed away recently, but his legacy lives on through his books.

Adding Positive or Negative Values

To add up the positive values in a column you can use

=SUMIF(A:A,">0")

This will display the total of all the positive values in column A.

To add up the negatives use

=SUMIF(A:A,”<0″)

This will display the total of all the negative values in column A.

Finding how many entries in a column

To find out how many entries there are in a column you can use

=COUNTA(A:A)

This will display how many entries there are in column A.

This is different from the COUNT function which only counts numerical values.

Determine if a Value is Duplicated in a Column

To determine if a value is duplicated in a column you can use

=COUNTIF(A:A,B1)>1

Will display TRUE if the value in cell B1 appears more than once in column A and FALSE if it appears once or not at all.

You can use the above as a logical test in an IF function as well.

Determine if a value is in a range

To determine if a value is in a column you can use the COUNTIF function

=COUNTIF(A:A,B1)>0

Will display TRUE if the value in cell B1 is in column A and FALSE if it isn’t.

You can use the above as a logical test in an IF function as well.

ABS Function

To confirm that two values are within 1 of each other you can use the ABS function.

=ABS(A1-B1)<=1

Will display TRUE if the values in the cells are within one of each other. It will display FALSE if their difference is more than one.

1 is used as it is a common balancing check for rounded numbers.