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
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.
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.
Anonymous
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 has had an AutoCalculation feature for many versions. This means you can see the result of common functions without typing a single formula.
Excel Hacks (O'Reilly)
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).
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)
In a recent training session I had a question about where the Print Selection option is in the new Excel versions.
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.
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.
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.
Every problem contains the seeds of its own solution.
Anonymous
If you need to limit where a user can scroll to in a sheet you can change a setting in the VBA screen to restrict access to a specific range.
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.
It is our choices, Harry, that show what we truly are, far more than our abilities.
Albus Dumbledore
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.
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.
The time to repair the roof is when the sun is shining.
John F Kennedy (1917 – 1963)