genie news band rates

7/31/2019 Genie News Band Rates

1/4


2/4

2 2012 AbleOwl (SEY) Ltd

TheLater avail. column shows whether there is an updated version of this component ('y' ifthere is, 'n' if there isn't). If there is an updated version, you can download it easily.

(4) If there is a 'y' in theLater avail. column for this component, choose Download.

(5) Choose Install.

Genie installs the BandRates function into Marie's workbook, and displays the messageshown below. (As this is a custom function, it needs to be installed into each workbook inwhich it is used).

"That sounds easy enough", thinksMarie. She switches to her taxcalculation worksheet, mClients(shown right).

(6) In Excel 2007/2010, chooseFormulas | Insert Function(Alt M F).

In Excel XP/2003, choose Insert |Function (Alt I F).

In any of the above versions, you can also click the Insert Functionbutton to the left of the Formula bar, as shown right.

The Insert Function dialog box appears.

(7) Select User Defined from the Or select a category drop-down.

BandRates appears in the Select a function pane, as shown below.


3/4

2012 AbleOwl (SEY) Ltd 3

Marie can see that BandRates takes two arguments: an amount and a BandTable. She already

has the amounts that she needs to calculate tax on, but she's not sure what a BandTable is.(8) ChooseHelp on this function.

A Help dialog box appears, as shown below. It contains an explanation of the BandRatesarguments.

Marie can see that her rates tableneeds to list the upper value foreach bracket in the left column,and the corresponding rates in theright column.

(9) Close the Help window.

She sets her rates table up on a separate sheet, dTaxRates;it appears as shown right. The final bracket ("Over200,000") needs an upper value, so she selects one that ishigher than the annual income of any of her clients.

(10) Select M15.

(11) Enter =BandRates(L15, dTaxRates!$K$14:$L$20)

This calculates that the tax for Di O'Bolic's income of$56,700 is $5,740, as shown below. That's $5,000

untaxed, $15,000 taxed at 8% ($1,200), $35,000 at 12%($4,200) and $1,700 taxed at 20% ($340).

All of these calculations areperformed, and the answer returned,in a single cell, by GeniesBandRates function.

(12) Copy the formula down.

And Marie's tax nightmares aregone! Her sleep is instead filledwith sweet dreams of Genie.

Note that the BandRates functionappears in proper case rather thanas BANDRATES. This is your cluethat it's a custom function, rather


4/4

4 2012 AbleOwl (SEY) Ltd

than a built-in Excel function.

It's also worth mentioning that BandRates isn't restricted to performing tax calculations(though that application alone would justify its existence for many!). Another common usefor this type of calculation is in figuring mileage allowances. Often, the allowance perkilometre decreases as the distance travelled increases; for example, $0.90/km for the first5,000 kilometres, $0.80/km for the next 5,000, and so on.

The pricing for multiple Genie subscribers might seem like magic, but in fact it uses

BandRates!

genie news band rates

Documents