Tag Archives: choose

Data R

Quick R Tip – easily read files using file.choose()

I’ve been using R recently for a few things and the one thing I’ve often struggled with is getting the software to read external datasets (such as csv files) to work on.  The general code I’ve been using to do this is:-

data <-read.csv("My file path and file name")

but it’s a real pain in the backside to do and you can get some pretty weird errors if you don’t get the path and filename correct.

Anyway, today I found a much nicer way of loading in my data:-

data <- read.csv(file.choose())

Which pops up a standard Windows file dialog so that I can just select a file to read in.  Much easier and less prone to errors and resulting frustrated hair-pulling, I think.

Downloadable content Excel Functions and Formulas

Answer to yesterday’s CONCATENATE project

Yesterday, in the tutorial about CONCATENATE I asked if you could put together a formula which might use both CONCATENATE and CHOOSE to output different text based on user input.  Here’s a way that this could work – I’ve uploaded an Excel file and it can be downloaded from here.

You can click on the image for a larger version so that you can see how it all fits together.

So, what have I done here?  I’ve set up a table in the range A1:D6 which contains various text items.  In cells E3, E4, and E6 you can type numbers 1 to 4 to change the output of the CONCATENATE function based on which item is chosen from the three CHOOSE functions in the formula.  I know the resulting formula is pretty chunky, but does it make sense?  Download it and have a try and you’ll see what I mean.

It’s not the best example out there, but it shows a way that two or more functions can be combined to make a spreadsheet more powerful.  You could use any other functions to do similar things.  You might, for example want the output to be defined by the result of a lookup function, or the result of a mathematical equation.  As I work through the A to Z I’ll start posting more examples of how they can be used together to give a greater edge to your work.

Download the file and have a play with it to see what you can do.  If you come up with anything really interesting, do let me know.

Excel Functions and Formulas

A to Z of Excel Functions: CHOOSE

The CHOOSE function is pretty cool.  It’s one of a few unusual functions within Excel that can be really useful when used in conjunction with others and can really add a lot of power to your spreadsheets.  It kind of falls into the category of conditional functions such as IF, AND and so on, but it has it’s own thing going on, and that’s what I think makes it cool.

Anyway, I’ll get on with it.  The CHOOSE function lets you choose an item from another list of items and display it as the result.  it looks like this:-

=CHOOSE(position, value 1, value 2….and so on)

The basic gist of CHOOSE is that the returned value of the function is the value in the position specified by the ‘position’ argument.  Let’s look at a really basic example:-

=CHOOSE(1,1,2,3,4)
=1

Here we have asked the function to return the first value it finds in the list of values 1, 2, 3 and 4.  Let’s try it again, but with that first argument set to 3 – what do you suppose the return value will be?

=CHOOSE(1,1,2,3,4)
=3

Yep.  You guessed it – really simple, yes?

Let’s have a look at another example to make sure we’re getting it:-

=CHOOSE(6,12,45,32,12,54,32,12)
=32

32 is the sixth value in the list of numbers starting with 12.

It’s that simple!  It can, however be made much more powerful, as the ‘position’ argument, and the values themselves can relate to cell references, which can give the function a lot more power.  Choose can even be used to return ranges which can be used in other functions such as SUM, COUNT or even VLOOKUP.  Check out the example below:-


In this example we have used the CHOOSE function to provide the range for a SUM function.  Depending on whether the user fills in 1, 2 or 3 as the position number, the CHOOSE function will return one of the ranges specified and this will be used as the argument for SUM.  Let’s look at this more closely and break the whole function down as it works.  Here we are wanting to add the values in the second column:-

=SUM(CHOOSE(2,A1:A3, B1:B3, C1:C3))
=SUM(B1:B3)
=9

Pretty cool, huh?  What makes it cooler is that the formula above can take the place of nested IF functions.  The function in the screenshot above, for example, does the same as:-

=IF(C6=1,SUM(A1:A3),IF(C6=2,SUM(B1:B3),IF(C6=3,SUM(C1:C3))))

As you can see, this has lots of potential for use in spreadsheets where you want to provide some element of decision-making functionality and, with a little imagination can be put to use to make some real formula magic happen.  I told you it was cool…  Keep coming back for more A to Z!