Calculating rental fees


Q I use Excel to keep track of rental items for my job. An item rents for, say, $1000 for five days; and then, from the sixth day on, the fee is $100 per day. I can't figure out a formula to count the five days with its fixed fee and then add increments of $100 for each day thereafter.

- Gary Risinger

A You can often solve a problem like this by dividing it into steps. The worksheet illustrated has two sections, labelled Input (rows 2 to 5) and Calculations (rows 8 to 10). The Input section contains values only, while the Calculations section contains formulas. In all cases, the same solution works in Lotus 1-2-3 if you substitute @ for the equal sign.

Caption: The three formulas used to calculate total rental fees for
an item can be reduced to a single formula

First, figure extra rental days. Cell B8's formula is =IF(B5>B3,B5-B3,0). It says: if the total number of rental days exceeds the number of days in the base rental period, subtract the days in the base period from the total rental days; otherwise, return 0 (no extra days).

The formula in cell B9 is =B8*B4. It simply multiplies the number of additional days calculated in B8 by the additional-day fee in B4. The "bottom line" formula in cell B1, =B2+B9, adds the base rental fee to the extra-day fee.

The worksheet is using three formulas, which you can reduce to one by replacing cell references with formula text. For example, the final formula (in B10) contains a reference to cell B9. Copy the formula's text (excluding the initial equal sign) from cell B9 to cell B10. The formula in B10 will be =B2+B8*B4. Now the formula refers to cell B8 (which also holds a formula), so you can replace that reference with a copy of cell B8's formula text: =B2+(IF(B5>B3,B5-B3,0)*B4).

- John Walkenbach


Category:spreadsheet
Issue: October 1999

These Web pages are produced by Australian PC World © 1999 IDG Communications