Search Not Just Numbers

Loading...

Tuesday, 16 September 2014

Excel Tip: Knowing where you are in your spreadsheet, using the ROW and COLUMN commands

Sometimes in your spreadsheet, like in life, it is good to get your bearings to establish where you are.

This can be very useful in a formula if you want the result to be dependent on the location in the spreadsheet.

For example, you might want to:

  • format odd and even rows (or columns) differently, or
  • apply a different formula depending on how far down a list an item appears, or
  • populate cells with a particular value, formula or format based upon  an entered width and height.
To do any of these types of things with a formula, the formula will have to know its own location (i.e. its row and column) first.

The ROW and COLUMN commands do exactly that.

The syntax of the ROW command is:

=ROW([Reference])

where Reference is the cell reference that you wish to return the row of. So,

=ROW(A5)

for example, will return 5.

But notice that Reference is in square brackets which, you may remember form earlier posts, means that it is an optional argument. So, the function can be written as just:

=ROW()

This will return the number of the row in which the formula sits, i.e. giving us the row information that we would need for all of our examples above.

Typically we will use it in an IF statement to drive the different outcomes in our examples, for example:

=IF(ROW()<=10,1,2)

will return a 1 in rows 1 to 10 and a 2 thereafter.

The COLUMN function works exactly the same way so:

=COLUMN(A5)

returns 1 (the column is returned as a number not a letter, so A is 1, B is 2, etc. - this does make it easier for adding and subtracting column positions). Also,

=COLUMN()

returns the column number of the column in which the formula sits.

One word of caution. You need to remember that you have used these functions if you start inserting or deleting rows and columns. Most functions will adjust accordingly and maintain the same relative references, but because these refer to the actual cell positions, their results will change if their position changes.



Excel Expert Course

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, 9 September 2014

Excel Tip: SUBTOTAL and SUM - what's the difference?

I'm sure most of you are familiar with the SUM command for totalling ranges of cells, but have you heard of the SUBTOTAL command? - and if so, do you know what it does?

I must admit that I had only looked at the SUBTOTAL command in any detail this week - prompted by a question from a reader. I had never really used it, because I normally advocate doing any analysis of a data list separately using PivotTables, rather than within the list itself and didn't realise that it offered some interesting differences to just using the SUM command.

At first glance it would appear to be the same, but it has a number of additional powers!

I am aware that most of you will know this next bit, but I have included it for completeness:

The SUM command is used as follows:

=SUM(number1,[number2],....)

only number1 is required and should be a number, or a reference to a number or range of numbers.

So, number1 could be, for example, 3, A4, A1:A4 or a named range.

The same rules apply to number2 and number3 all the way up to number255, except these are all optional.

The SUM function then returns the total of all of these numbers.

The SUBTOTAL command has very similar arguments except it has one additional argument in front of them:

=SUBTOTAL(function_num,ref1,[ref2],....)

ref1 and the optional arguments ref2 to ref254 are very similar to number1, etc. in the SUM function, however they must be references to cells or ranges of cells - i.e. not actual numbers.

The function_num argument must be between 1 and 11 or between 101 and 111. This argument determines how the function is to summarise the numbers. The Excel function that will be applied for each of function_num 1 to 11 is given below:

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

This is the first real difference between SUM and SUBTOTAL. SUBTOTAL can mimic any of these 11 functions.

The second difference is more subtle. You would expect the following two functions to return the same result:

=SUM(A1:10)
=SUBTOTAL(9,A1:10)

and in most cases they will. The difference becomes apparent when you apply a filter to the data. This will have no effect on the SUM result but the SUBTOTAL will exclude any rows hidden by the filter from the calculation. This could be very useful if you regularly work with a data table that you filter in place using the AutoFilter facility, as it will show you the total of the displayed rows.

You can further affect the result by using function_num 101 to 111. These work exactly the same as 1 to 11 but exclude rows hidden using the Hide command as well.

One final difference is that the SUBTOTAL command will ignore any other SUBTOTAL commands in the range being summed, thereby avoiding double-counting. Thanks to Jeremy for pointing out that I'd missed that one!


Excel Expert Course

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, 2 September 2014

Excel Tip - counting workdays allowing for holidays

Please accept my apologies for the lack of posts these last couple of weeks - I've been away on holiday, chilling out on the Isle of Skye (that's me in the picture relaxing on the coral beach near Claigan).

I had intended to write some posts in advance but it didn't exactly work out as planned!

Now I'm back, I thought I'd write a (vaguely) holiday related post.

If you want to know how to work out the number of working days between two dates (allowing for weekends AND holidays), read on.

To do this, we can use the NETWORKDAYS function.

The syntax for this function is:

=NETWORKDAYS(StartDate,EndDate,[Holidays])

I would not recommend entering the dates directly into the function. This is not only good advice for making this particular function work, but it is best practice when referring to any variable in Excel - enter its value in a cell and refer to the cell.

Ignoring the optional argument, if A1 contains 1/8/2014 and A2 contains 31/8/2014, then

=NETWORKDAYS(A1,A2)

returns 21, being the number of working days in August 2014 (if you ignore public holidays).

The best way to use the optional third argument is to refer to a range, where you can enter holiday dates. So, say we add the third argument as follows:

=NETWORKDAYS(A1,A2,B1:B20)

We can now enter the dates of any holidays in cells B1 to B20 and these will be excluded from the calculation.

So, in the UK, last Monday was our August Bank Holiday, so if we enter 25/8/2014 into any of the cells from B1 to B20, the formula returns 20.

A practical way of using this functionality would be to enter the whole year's holidays into the range referred to (B1:B20 in our example), any of those dates that fall between the start and end dates would then be excluded from the calculation.


Excel Expert Course

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, 12 August 2014

Excel Tip: Add a simple tick box to your spreadsheet - and use its result!

Do you ever wonder how you get those professional looking tick boxes (or check boxes) in your spreadsheet?

They're easier to add than you think, and it's pretty straight-forward to have calculations depend on the result of the tick box.

In this post, I'll show you how to do both.

Before you can add a tick box, you need the Developer Ribbon enabled. If you don't see it as one of the named Ribbons at the top of Excel, then you can add it by selecting File - Options - Customize Ribbon and then ticking Developer in the list of Main Tabs on the right-hand side.

To insert a tick box, go to the Developer ribbon and click Insert in the drop-down menu that appears, click the Check Box (Form Control) icon. This is the tick box under the heading Form Controls - if you hover over it you will see the name "Check Box (Form Control)" appear.

Your cursor will become a cross and you can now click where you want the tick box to appear.

The box will appear with some default text to the right of it (this will usually be Check Box 1 if this is the first one that you have inserted into the spreadsheet. This text can be edited (or deleted) by double-clicking on the text and editing or deleting as required.

This is your tick box created and you can now click it to toggle between ticked and unticked.

However, I did say that I would show you how to use the result of the tick box.

If you right-click on the tick box you will see the menu option "Format Control". Click on this and go to the Control tab.

You will see a box entitled Cell Link. Enter a cell reference here, where you wish to store the result of the tick box (let's say C3). Now when you click the tick box, cell C3 will switch to show TRUE, and when you untick the tick box, C3 will show FALSE.

You can then use cell C3 in a formula. The most common way to use it would be as the criteria argument of an IF function. As C3 contains the logical value TRUE or FALSE, then this is all you need as your criteria argument, so:

=IF(C3,100,0)

will return the value 100 if the tick box is ticked, or 0 if not.

It's as simple as that!

Excel Expert Course

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