Resources on Excel Formulas @ ExcelOptimize.Com - Part 2
Currently Browsing: Excel Formulas
Posted by Andrian in Excel Formulas on 02 19th, 2009 | No Comments

Learn R1C1 Referencing To Understand Formula Copying

Problem: All of a sudden, the column letters along the top of your spreadsheet have been replaced by numbers, as shown in Fig. 197. None of the formulas that you enter will work.Strategy: Relax. There are two ways of naming cells. Someone has turned on the R1C1 style of addressing. To return to the normal...
Read More
Posted by Andrian in Excel Formulas on 02 18th, 2009 | No Comments

Simplify Entry Of Dollar Signs In Formulas

Problem: It is a pain to type the dollar signs in complex formulas such as the formula shown in Fig. 182.Strategy: Use the F4 key as you are entering the formula. The F4 key will toggle a reference through the four possible reference types.As shown in Fig. 183, start to type the formula =E7*(B1.Immediately...
Read More
Posted by Andrian in Basic Excel, Excel Formulas on 02 15th, 2009 | No Comments

Create Easier-To-Understand Formulas With Named Ranges

Problem: As shown in Fig. 206, your worksheet contains several different formulas. It would be easier to understand the results if each component of every formula were named for what it represented and not just for the cell it came from.Strategy: Use named ranges to make formulas easier to understand.1)...
Read More
Posted by Andrian in Excel Formulas on 02 9th, 2009 | No Comments

Copy A Formula While Keeping One Reference Fixed

Problem: You have 5,000 rows of data. As shown in Fig. 173, each row contains a quantity and the unit price. The sales tax rate for all orders is shown in cell C1. After entering a formula to calculate the total with sales tax in the fi rst row, how do you copy the formula down to other rows? If you...
Read More
Posted by Andrian in Excel Formulas on 02 8th, 2009 | No Comments

Create A Multiplication Table

Problem: Create a multiplication table to help your kids in school. In Fig. 176, you want to enter a single formula in cell B2 that can be copied to the entire table. Strategy: In the last chapter, you learned how to use an absolute reference, such as $C$1, so that Excel would not change from column...
Read More
Posted by Andrian in Excel Formulas on 02 8th, 2009 | No Comments

Copy A Formula That Contains Relative References

Problem: You have 5,000 rows of data. After entering a formula to calculate Gross Profit Percent for the first row, as shown in Fig. 167, how do you copy the formula down to other rows? Strategy: All of the cell references in the formula are known as relative references. The amazing thing about Excel...
Read More
 Page 2 of 2 « 1  2