Resources on Excel Formulas @ ExcelOptimize.Com
Currently Browsing: Excel Formulas
Posted by Andrian in Excel Formulas on 12 26th, 2009 | No Comments

New functions in Excel 2007

New functions in Excel 2007
Excel 2007 also add some new functions that not available on previous version, those functions are: IFERROR—Used to check for an error, and display a message or perform a different calculation. AVERAGEIF— Used to calculate a conditional average (similar to SUMIF and COUNTIF). AVERAGEIFS—Used to...
Read More
Posted by Andrian in Basic Excel, Excel Formulas on 02 28th, 2009 | No Comments

How To Join Two Text Columns

Problem: As shown in Fig. 257, you have data with First Name in column A and Last Name in column B. You want to merge these into one column.Strategy: Use the ampersand (&) as a concatenation operator in a formula in column C. Change the formulas in column C to values before deleting columns A and...
Read More
Posted by Andrian in Basic Excel, Excel Formulas on 02 27th, 2009 | No Comments

How To Calculate Sales Over Quota

Problem: In the spreadsheet shown in Fig. 253, enter a formula to calculate the excess of sales over quota on a record-by-record basis.Strategy: There are a couple of functions that would work in this situation. For instance, you could use an IF function. Give the IF function a logical test and specify...
Read More
Posted by Andrian in Basic Excel, Excel Formulas on 02 26th, 2009 | No Comments

Add Two Columns Without Using Formulas

Problem: You prepared a summary of sales by rep for the month. Due to an accounting glitch, someone gave you a similar fi le with additional sales made on the last day of the month, as shown in Fig. 248. You need to add the new sales to the old sales. There is no need to keep the original two columns...
Read More
Posted by Andrian in Basic Excel, Excel Formulas on 02 25th, 2009 | No Comments

Count, average, etc. Without using a formula

Problem: Your manager calls on the telephone and asks for the average price of a particular product. You need to quickly find an average of the prices in Fig. 245. Is there a faster way than entering a formula?Strategy: The QuickSum feature in the status bar can be customized to show Average, Min, Max,...
Read More
Posted by Andrian in Basic Excel, Excel Formulas on 02 24th, 2009 | No Comments

Total Without Using A Formula

Problem: Your manager calls on the telephone and asks for the total sales of a particular product. You need to quickly find a total. Is there a faster way than entering a formula?Strategy: The QuickSum indicator in the status bar will show the total of the highlighted cells. With your manager on the...
Read More
Posted by Andrian in Basic Excel, Excel Formulas on 02 23rd, 2009 | No Comments

Assign A Formula To A Name

Problem: You have thousands of identical formulas on 20 worksheets, as shown in Fig. 223. Every time that you want to change the formula, you have to edit all 20 sheets. Is there a way to make a formula be variable and change it in just one place?Strategy: Use a Name, but assign a formula to the name.Think...
Read More
Posted by Andrian in Basic Excel, Excel Formulas on 02 22nd, 2009 | No Comments

Use Natural Language Formulas To Refer To The Current Row

Problem: In the previous example, a natural language formula referred specifically to one cell by indicating a row name and a column name. It is also possible to have natural language formulas that refer to a specific column in the current row.Strategy: As shown in Fig. 221, you have a census of employees...
Read More
Posted by Andrian in Basic Excel, Excel Formulas on 02 21st, 2009 | 3 Comments

Build A Formula Using Labels Instead Of Cell Addresses

Problem: You hate using cell references such as B2 in formulas.Strategy: Use natural language formulas. These formulas are fairly amazing. Excel has offered support for natural language formulas for many versions. With these formulas, you can use the headings in a worksheet to describe which cells you...
Read More
Posted by Andrian in Basic Excel, Excel Formulas on 02 20th, 2009 | No Comments

Use Named Constants To Store Numbers

Problem: You’ve seen how you can assign a name to a cell. It is also possible to assign a name to a constant. This could be useful if you have a number, such as a local sales tax rate, that changes once a year.Strategy: From the menu, use Insert – Name – Define. Type a name like SalesTax. In the...
Read More
 Page 1 of 2  1  2 »