Ampler adds non-standard custom functions to Excel — custom date, math, and text functions such as ISWEEKDAY, CAGR, COUNTDISTINCT, and CONTAINS — inserted from a menu.
Quick answer: Select the cell for the result, click Insert formula on the Ampler ribbon, and select the custom formula you want.
How to use it
Select the cell, then choose a custom function to insert.
- Select a cell where you would like the custom formula.
- Click Insert formula on the Ampler ribbon and select the desired formula.
Custom functions include date (ISWEEKDAY, ISLEAPYEAR), math (CAGR, COUNTDISTINCT), and text (CONTAINS, SUBSTRING, TRIM) — and more can be added.
Keyboard shortcut
Insert formula has no default shortcut. To assign one, open the Settings dropdown on the Ampler ribbon, choose Shortcuts, click Set shortcut on the function, and press your key combination.
Why use it
Some calculations — a compound growth rate, a distinct count, a substring test — take a long, fragile formula in native Excel. Ampler adds them as named custom functions, so the calculation is a single, reliable function instead of a hand-built formula.
How consultants use it
Standard, named functions make a model both faster to build and easier to audit than bespoke formulas. Three conventions apply:
- Named over bespoke: a custom function replaces a long, error-prone formula.
- Consistent across the model: the same function is used wherever the calculation appears.
- Common consulting maths: growth rates, distinct counts, and text tests are one function away.
Custom functions make common calculations reliable and consistent.
Tips & best practices
- Use CAGR for growth rates rather than a hand-built exponent formula.
- Use COUNTDISTINCT where a helper column would otherwise be needed.
- Keep to the named functions so the model stays consistent.
Availability
Included in Ampler for Excel and the Ampler Suite, on Windows desktop Excel.
Frequently asked questions
What custom functions does Ampler add to Excel?
Custom date functions (e.g. ISWEEKDAY, ISLEAPYEAR), math functions (e.g. CAGR, COUNTDISTINCT), and text functions (e.g. CONTAINS, SUBSTRING, TRIM), with more that can be added.
How do you insert a custom formula in Excel?
Select the cell, click Insert formula on the Ampler ribbon, and choose the custom function you want.
How do you count distinct values in Excel?
Use Ampler’s COUNTDISTINCT custom function via Insert formula, instead of a helper column.