# Emulate Excel¶

One of agate’s most powerful assets is that instead of a wimpy “formula” language, you have the entire Python language at your disposal. Here are examples of how to translate a few common Excel operations.

## Simple formulas¶

If you need to simulate a simple Excel formula you can use the `Formula` class to apply an arbitrary function.

Excel:

```=(\$A1 + \$B1) / \$C1
```

agate:

```def f(row):
return (row['a'] + row['b']) / row['c']

new_table = table.compute([
(Formula(f), 'new_column')
])
```

If this still isn’t enough flexibility, you can also create your own subclass of `Computation`.

## SUM¶

```number_type = agate.Number()

def five_year_total(row):
columns = ('2009', '2010', '2011', '2012', '2013')

return sum(tuple(row[c] for c in columns)]

formula = agate.Formula(number_type, five_year_total)

new_table = table.compute([
(formula, 'five_year_total')
])
```

## TRIM¶

```new_table = table.compute([
(Formula(text_type, lambda r: r['name'].strip()), 'name_stripped')
])
```

## CONCATENATE¶

```new_table = table.compute([
(Formula(text_type, lambda r: '%(first_name)s %(middle_name)s %(last_name)s' % r), 'full_name')
])
```

## IF¶

```new_table = table.compute([
(Formula(boolean_type, lambda r: row['batting_average'] > 0.3), 'mvp_candidate')
])
```

## VLOOKUP¶

```states = {
'AL': 'Alabama',
'AZ': 'Arizona',
...
}

new_table = table.compute([
(Formula(text_type, lambda r: states[row['state_abbr']]), 'mvp_candidate')
])
```

## Pivot tables¶

You can emulate most of the functionality of Excel’s pivot tables using the `TableSet.aggregate()` method.

```jobs = employees.group_by('job_title')
summary = jobs.aggregate([
('salary', agate.Length(), 'employee_count')
('salary', agate.Mean(), 'salary_mean'),
('salary', agate.Median(), 'salary_median')
])
```

The resulting `summary` table will have four columns: `job_title`, ``employee_count`, `salary_mean` and `salary_median`.