Posted by Ernest__CT on 7/5/05 12:53am
Msg #49817

Excel spreadsheet for rates, as promised

Now that the end-of-month craziness is mostly past, here is the format I use for my town/ZIP Excel spreadsheet. Two caveats: 1) This works for me because I'm a geek; your results may vary. No real town names or ZIP Codes were used. The rates listed may or may not apply to any signings I do in real life. B) This works in Excel 2003; it should work in other versions, BUT .... III) If you'd like the sample spreadsheet emailed to you, view my Profile and send email. I'll help as time permits. d) Never believe anyone who says there are only two caveats.

Create a blank spreadsheet. The data in the Columns in Row 1 should be
The word "Base".
Whatever your base fee is, including the $. For example, $65.
The amount you charge per mile over your initial free miles. For example, $0.75.
The words "per mile".
The word "after".
The number of free miles you will travel. For example, 10.
The word "miles".

Row 1 should now read
Base $65 $0.75 per mile after 10 miles
and your cursor should be in Cell A2. Use the "Window" pull-down menu and select "Freeze Panes". This will keep Row 1 in place when (if) you decide to sort the towns/ZIPs.

Enter the town names in Column A and their ZIP Codes in Column B. Enter the mileage from your home (office) to the respective towns in Column C. For example purposes, Rows 2, 3, and 4 might look like
East Overshoe 123467 5
North Overshoe 123489 15
Overshoe 123456 10

No big deal so far, right?r All we have is a list of towns, ZIP Codes, and distances. Now we need to put the formula in each Row in Column D. The formula for Row 2 is
=$B$1+IF(C2>$F$1,($C$1*(C2-$F$1)),0)
When you've entered that formula and left Cell D2, "$65.00" should appear in the Cell. If not, please check to be sure you've typed the formula exactly the way it is shown.

You can now copy and paste the formula into Column D of the rest of the Rows that have data. Reading down in Column D you should see
$65.00
$68.75
$65.00

The next post will explain the formula, for those who care.

Reply by Ernest__CT on 7/5/05 1:40am
Msg #49818

Explanation of Excel spreadsheet formula

The formula is
=$B$1+IF(C2>$F$1,($C$1*(C2-$F$1)),0)

Broken down: The "=" at the beginning tells Excel to replace the formula with the formula's result when the Cell is displayed.

The "$B$1" tells Excel to use the VALUE in Cell B1. The "$" before the "B" AND before the "1" tell Excel to keep using the value from Cell B1 no matter where the formula is in the spreadsheet. If you were to change Cell B1 from $65 to $70, all of the D Column cells would reflect your base price increase. OK so far?r

The "+" asks Excel to add whatever follows to the value it already found.

(Ah, the interesting part.) The "IF(..." is a logical function. That means that Excel will replace it with one of two results depending on whether the result of its test is TRUE or FALSE. In our case, the test is "Is the value in Cell C2 geater than the value in Cell F1?" (Please note that the "C2" [WITHOUT dollar signs!] will be replaced with the current Column and Row if the formula is copied and pasted. The "$F$1" [WITH dollar signs] will always refer to Cell F1 no matter where the formula is copied.)

Following the test is a comma; it seperates the test from the value to be used if the test evaluates to TRUE. (Out of long-standing habit, I enclose some calculations in parentheses even though they may not need them.) In our case, if the test is true, then Excel takes the value stored in Cell C1 (dollars per mile) and multiplies it by the number of miles beyond the maximum free miles (the "*" means multiply). Because of the way computer math works, we need to enclose the math for deducting the free miles in parentheses, so that the subtraction gets done BEFORE the multiplication, that's why the formula says "$C$1*(C2-$F$1)".

It is very important to match parentheses! Because we had an opening parenthesis at the beginning of the TRUE portion of the IF function, we need to have a closing parenthesis at the end of the TRUE portion.

A comma seperates the TRUE portion of the IF from the FALSE portion. That means, in our case, that if the number of miles is less than or equal to 10 (the value stored in Cell F1), then we add zero to the value stored in Cell B1.

After the FALSE portion of the IF we close the IF function with a closing parenthesis.

Questions?

Reply by Roberta/Or on 7/5/05 7:59pm
Msg #49935

Re: Explanation of Excel spreadsheet formula

Ernest,
Thank you sooo for your generosity and time !!!!!
I have Microsoft Works ..(*/@) and had just finished doing exactly the same thing
for my area......it took 4 solid evenings......BUT I will not have to re-visit it except for
modifications.......
Again thank you for your gift !!!!