Excel computed column exceptions

Question

(Alternative name: Why doesn't Excel actually support custom formulas with parameters without using VB and the problems that entail?).

[Updated to clarify my question]

In Excel, when you define a table, it will tend to automatically copy the formula in the column. This is very similar to "fill."

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

In the tables I'm going to do for some calculations, the first row tends to be "special" in some way. So, I want autocomplete to be omitted, but just not in the first row, or not in cells marked as custom. Excel documents mention exceptions in computed columns, but only with respect to finding and eliminating them.

For example, the first line calculates the initial value. All other lines calculate some incremental change. A trivial example: a table of 1 columns and 4 rows:

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

The first formula should be different from the rest. This creates a simple numbered list with A2 = 42, A3 = 43, A4 = 44. But now, say, I would like to change it to increase by 2 instead of 1. If I edit A3 as "A2 + 2", Excel will change table:

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

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

Is this (exceptions - especially in the first row of the table) an incredibly common requirement?

Answer 1

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

Firstly, there is no table formula syntax for the previous row, instead excel will return to A1 format by default, but you can use the offset formula to move the current cell to the previous row, as shown below. However, in this case it will return an # value error, as I am tilting +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 the if statement to initialize the value, buying a check if the value of the previous line = course value. If you use the initial else value, add an increment. Note. Estimated table called 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 that you can set the initial value as a cell outside the table to determine the initial value (for example, $ A $ 1) and the increment (in the case of $ A $ 2), as shown 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 many columns that need to determine if they are the first row, you can have one column if the first row and the rest can work with a simpler if. for example, ABC will return true for the first line false for others, then DEF in increments of 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 do not know if you are looking for something as simple as blocking a formula. You can do this by highlighting the part of the formula that you do not want to change, and then press F4. This will be absolute for this formila section, using $ to indicate it, and will not change when copying / pasting it into a table.

Alternatively, you can use Defined Names. They can be configured on the Data tab and basically assign something a name or a variable that you can put in your formulas. It can be as simple as a simple link for a cell on another sheet to incredibly complex multi-page formats.

Answer 3

Usually, to process an “exceptional” formula in the first row of a table consisting of several columns, you simply enter it manually there and fill in only the lines below. But if you have more “exceptional” cases scattered around, you will need another column with 0/1 values ​​indicating where the exceptions are. And then you use if(condition, formula_if_true, formula_if_false) everywhere.

  AB Number Exceptional? 1 if(C1,42,A1+1) 0 2 if(C2,42,A2+1) 1 3 if(C3,42,A3+1) 0 

As much as I like Excel, and how much it is the best product for all of MS, it is still a weak tool. FYI, you can quiklky learn modern and powerful scripting languages, such as Ruby, here , and never again worry about the features of spreadsheets.