July 26, 2004

Instant Row Numbers

I got asked by Jon Eaton the other day about numbering rows automatically after a blank row is inserted. Here's a simplified version formula I wrote (the one I made for Jon was a tiny bit more difficult)

=IF(INDIRECT(ADDRESS(ROW()-1,COLUMN()))="",1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1)

The ADDRESS function
You should already be familiar with the ROW and COLUMN functions. You can either put a reference (cell address) inside them such as =ROW(A2) which will return 2 or =COLUMN(D3) which will return 4. Used by themselves, =ROW() or =COLUMN() will automatically return the row or column number of the cell that the formula is placed in.

The ADDRESS function takes this a step further. =ADDRESS(ROW(),COLUMN()) will return the address of the cell it is placed in.



The INDIRECT function
As you can see from the formula I wrote at the top, the INDIRECT function is combined with the ADDRESS function. I'm using =ADDRESS(ROW()-1,COLUMN()) to refer to the cell above the row my formula is placed in, the INDIRECT function is used to refer to the value in that cell.

Here is a picture with INDIRECT in action.


I have added a column to the above ADDRESS formula to refer to cell E3 as in =ADDRESS(ROW(),COLUMN()+1). Then in cell B2, the INDIRECT function is used to refer to cell D3 which in turn refers to cell E3. Yes, INDIRECT is somewhat ...indirect!

Now you should be getting a feel for how my original formula works. The IF function determines that if the above row is blank (""), the result should be 1. If not, add 1 to the cell value above.

Enter the formula into a cell below a bank row and drag down. The numbers will appear in order.



Here's a modified formula to work in the top row also.

=IF(ISERROR(ADDRESS(ROW()-1,COLUMN())),ROW(),IF(INDIRECT(ADDRESS(ROW()-1,COLUMN()))="",1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1))

This formula works on the principle that there is no row above the top row. As this would result in an error, the formula returns the regular row number. In the case of the top row, this just happens to be Row 1.  

Posted by andrewe at 22:43

July 25, 2004

I like OFFSET

I haven't used OFFSET that much until recently, but now I am beginning to appreciate it more and more.

In it's simplified form, you can just use it to refer to one particular cell like this.

=OFFSET(reference,rows,columns)

For example, if I used =OFFSET(A1,4,1), this will show cell B5 as in cell A1 + 4 rows down = cell A5, then cell A5 + 1 column to the right = cell B5. (Use minus numbers to refer to cells above or to the left)

Here's a couple of useful formulas that I put together.

Show every 3rd Row
In Column A:A I have a list of names. In cell B1 I enter =OFFSET($A$1,ROW()*3-1,0)
and drag down.



Show every 3rd Row from A1
This means showing names in cells A1, A4, A7 and so on.

First I have entered =A1 in cell C1. Then I use a slight variation of the above formula in cell C2 and drag down.



In both formulas, you can change the "3" for any interval of rows you like.

The more I play with OFFSET, the more I appreciate it's usefulness. Other useful reference functions are ADDRESS and INDIRECT. I'll show a neat trick using them the next time I post.  
Posted by andrewe at 00:19

July 16, 2004

Multiple Lookup

The SUMPRODUCT function must be one of the most useful functions available in Excel. I also think it is one of the most interesting (Even some of the leading Excel experts seem excited on finding new ways to use it's potential to the max)

Today's demo is rather easy. I have names and dates in the columns shown in the picture below.



Some of the names are the same, some cells are blank. Combinations of CONCATENATE, INDEX, MATCH and OFFSET etc will also get the correct result, but SUMPRODUCT just by itself will get the results without the need to use nested formulas such as in the next picture (Incidentally using SUM and IF is a good alternative to remember, but don't forget that limitations on nesting formulas can make life difficult later on, even if there are ways to get around this)



So how does SUMPRODUCT work? A simple explanation would be to say that the "*" operator acts as AND. It looks for a matching combination of the First, Middle and Last Names to locate the correct Age.

(The "+" operator acts as OR so the result in the example above would be 78 had I so used it instead of the "*" operator)

For some further information on SUMPRODUCT I suggest xlDynamic.com as a very useful reference.

Have a nice weekend.  
Posted by andrewe at 03:06

July 09, 2004

Being Choosey

The CHOOSE function is a kind of a Lookup Function.

Here's an example of how it can be used. Keep in mind that the current month is July and wanting to show this month in a cell, I might start off with this formula.

=MONTH(TODAY())

This gives me the number 7 but what I really want is July.

Say I use the Format Cells dialog box (Right click the cell to access it) and enter a custom format of mmmm. Hmmm, that's strange...the month now shows as January! This is a common problem that occurs when converting dates to text.



Okay then, let's try just entering =TODAY() by itself and then using mmmm.

Well, this should give me July and it does so on earlier versions of Excel. Unfortunately the number of available formats have now increased, so what I get now is the local format which in my case is in Japanese. Okay for me, not so good for others.



This is still not a real problem, I can fix this by using an English format as in [$-409]mmmm.



Solved? Maybe so, but I don't really know how the [$-409]mmmm format will appear on other Excel versions, and I want to be sure of consistent results each time.

Where does the CHOOSE function come into play?

Well, I know that using the CHOOSE function I can set my own results without relying on various types of formatting. (Let me say that I could use a variety of other Lookup Functions etc, CHOOSE is just one of many solutions)

It works like this.

=CHOOSE(lookup number, option 1, option 2,...)

Let's go back to the original MONTH formula and nest it using the CHOOSE function like this.

=CHOOSE(MONTH(TODAY()),"January","February","March"...)

It's a little longer but it does do the job intended. And that's what I am really after.  
Posted by andrewe at 01:52

July 05, 2004

Go to Go To

It's a great navigational aid, I love the Go to dialog box.

Just push F5 and it appears on your screen, showing all of your cell and range names, plus it also shows the last four places you have been to, even on other Worksheets or Workbooks. (The ability to jump to other Workbooks make it even easier to navigate than the Name Box next to the Formula Bar)

Here's an example.


If you're not sold yet, try pushing Special. As you can see, there is a whole bunch of new selections available.

Conditional Formatting is a great thing, but if you're not careful, inserting rows can make other rows gradually creep down the page...(Hey, that's funny! Why are these cells changing colors where they shouldn't ?!)

Problem is that unless the conditional format itself is met, it's very difficult to know which cells are formatted and which ones are not.

This is where the Go to dialog box comes in handy. Select Conditional formats as in the image and they will be highlighted after you push the OK button.



I tried this a couple of days ago at a whim and was surprised to see a group of conditionally formatted rows had somehow broken away from the main group and were on their merry way to the bottom of the sheet. (Key word is were)  
Posted by andrewe at 00:45

July 03, 2004

Back to Numbers

In the case that you want to convert numbers formatted as text back to numbers again, try using Excel's Error Checking features (To the best of my knowledge this is available from XP onwards)

Open the Options dialog box from the Tools menu, then select the Error Checking tab. If the Enable background error checking and Numbers stored as text checkboxes are ticked, then numbers formatted as text will appear with an error marker, (usually green, my Excel 2003 version allows me to choose other colors if desired)



Select cells with the error mark and a small dropdown menu will appear. Click it and choose Convert to Number.



Note this applies to regular formatting. Numbers changed by the TEXT function are not recognized as errors so you will probably be better off using the VALUE function instead.  
Posted by andrewe at 04:28