It’s a rather simple task but one that we often need to perform; being able to generate a random number. There are 2 different ways to get this done in excel.

#1-For a simple request such as: “Give me a number between 1 and 10”?

You can simply use the function randbetween:

=RANDBETWEEN(1,10)

Then, anytime you want to generate a new number, you’d press “F9” on your keyboard (refresh)

#2-If you’re looking for something a bit more sophisticated, you could use the function “rand” which would give you a number between 0 and 1 with (decimals)

So if i was looking at a number between 1 and 10 but wanted to include decimals I could do:

=10*rand()

Simple enough?

One of the functions that I’ve used several times on this blog is the “IsError” function where I will test to see if a specific cell has an “error”. Why? For example if I use the vlookup function and I cannot find the “value”, then I’d prefer seeing some other message rather than a #Value

There are many other types of “IsFunctions” that will either return “True” or “False” which can be very useful. Here are a few:

ISBLANK(value)
ISERR(value)
ISERROR(value)
ISLOGICAL(value)
ISNA(value)
ISNONTEXT(value)
ISNUMBER(value)
ISREF(value)
ISTEXT(value)

For example, if I have a database of hundreds of lines and want to be sure that a specific field is not blank, this would be a good way to do it. For example:

Then, I could transform that data into a number by doing the following:

=IF(ISBLANK(B2),1,0)

As you can see, these type of functions are great to add control tests

One of the more simple but recurring tasks in excel is trying to determine how many cells in a given range result in a given answer, which could be a number, or any other symbol. There are many different ways to get that result in excel but I personally think the “countif” function is the most efficient way of getting it done.

Suppose that you have a spreadsheet with a number of different columns and you’re trying to determine how many of the stocks in this spreadsheet in column “D” have “US” as their country of incorporation, I could do the following:

You would see that 473 of the 500 names

Simple enough? I would hope so! As always you can download the spreadsheet here

One of the very interesting things that is possible in Google docs is the ability to create spreadsheets that are easy to view from any location and also possible to share.

I was looking to graph my running in a spreadsheet and initially was going to do it in Excel but then I thought about 2 factors:

-I wanted to be able to update it from my phone, office and home
-I wanted to share the info with my wife and a few friends

That made using a cloud based software the only real solution. I did consider going with Excel but even though it’s gotten much better, it remmains more difficult to use on all kinds of devices such as a smartphone, etc. So in the end, I went towards a Google Doc Spreadsheet. The first step was easy, I entered my monthly totals for the past 3 years:

Then, I simply added a couple of charts:

I then tried a different chart by clicking on “more” to get to this view

I personally felt like this was a much better look:

Here is my final result: