Happiness is essentially a state of going somewhere, wholeheartedly, one-directionally, without regret or reservation.
William H. Sheldon
Monthly Archives: August 2015
The Martian (2014)
This is the first fiction book I have included on my website. Whilst Science Fiction, the story is based on accurate scientific knowledge.
The author self-published and was “discovered” after the book went viral.
This is one of the best audio books I have ever listened to.
Narrator was brilliant.
The story was terrific, lots of high and lows and great humor.
Highly recommend, even if you don’t normally read Sci-Fi.
Patience is passion tamed.
Lyman Abbott
Paste Values
After copying, use the following keyboard combination to paste just the values – no formulas or formats.
Alt h v v
These keys are pressed in sequence, not held down.
Never mind what others do; do better than yourself, beat your own
record from day to day, and you are a success.
William J. H. Boetcker (1873-1962)
Scrum: The Art of Doing Twice the Work in Half the Time (2014)
I listened to the Audio book read by the author.
A great book that’s filled with lots of real world examples to get its many practical point across.
An real eye opener for project managers. Some good ideas on planning how long a task will take – and it doesn’t use hours!
The practices have been around for over 20 years, but haven’t been widely known outside software development.
Highly recommended.
Random Dates
In Excel dates are stored as numbers.
To generate random dates you can use the RANDBETWEEN function with a start date and an end date.
The formula in cell C2 is
=RANDBETWEEN(A2,B2)
Cell C2 has been formatted as a date.
This formula is dynamic. Each time Excel calculates it will update and most likely change.
You can use Copy > Paste Values to capture the date(s).
Working with Imported Dates and Times in Excel
Date data imported from other systems can include times. This can make lookup and other calculations difficult. One function can make removing or extracting time easy.
I like living. I have sometimes been wildly, despairingly, acutely miserable, racked with sorrow, but through it all I still know quite certainly that just to be alive is a grand thing.
Agatha Christie (1890 – 1976)
Counting Errors in a Range in Excel
If you need to ensure that a range has no errors you can count the errors and compare the result to zero to ensure the range is error free.
The formula in cell C1 which counts errors in a range is
=SUMPRODUCT(ISERROR(A1:A5)*1)
If you want to display TRUE for no errors and FALSE for error(s) you can use
=SUMPRODUCT(ISERROR(A1:A5)*1)=0
You can also use an IF function to display the text Error if any errors are found.
=IF(SUMPRODUCT(ISERROR(A1:A5)*1)=0,"OK","Error")
Using a Number as Text in Excel Made Easy
When using numbers in text strings you usually need to format them. You typically need to use the comma format and handle decimals. There is a function that can simplify this task.
Removing Line Feeds in Excel
Sometimes when text data is imported from other systems it can contain line feeds.
A line feed wraps the text onto a new line – it is not associated with the Wrap Text format.
Cell A1 below shows an example. Cell B1 has the formula that removes line feeds and replaces it with a space.
The formula in B1 is
=SUBSTITUTE(A1,CHAR(10)," ")
Will and I could hardly wait for the morning to come to get at something that interested us. That’s happiness.
Orville Wright, co-inventor of the airplane