Wednesday, January 19, 2005

CTRL SHIFT ENTER (CSE) Formulas in Excel

There are a bunch of things you can do with array or CSE formulas in Excel. These include reducing the number of formulaic steps required to achieve a result. For example take a column of data C1:C20 that has a number of calculations in it. Some of these return errors. One way to calculate the number of errors is to create a second column (say D) that has a formula returning a result , eg 1, if there is an error. A sum of this column will tell you how many errors there are.
Or you could use a CSE formula like this one. Note when entering this you must press CTRL SHIFT ENTER to finish editing the formula. This will put the {}braces around the fomula. You don't need to type these.

{=CONCATENATE("There are "&SUM(IF(ISERROR(C1:C20)=TRUE,1,0))&" errors","")}


Posted by: Jethro | 6:41 AM |