Knowing your ABCs, or rather your 123s, is vital to creating formulas in Excel. You know that 1 + 1 = 2 and that a dozen = 12. When you're using Excel to perform numeric-based calculations, however, it is useful to be familiar with standard math formulas that have real-life applications, such as how much to leave for a tip.
Here are more than ten often-used formulas for ten common needs.
Keep in mind that percentages are a portion of 1. For example, 25 percent is mathematically expressed as .25.
Discounted price (or sale price)
When an item is on sale, it is being offered at a discount. The notification you see may state the discount as an amount (for example, $2 off) or as a percentage (for example, 25 percent off the full price).
To calculate the monetary price point after the discount is applied, you need to know the list price and the discount rate (the percentage of the discount). This formula calculates the discount:
Discounted price = list price X (1 – discount rate)
Here is a calculation that determines the price of a $10 item reduced by 25 percent:
$7.50 = $10 x (1 – .25)
The $10 item costs $7.50 after the discount. Note that $7.50 is 75 percent of $10. The list price is not multiplied by the discount rate, but by 1 minus the discount percentage. In this example, 1 – .25 = .75. Taking this to the final step, the calculation boils down to $10 x .75 = $7.50. $7.50 is the discounted price.
Sales tax
Most items you purchase have a tax added to the total, which in turn provides a grand total (total sales price plus the tax). Taxes are charged as a rate, or percentage. With a tax rate of 5 percent, an item that costs $100 will have an extra 5 percent ($5) added to make the grand total $105.
If you know the price of an item and the sales tax rate, use this formula to calculate the sales tax amount:
Sales tax amount = Item price X tax rate
If you know the grand total and the item price, and want to calculate the sales tax rate, use this formula:
Sales tax rate = (grand total / item price) – 1
Tip
A percentage of an amount is left as a tip. The tip is added to the amount to determine the total amount. The most common place to leave a tip is a restaurant. Suppose that the cost of a meal is $25. You want to leave a 15 percent tip. To calculate the total amount, use this formula:
Total amount = amount of check X (1 + percentage you want to leave for the tip)
When you plug in numbers, the calculation looks like this:
Total amount = 25 x 1.15
In this example, the total amount to leave is $28.75.
Commission
Some people earn some or all of their income as commission. Commission is a percentage of sales. Typically, it's a percentage of how much is sold by a person over a period of time — monthly, quarterly, and so on.
A simple commission structure is a flat rate based on total sales for the period. For example, a person may earn a 6 percent commission rate on sales each month. Assuming that he sold $4,500 worth of goods or services in a month, the calculation to figure out his commission pay is
Commission amount = total sales x commission rate
When you plug in the numbers, you have
$270 = $4500 x 0.06
The commission for the month is $270. Good job!
Loan payments
When you borrow money ,most often you pay interest on the loan. Although there are some interest-free loans, these loans are not common. To calculate the payments on a loan, you need three values: the amount of the loan (the principal), the interest rate (expressed as a percentage), and the number of payments.
Suppose that you borrow $10,000 at an annual rate of 6 percent for 5 years. Payments are monthly, and there are a total 60 payments. Note the need to work with the effective interest rate. To calculate the effective interest rate, divide the annual interest rate by 12. (Payments are monthly.) The effective interest rate is .005.
The formula to calculate the monthly payment is
Payment = principal x ( the effective interest rate / (1 – (1 + the effective interest rate) to the negative exponent of the number of payments))
When you plug in the numbers, the calculation looks like this:
$193.33 = $10,000 x (.005 / (1 – (1 + .005)-60))
The monthly payment is $193.33, paid 60 times over the life of the loan. Altogether, you pay $11,600. Subtract the loan amount of $10,000 to get the total interest paid on the loan: $1,600.
Temperature conversion
The two common scales for measuring temperature are Fahrenheit and Celsius. On the Fahrenheit scale, water freezes at 32 degrees and boils at 212 degrees. On the Celsius scale, water freezes at 0 degrees and boils at 100 degrees. Different countries use one system or the other, so you may need to convert a temperature value from one to the other.
To convert from Fahrenheit to Celsius, use this formula:
Temperature in Celsius = 5/9 x (temperature in Fahrenheit – 32)
To convert from Celsius to Fahrenheit, use this formula:
Temperature in Fahrenheit = temperature in Celsius x 1.8 + 32
Fraction and decimal conversion
You may have any number of reasons to convert a fraction to a decimal or a decimal to a fraction. Subjectively, it is easier to perform mathematical calculations with decimals than with fractions; therefore, converting a fraction to a decimal is a necessary first step. In everyday language, expressed fractions are easier to follow than their decimal equivalents, for example, you would say "My gas tank is down to one-third."
To convert a fraction to a decimal, simply divide the first number (the numerator) by the second number (the denominator). To convert 4/5 to a decimal, for example, structure the formula like this:
Decimal value= numerator/denominator
Or structure it like this:
0.8 = 4/5
Converting a fraction to a decimal is slightly more complex. Look at the number, and separate the part to the left of the decimal point from the part to the right of the decimal point. Next, take the part to the right of the decimal, and draw a line under it or a slash after it. After the line or slash, enter a 1, followed by a number of zeroes that matches how many digits are in the part of the number to the right of the decimal point. Then put the part of the number before the decimal point in front.
If you use 24.25 as an example, this number is separated into 24 and 25. The 25 is followed by a line or slash, followed by 100. Add 24 back by putting it in front of 25/100. The final result looks like this:
24 25/100
The formula, therefore, is
Part of number before decimal point plus part of number after the decimal point followed by a line or slash plus a 1 with a number of zeroes that matches the number of digits of the part after the decimal point
Distance conversion
To convert miles to kilometers, use this formula:
# of kilometers = # of miles x 1.609344
To convert kilometers to miles, use this formula:
# miles = # of kilometers / 1.609344
To convert feet to meters, use this formula:
# of meters = # of feet x 0.3048
To convert meters to feet, use this formula:
# of feet = # of meters / 0.3048
To convert inches to centimeters, use this formula:
# of centimeters = # of inches x 2.54
To convert centimeters to inches, use this formula:
# of inches = # of centimeters / 2.54
Weight conversion
Note that a short ton is currently referred to as a ton (2,000 pounds).
To convert pounds to kilograms, use this formula:
# kilograms = # of pounds x 0.45359237
To convert kilograms to pounds, use this formula:
# of pounds = # of kilograms / 0.45359237
To convert ounces to grams, use this formula:
# of grams = # of ounces x 28.3495231
To convert grams to ounces, use this formula:
# of ounces = # of grams / 28.3495231
To convert metric tons to short tons, use this formula:
# of short tons = # of metric tons / 0.90718474
To convert short tons to metric tons, use this formula:
# of metric tons = # of short tons x 0.90718474
Liquid volume conversion
To convert fluid ounces to centiliters, use this formula:
# centiliters = # of fluid ounces x 2.9573529562499997696
To convert centiliters to fluid ounces, use this formula:
# of fluid ounces = # of centiliters / 2.9573529562499997696
To convert quarts to liters, use this formula:
# of liters = # of quarts X 0.94635294599999995904
To convert liters to quarts, use this formula:
# of quarts = # of liters / 0.94635294599999995904
To convert gallons to liters, use this formula:
# of liters = # of gallons x 3.785411784
To convert liters to gallons, use this formula:
# of gallons = # of liters / 3.785411784
