Exceptions in Excel calculated columns

Question

(Alternate title: Why on earth doesn't Excel support user-defined formulas with parameters without resorting to VB and the problems that entails?).

[ Updated to clarify my question ]

In excel when you define a table it will tend to automatically replicate a formula in a column. This is very much like "fill down".

But ... what if you need exceptions to the rule?

In the tables I'm building to do some calculations the first row tends to be "special" in some way. So, I want the auto-fill down, but just not on the first row, or not on cells marked as custom. The Excel docs mention exceptions in computed columns but only in reference to finding them and eliminating them.

For example, first row is computing the initial value The all the remaining rows compute some incremental change. A trivial example - a table of 1 column and 4 rows:

    A
1 Number
2 =42
3 =A2+1
4 =A3+1

The first formula must be different than the rest. This creates a simple numbered list with A2=42, A3=43, A4=44. But now, say I'd like to change it to be incremented by 2 instead of 1. If I edit A3 to be "A2+2", Excel changes the table to be:

    A
1 Number
2 =A1+2
3 =A2+2
4 =A3+2

Which of course is busted -- it should allow A2 to continue to be a special case.

Isn't this (exceptions - particularly in the first row of a table) an incredibly common requirement?

Answer 1

If you have the data formatted as a table you can use table formulas (eg [@ABC]) instead of A1 format (eg A1, $C2 etc). But there are 2 tricks to account for.

Firstly there is no table formula syntax for the previous row, instead excel will default back to A1 format, but you can use the offset formula to move you current cell to the previous row as shown below. However in this case it will return an # value error since I cant +1 to "ABC".

     ABC
1   =OFFSET([@ABC],-1,0)+1
2   =OFFSET([@ABC],-1,0)+1
3   =OFFSET([@ABC],-1,0)+1
4   ....

So the second trick is to use a if statement to intialise the value, buy checking if the previous row value = heading value. If the same use the initial value else add the increment. Note assumes table is named Table1

     ABC
1   =IF(OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]],42,OFFSET([@ABC],-1,0)+1)
2   =IF(OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]],42,OFFSET([@ABC],-1,0)+1)
3   =IF(OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]],42,OFFSET([@ABC],-1,0)+1)
4   ....

Note you can set the initial value to be a cell outside the table to define the initial value (in say $A$1) and increment (in say $A$2) as below

     ABC
1   =IF(OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]],$A$1,OFFSET([@ABC],-1,0)+$A$2)
2   =IF(OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]],$A$1,OFFSET([@ABC],-1,0)+$A$2)
3   =IF(OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]],$A$1,OFFSET([@ABC],-1,0)+$A$2)
4   ....

I use this IF OFFSET combination all the time for iterating and looping in tables.

If you have alot of columns that need to determine if they are the first row you can have one column test if first row and the rest can work with a simpler if. eg ABC will give true for first row false for others, then DEF with increment the initial value

     ABC                                            DEF
1   =OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]]   =IF([@ABC],$A$1,OFFSET([@DEF],-1,0)+$A$2)
2   =OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]]   =IF([@ABC],$A$1,OFFSET([@DEF],-1,0)+$A$2)
3   =OFFSET([@ABC],-1,0)=Table1[[#Headers],[ABC]]   =IF([@ABC],$A$1,OFFSET([@DEF],-1,0)+$A$2)
4   ....

Hope that helps

Answer 2

I don't know if you are looking for something as simple as locking down a formula. You can do that by highlighting the part of the formula you do not want to change and then hitting F4. This will absolute this section of the formila, using a $ to indicate it, and will not change as you copy/paste it down the table.

Alternately, you may be able to use Defined Names. These you can set up in the Data tab and basically assigns something to a name or variable you can then put into your formulas. These can be as simple as an easy reference for a cell on another sheet to incredibly complex multi-sheet formals.