Friday, April 01, 2005

Using the MAX function in a CSE Formula in Excel

CSE (or CTRL SHIFT ENTER) formulas can be used to make functions do things they normally can't do.

For example the MAX formula will usually only find the largest value in a range of values. But what say you wanted to find the largest value in a column based on a value in another column.

The attached pictures illustrate how this can work useing a CSE formula.

Using the example of a list of employees and their sales over a period from which you wish to find the maximum sales for the employee for the period.

The required formula is MAX(IF(A2:A21=E2,C2:C21)), but it must be entered as a CSE formula. That is once you have typed the formula hold CTRL and SHIFT down as you press Enter. This creates the squiggly brackets {} (squiggly is a highly technical term) around the formula.

I have attached two pictures. The first shows the cell when it is being edited and the colours outlining the ranges. The second shows the result of the formula and the squiggly brackets in the formula bar.

Thanks to one of my readers Mark for supplying this one.



Posted by: Jethro | 5:05 PM |