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.
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
This doesn’t stop the contents of the cell from displaying in the Formula bar.
You might think adding a cumulative total to a filtered list is impossible, but with one of Excel’s new functions, it is easy.
I must create a system or be enslaved by another man’s.
William Blake (1757-1827)
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.
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
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 has had an AutoCalculation feature for many versions. This means you can see the result of common functions without typing a single formula.
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.
The (MONTH(A1)>6),6,30) 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.
In a recent training session I had a question about where the Print Selection option is in the new Excel versions.
Displaying Blank or a Zero if an error is encountered
To display a blank cell if an error is encountered use
To display a zero in a cell if an error is encountered use
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
This will display the total of all the positive values in column A.
To add up the negatives use
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
This will display how many entries there are in column A.
This is different from the COUNT function which only counts numerical values.
Every problem contains the seeds of its own solution.