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.

| | Posted in Books

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

Warren Beatty

| | Posted in Quotes

### 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.

### Related Posts

| | Posted in Tips

# Cumulative Total in a Filtered Excel List

### A solution to a tricky problem

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)

| | Posted in Quotes

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.

| | Posted in Books

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

Anonymous

| | Posted in Quotes

### 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.

### Related Posts

| | Posted in Tips

# Status Bar Calculations in Excel

### Quick and easy calculations

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)

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.
| | Posted in Books

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)

| | Posted in Quotes

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).

| | Posted in Books

### 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)

### Related Posts

| | Posted in Tips

# Where is Print Selection in Excel?

### Find out where it moved to

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

`=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.

### Related Posts

| | Posted in Tips

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.

| | Posted in Books

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.

| | Posted in 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.

### Related Posts

| | Posted in Tips

### 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.

### Related Posts

| | Posted in Tips

Every problem contains the seeds of its own solution.

Anonymous

| | Posted in Quotes

# Restricting the Scroll Area in Excel

### Non-VBA and VBA solution

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.

### 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.

### Related Posts

| | Posted in Tips

It is our choices, Harry, that show what we truly are, far more than our abilities.

Albus Dumbledore

| | Posted in Quotes

### 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.

### Related Posts

| | Posted in Tips

### 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.

### Related Posts

| | Posted in Tips

The time to repair the roof is when the sun is shining.

John F Kennedy (1917 – 1963)

| | Posted in Quotes