Search Not Just Numbers

Loading...

Tuesday, 1 September 2015

Excel Tip: The Curious Case of FIND and SEARCH

One of the things I love about Excel is that I continue to learn and find new quirks all the time.

An issue that arose while working on a spreadsheet for a client last week, caused me to find one of these such quirks.

Read on about the Curious Case of FIND and SEARCH...

I regularly use the FIND function to find text within other text, for various reasons.

The FIND function works as follows:

=FIND(text,within text,[start character])

This function returns the position (character number) of the first occurrence of "text" within "within text" - reading left to right, starting at the start character, or the beginning if omitted.

So,

=FIND("U","NOTJUSTNUMBERS") returns 5

whereas,

=FIND("U","NOTJUSTNUMBERS",7) returns 9

I had used this function as part of a larger formula in a client spreadsheet, but it needed to be case-insensitive. After a little Googling, I discovered the SEARCH function which is exactly the same as the FIND function, the only difference being that it is not case sensitive!

I was even able to use Find and Replace to swap all of my FIND functions for SEARCH functions, to stop them being case sensitive.

I might have come across this earlier if it had been called something like FINDCASEINSENSITIVE!




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 25 August 2015

Excel Tip: Using the weekday of a date in a function

In an earlier post, I showed a method for pulling the text of the weekday out of a date. This is all very well if you are just wanting to pull the name of the weekday out of the date, but if you want to do any calculations using this information, then working with the day name is a bit "clunky".

As usual, Excel offers a different approach that is more appropriate.

The WEEKDAY function pulls the day out of the date as a number from 1 to 7 or 0 to 6, with options of which weekday to start on.

The syntax of the WEEKDAY function is as follows:

=WEEKDAY(Date,[Option])

Without the optional second argument, this will return a number 1 to 7 for the weekday of the date in the first argument, with Sunday being 1. You can, however, change how it works by using any of the following as the second argument:

1 or omitted - Numbers 1 (Sunday) to 7 (Saturday).

2 - Numbers 1 (Monday) to 7 (Sunday).

3 - Numbers 0 (Monday) to 6 (Sunday).

11 - Numbers 1 (Monday) to 7 (Sunday).

12 - Numbers 1 (Tuesday) to 7 (Monday).

13 - Numbers 1 (Wednesday) to 7 (Tuesday).

14 - Numbers 1 (Thursday) to 7 (Wednesday).

15 - Numbers 1 (Friday) to 7 (Thursday).

16 - Numbers 1 (Saturday) to 7 (Friday).

17 - Numbers 1 (Sunday) to 7 (Saturday).

So, for example, we can determine whether the date in cell A1 is a weekend with a formula such as:

=IF(WEEKDAY(A1,2)>5,"Weekend","Work")

If you need a refresher on the IF function, take a look at this earlier post.

By entering 2 as the second argument, Saturday and Sunday will be 6 and 7 respectively, so we can apply the criteria >5 to identify a weekend.





If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 18 August 2015

Excel Tip - Formatting numbers for thousands or millions

Apologies for the the lack of posts over the last few weeks. I intentionally took two weeks break while I was on holiday on the Isle of Skye with the family, then I have involuntarily missed the last two weeks while catching up since I got back!

I'm back now with a simple post that I have been asked about on numerous occasions.

"How can you show a number as millions or thousands? e.g. show, 3,000,000 as 3 million."

Well, the good news is that you can do it simply with a custom number format.

There is a full description of how number formats work available here, if you wish to know a lot more about this subject, but we don't need to understand all of that to format millions or thousands as required here.

If you right-click the cell or range that you wish to apply the number format to, and select "Custom", you will be presented with a box to allow you to create the format. This will be pre-populated with the current number format of the cell, and you can choose to start from any other.

We'll work from scratch with a simple format in this case, then you should be able to apply the same principles to more complicated formats.

Let's start with a number format of

#,##0.00

This is a commonly used format, to show numbers with two decimal places and a comma separator for the thousands.

The characters mean the following:

#  -  show this character only if required (e.g. if the number was 12, it would show as 12.00, not 0,012,00)

0  -  always show this character, even if it is not significant (this example will always display two decimal places, and a zero before the decimal place if it is less than 1)

,  -  use a thousands comma separator (this works for all thousands)

In this format, 15,450,324 would appear as 15,450,324.00, but we might want it to appear as any of the following for example:

15 million
15.45 million
15,450 thousand

We can do this by use of the comma and the insertion of text. Taking the last example, we can remove everything after the comma, swap the hash for a 0 (so that we still get a zero, if there is less than a thousand) and add the text, i.e.

0," thousand"

This, however gives us

15450 thousand

We can re-introduce the comma as a thousands separator as follows:

#,##0," thousand" giving us 15,450 thousand

If we want to do millions, we can add a second comma on the end, i.e.

#,##0,," million" giving us 15 million

We can also put decimal places before the commas, e.g.

#,##0.00,," million" giving us 15.45 million.

Have a play, and a read of the Office Support article on the subject, if you want to see what else you can do.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 14 July 2015

Is Excel error-prone?

As you may be aware from one of my earlier posts, I was attending an ICAEW event last week, marking one year since the launch of the Twenty Principles for Good Spreadsheet Practice, as I was privileged to be part of the team that worked on devising the principles.

One of the age-old accusations against Excel was addressed by Professor Ray Panko, the keynote speaker at the event. Namely that it is error-prone.

I thought it might be good to highlight his research into this accusation, as you might find it interesting reading (and a useful defence when senior managers say that "we must get rid of these spreadsheets").

The research paper behind Professor Panko's talk is available to read here:

What We Know About Spreadsheet Errors

If you want to see some UK-based research into Spreadsheet errors, take a look at F1F9's paper:

Capitalism's Dirty Secret

Professor Panko is possibly the world's leading academic when it comes to research into spreadsheet use and it was very interesting to hear his take on this subject.

A telling quote from his talk was the wonderful:


"Spreadsheets aren't error-prone, people are!"

Professor Raymond R. Panko, University of Hawaii

According to his research, the error rates in spreadsheets are roughly the same as in other types of activities with a similar level of complexity (such as computer programming), i.e. around 3%-5%.

The big difference is that this is recognised in software companies, whose testing processes tend to reduce this rate considerably, whereas most spreadsheet development is carried out in a much less rigorous environment. It's not that spreadsheets are error-prone, it's that the errors aren't removed by rigorous testing - i.e. it is nothing to do with the platform, but the different approaches of those carrying out the work.

To reduce the rates of errors in the finished (post-testing) solution doesn't require choosing a different platform, just adopting some of the best practices used in other types of software development.

This means applying good practice as per the Twenty Principles, and in particular, rigorously testing the solution (Principle 18), preferably by more than just the original developer, who is much less likely to spot his/her own errors.






If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".