|
|
|
|
|
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 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
Calculating rental fees