Usage Rule Example for Internet Provider
When creating and uploading a usage rule for an internet provider that uses tiered, you would create a Usage Lookup Table with the following name and requirements:
InternetCustomers — This two-column Lookup Table is used to match a customer with the Internet Access Plan they are using. Although you can use customer names of your own choosing and add more than three customers, to explore this example, you would need to create a Lookup Table with the following entries:
| Lookup | Value |
|---|---|
Smith, John | Premium |
Jones, Jack | Regular |
Black, John | Economy |
| Cell | Explanation | Contents |
|---|---|---|
B7 | Unit of Measure. Calculations in this example are based on Gigabytes of information downloaded. The first few GB are free. This cell in informational only and is not used elsewhere. | Gigabyte |
B8 | Amount (Input). The number of Gigabytes of information downloaded in this billing period. | Any number between 0 (zero) and 50. |
B12 | Text 3 (Input). The name of the customer that matches a name in the InternetCustomers Lookup Table. This information is used to identify the Internet Service Plan | Any name that matches a name in the Lookup Table (for example: Smith, John). |
D2 | Rule Result: Formula to determine that a customer name has been entered into the B12 cell. The rule cannot work unless there is a customer entered. | =IF(B12="";0;1) |
E2 | Rule Rate: Formula to determine the charge for this period of Internet access. | =SUM(J2:J24) |
G5 | Label associated with H5. | Customer Plan |
H5 | Formula to determine the Internet Access Plan associated with the current customer. | =VLOOKUP(B12;$InternetCustomers.A2:B13;2;0) |
| G6 | Label to identify the Tiered Rating tables. Use leading spaces to center the label. | Rating Tiers |
G7, H7, I7, J7 | Labels to identify the contents of those columns. | Minimum; Maximum; Price; Calculation |
G8, G14, G20 | Labels to identify the areas listing tiered rating associated with individual Internet Access Plans. | Premium Plan; Regular Plan; Economy Plan |
I8, I14, I20 | Base price for the individual plans. These cells use "Currency" formatting. Used by J2. | 20; 15; 10 |
G9 through I12 | The tiered rating table associated with the "Premium Plan." | See the illustration below for the actual figures used. |
G15 through I18 | The tiered rating table associated with the "Regular Plan." | See the illustration below for the actual figures used. |
G21 through I24 | The tiered rating table associated with the "Regular Plan." | See the illustration below for the actual figures used. |
J2 | Formula that determines the base price of the Internet Access Plan. Used by E2. | =IF(H5="Premium"; I8; IF(H5="Regular"; I14; IF(H5="Economy"; I20; 0))) |
J9 | Calculation to determine price of Internet Access in Premium Access Plan, tier 1. | =IF(AND(H5="Premium"; B8<G10); (B8*I9); 0) |
J10 | Calculation to determine price of Internet Access in Premium Access Plan, tier 2. | =IF(AND(H5="Premium"; B8<G11; B8>H9); SUM(((B8-H9)*I10); (H9*I9)); 0) |
J11 | Calculation to determine price of Internet Access in Premium Access Plan, tier 3. | =IF(AND(H5="Premium"; B8<G12; B8>H10); (SUM(((B8-H10)*I11); ((H10-H9)*I10); (H9*I9))); 0) |
J12 | Calculation to determine price of Internet Access in Premium Access Plan, tier 4. | =IF(AND(H5="Premium"; B8>H11); (SUM(((B8-H11)*I12); ((H11-H10)*I11); ((H10-H9)*I10); (H9*I9))); 0) |
J15 | Calculation to determine price of Internet Access in Regular Access Plan, tier 1. | =IF(AND(H5="Regular"; B8<G16); (B8*I15); 0) |
J16 | Calculation to determine price of Internet Access in Regular Access Plan, tier 2. | =IF(AND(H5="Regular"; B8<G17; B8>H15); (SUM(((B8-H15)*I16); (H15*I15))); 0) |
J17 | Calculation to determine price of Internet Access in Regular Access Plan, tier 3. | =IF(AND(H5="Regular"; B8<G18; B8>H16); (SUM(((B8-H16)*I17); ((H16-H15)*I16); (H15*I15))); 0) |
J18 | Calculation to determine price of Internet Access in Regular Access Plan, tier 4. | =IF(AND(H5="Regular"; B8>H17); (SUM(((B8-H17)*I18); ((H17-H16)*I17); ((H16-H15)*I16); (H15*I15))); 0) |
J21 | Calculation to determine price of Internet Access in Economy Access Plan, tier 1. | =IF(AND(H5="Economy"; B8<G22); (B8*I21); 0) |
J22 | Calculation to determine price of Internet Access in Economy Access Plan, tier 2. | =IF(AND(H5="Economy"; B8<G23; B8>H21); (SUM(((B8-H21)*I22); (H21*I21))); 0) |
J23 | Calculation to determine price of Internet Access in Economy Access Plan, tier 3. | =IF(AND(H5="Economy"; B8<G24;B8>H22); (SUM(((B8-H22)*I23); ((H22-H21)*I22); (H21*I21))); 0) |
J24 | Calculation to determine price of Internet Access in Economy Access Plan, tier 4. | =IF(AND(H5="Economy"; B8>H23); (SUM(((B8-H23)*I24); ((H23-H22)*I23); ((H22-H21)*I22); (H21*I21))); 0) |
NOTES:
Column B is reserved for input, column C is reserved for output, column D is reserved for the Rule Result, and column E is reserved for the Rate generated by the rule. Cells in columns G through Z are reserved for rule-related information, formulas, and calculations.
When performing calculations, Gotransverse recognizes only certain functions. For a list of these functions, see Global Usage Rule SpreadsheetFunctions.
Cell J2 contains a nested IF statement to determine the base cost of the Internet Access Plan for the billing period.
Cells J9 through J24 use an AND function imbedded in an IF statement. If you prefer, you can use nested IF statements to calculate the usage. For example, in cell J11, you could use the following formula (other cells are similar): =IF(H5="Premium";(IF(B8<G12;(IF(B8>H10; (SUM(((B8-H10)*I11); ((H10-H9)*I10); (H9*I9))); 0)); 0)); 0)
When adding the rating tables and the related formulas for this example, be careful that you use the same cells as named in the above table. Remember that the formulas provided in the above table refer to specific cells and may not provide correct information if placed in different cells.
When adding information to the Rating Tiers tables in cells G9 through I12, G15 through I18, and G21 through I24, you can use any tier divisions that make sense to you (in columns G and H), as long as you follow the pattern in the illustration. In addition, you can enter your own prices (in column I). Be careful that you do not overwrite any cell that may contain a formula.
Formulas used in this example have been tested and were shown to work during product testing. You may determine that other Gotransverse-recognized formulas would be more suitable to fill your needs.