Home > Excel Help Portal > Using Excel Formulas

<<  (Page 2)  Previous              Next  (Page 4)  >>

Using Excel Formulas

The ExcelUser portal offers links to help pages created by top
Excel web sites. This section discusses ways to use Microsoft
Excel formulas.

101. Populate Dialog This example shows the scoring dialog that the assistant is presented after transferring all the answers. While preparing this example, I saw a few things I plan to repair the next time I can go into the code. This was written for Excel 7.0/95 and works well in Excel... Keywords: Formulas, Links, VBA

 
102. Help using Excel spreadsheet functions - If, Rank and Errors The IF function allows you to select one of two alternative values which are dependent upon a specified condition. It is in the format: =IF(Condition, Value if True, Value if False). If the condition is true then the first value is returned, otherwise the second is used. IF() initially gives you... Keywords: SERIES, Rank, Errors, Formulas

 
103. How to create Functions to Calculate Light Years in Excel 2000 This step-by-step article shows you how to create functions in Excel 2000 to calculate light years. Microsoft Excel does not include a function that converts miles into light years or light years into miles. This article explains how to create... KB#214260. Keywords: Years, Format, Formulas, VBA, Web

 
104. Excel functions for calculating loan repayments Banks generally set repayments on loans and mortgages in equal payments over a fixed period of time. Within these regular payments the mix of interest and principal changes as time passes. A single formula can easily calculate the repayments on a loan of £x,000 at y% over z years. The real world is... Keywords: SERIES, Days, Months, Years, Formulas, Links, Sounds, PMT, CUMPRINC

 
105. How to Add Data to a DropDown or List Box In Microsoft Excel, there are two ways of populating a list or drop-down box: you can link worksheet data to the control, or you can run a Microsoft Visual Basic for Applications macro to add data to the... KB#141573. Keywords: Format, Arrays, Formulas, VBA, Macro, Object, Links, Web

 
106. How to Implement Application.AutomationSecurity In Office 2000, automation is considered a trusted state, and, therefore, macros in a file are turned on by default, regardless of the security setting. Application.AutomationSecurity allows Office XP programs to support security for solutions... KB#317405. Keywords: Errors, VBA, Macro, Object, Files

 
107. Produce Color Chart Option Explicit Public ColChoice As Variant, ThrdTier, RowPos As Integer Public BackCol, ForeColor, FirsTier, SecTier, f1STpACK, s2NDpACK, t3RDpACK, POS Public firstDec, secDec, thirdDec, DecChoice, CompleteY, QuoTe As... Keywords: Charts, Colors, Fonts, Arrays, Formulas, Links, Printing, VBA, Macro, HTML, Web

 
108. Protection, Worksheet Keywords: Borders, Colors, Format, Styles, Errors, Formulas, Password, VBA, Macro, Web, Comments, Validation

 
109. SumIf with 2 conditions Bill send in this week's question. I have a database of events and my boss wants me to plot frequency charts by month. I read your trick to change daily dates to monthly dates and about CSE formulas. I have tried every criteria I can think of in the CountIf formula below to get it to look at 2... Keywords: Charts, Plot, Criteria, Dates, Arrays, Formulas, PivotTables, VBA, Web, COUNTIF

 
110. Use the Operation choices in Paste Special I was talking to Steve on the telephone the other day when I came up with the idea for this tip. Steve had a table of monthly sales figures. Because two product lines had been merged, Steve needed to add all of the sales figures from product D into the sales figures for product C. As I listened... Keywords: Days, Sales, Errors, Formulas, Clipboard, VBA, Web

 
111. Multi-Line Ascii Report You can easily customize this procedure to deal with any variety of ASCII reports. You need to figure out how many physical printed lines make up a single logical record on the report. MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed... Keywords: Sorting, Formulas, VBA, Web

 
112. Visual Basic Code to Use Instead of DIRECTORIES() This article contains sample Visual Basic for Applications code that you can use to duplicate the behavior of the DIRECTORIES() function that is included in the Microsoft Excel 4.0 Filefns.xla add-in. NOTE : The Filefns.xla add-in is not included... KB#213474. Keywords: Add-In, Query, Arrays, Errors, VBA, Macro, Web

 
113. Method to Determine Whether a Year Is a Leap Year This article contains information that explains how to determine whether the year in a date used in a Microsoft Excel document is a leap... KB#214019. Keywords: Days, Years, Errors, Formulas

 
114. How to use the INDEX and LINEST Functions to Solve for New XValues in Excel 2000 This step-by-step article explains how to determine values for a set of unknown x-values when you are given a set of known x-values and known y-values. Solve the Linear Equation To determine new x-values for a given set of data, you must solve... KB#214251. Keywords: Format, Arrays, Formulas, INDEX, LINEST

 
115. How to Use INDEX and MATCH Worksheet Functions with Multiple Criteria This article offers several examples that use the INDEX and MATCH worksheet functions in Microsoft Excel to find a value based upon multiple... KB#214142. Keywords: Criteria, Arrays, Formulas, INDEX, MATCH

 
116. How to Calculate Interest Paid for Multiple Periods You can use the CUMIPMT worksheet function to return the cumulative interest paid on a loan between a start period and an end period. You can also use the IPMT worksheet function to return the interest payment for a single given period for... KB#71952. Keywords: Add-In, Analysis ToolPak, Months, Arrays, Formulas, CUMIPMT, ROW

 
117. Registry Keys That Control the File Repair Feature Microsoft Excel 2002 contains a new feature that allows you to recover data from corrupted workbooks. This article describes how to use the registry to override the default behavior when Excel attempts to recover... KB#289840. Keywords: Formulas, Object, VBA, Corrupted, Registry

 
118. Excel Hall of Fame Tushsar Meta's set of user defined function was nominated as the Answer of the Week. The Question: Lucas in London posed this question. He has a series of raw data in column A of his worksheet. He would like a function that would (a) calculate the percentage difference between each data point, then... Keywords: SERIES, Arrays, Formulas, VBA, Object, User Defined Function, Web

 
119. Build A Formula Most of the salesmen’s names were in the system like AB, RD, TS, etc. Occasionally 2 salesmen would split a commission and I had to work out a scheme that my program could always recognize.The syntax is 2 initials, the first commission percentage, 2 more initials and the second percentage; i.e.... Keywords: Sales, Errors, Formulas, Links, VBA

 
120. How to use NETWORKDAYS with Multiple Holidays in Excel 2000 This step-by-step article describes how to use the NETWORKDAYS function with multiple holidays in Microsoft Excel 2000. When you use the NETWORKDAYS function with multiple holidays, you can enter the holidays as cell references or in the serial... KB#259200. Keywords: Analysis ToolPak, Dates, Days, Holidays, Networkdays, Format, Arrays, Errors, Formulas, DATEVALUE

 
121. Testing your spreadsheets - tips on good design Spreadsheets may contain hidden errors. These can present a degree of risk whenever decision making is based upon your calculations. Follow these steps to reduce the risk of errors occurring. Errors are easier to detect if the spreadsheet is well designed. It is best to create three distinct and... Keywords: Months, Format, Errors, Formulas, Links, Macro, Files, AVERAGE

 
122. Linked Files with Lookup Functions Updated Much More Slowly Than in Earlier Versions of Excel When you click Yes to update the links on a workbook that you are opening, Microsoft Excel appears to stop responding (hang). After a long wait, the links are updated, and you gain control of the worksheet. The same workbook may have been updated... KB#248207. Keywords: Formulas, Links, Files, Registry, VLOOKUP, HLOOKUP

 
123. Sample UserDefined Function to Hide Formula Errors Some formulas in Microsoft Excel return error values under certain conditions. For example, when you use a division formula that multiplies a number by zero, you receive the following error value: #DIV/0! Using the sample formula 100/0, you can... KB#280094. Keywords: Errors, Formulas, VBA, Macro, Web

 
124. Excel dynamic graphs using Names, Series and Offset Line graphs (charts) are an appropriate format for showing values over a period of time. Unfortunately, if there is one data series which has fewer categories than another and contains values of zero, a line plotted by Excel will correspondingly fall to zero on the Y axis. This looks misleading on... Keywords: Charts, SERIES, Data Table, Months, Format, Formulas, Defined Name, OFFSET

 
125. Excel -- Data Validation Data Validation 1. Data Validation -- Introduction 2. Data Validation -- Create Dependent Lists 3. Hide Previously Used Items in a Dropdown List 4. Display Messages to the User 5. Use a List from Another Workbook 6. Validation Criteria Examples 7. Custom Validation Criteria Examples 8. Data... Keywords: Criteria, Download, Format, Errors, Formulas, Validation

 
126. How to turn Off the Message for pdating Links in a Workbook in Excel This step-by-step article describes how to control the startup message about updating linked workbooks in Microsoft Excel. When you open a workbook that contains links to cells in other workbooks, you may receive the following message: This... KB#288851. Keywords: Links, Startup

 
127. Protecting cells, sheets, workbooks, and files Excel offers many ways to protect your data. But understanding the differences between the methods and the limitations of each of them can be daunting. Here's a quick review of four protection options in Excel 97 and later, plus explanations of how to use them. Note: These protection features... Keywords: Charts, Sorting, Format, Formulas, Password, Printing, VBA, Files, Window

 
128. Unsigned Macros May Be Trusted When Macro Security Setting Is High When you start Microsoft Excel, Excel opens an unsigned workbook that contains Microsoft Visual Basic for Applications macros, and enables the macros in the workbook, even though you have set the macro security level to... KB#235069. Keywords: Errors, Startup, Macro, VBA, Templates

 
129. Share your spreadsheets on the Web If you use Excel 2000 or later, it's relatively easy to save a workbook in HTML as a web document. In fact, Excel supports two types: static documents and interactive documents After creating your workbook, use the File, Save as Web Page command. Specify what you want to save (a single worksheet or... Keywords: Charts, Dates, Microsoft Access, Format, Arrays, Errors, Formulas, Printing, Error Message, Files, HTML, Internet Explorer, Web, Window

 
130. How to Create Functions to Calculate Light Years Microsoft Excel does not include a function that will convert miles into light years or light years to miles. However, you can use the following formulas for this calculation Miles to Light Years M / 5,877,000,000,000 Light Years to Miles LY... KB#142152. Keywords: Years, Format, Formulas, VBA, Web

 
131. How to change the Assembly Name or the Assembly Namespace for Your Office Managed Code Extension This step-by-step article describes how to change the name of an assembly or how to change the namespace of an assembly for a managed code extension that you create with Microsoft Visual Studio Tools for the Microsoft Office System. To change the... KB#824003. Keywords: Errors, Error Message

 
132. How to Manually Recalculate a Single Cell or Range In Microsoft Excel 2000, the Calculate Now command (on the Tools menu, click Options , and then click the Calculation tab) causes every cell in the open worksheet to be recalculated, even when the calculation mode is set to Manual . This... KB#213490. Keywords: Arrays, Formulas, VBA, Web

 
133. Cell Related Coding last cell even with empty cells in the column you could use Range("A65535").End(xlUp).select 'Example only Range("A" & Rows.Count).End(xlUp).Select Cells(Rows.Count,1).End(xlUp).Select Range(ActiveCell, ActiveCell.End(xldown)).Select xldown xlup xltoRight xltoLeft The use of 65536 to identify the... Keywords: Arrays, Formulas, VBA, Toolbar, Web, Comments, MAX

 
134. How to Create a Conditional Format to Hide Errors In Microsoft Excel, you can create conditional formatting on a cell, or a range of cells, so that error values are not displayed in the... KB#154874. Keywords: Conditional Formating, Custom Number Formats, Errors, Formulas

 
135. RTD Servers Used with Embedded Excel Workbooks May Be Problematic You may experience a number of problems when you host an Excel workbook that contains RealTimeData (RTD) formulas in an OLE or ActiveX Document Container such as Microsoft Internet Explorer. These problems may include the following: Performance... KB#284878. Keywords: ActiveX, OLE, Arrays, Formulas, Internet Explorer, Window, RTD

 
136. Word Stops Responding When You Use "Open Clip In" to Open a WMF File When you right-click a Windows Metafile (WMF) in the Insert Clip Art task pane, and then click Open Clip In, Microsoft Word stops responding for a long time, and then the Word File Conversion dialog box... KB#285629. Keywords: Errors, Files, Web

 
137. How to count Unique Elements in a Cell Range in Excel This step-by-step article shows you how to use the FREQUENCY function in an array formula to count the unique elements in a cell range. Count Unique Number Elements The following formula counts the number of unique values found in the range A1:A10... KB#268001. Keywords: Conditional Sum, Arrays, Formulas, FREQUENCY

 
138. How to Programmatically Suppress an Error Value Returned by a Formula Microsoft Excel returns an error value for any cell that contains a formula that cannot be properly calculated. These error values include the following: #DIV/0! #N/A #NAME? #NULL! #NUM! #REF! #VALUE! This article describes a Microsoft Visual... KB#213387. Keywords: Errors, Formulas, VBA, Macro, Web

 
139. How to Count Cells That Contain Formulas, Text, or Numbers In Microsoft Excel, you can count the number of cells in a worksheet that contain formulas, text, or numbers, by using the Go To Special dialog box to select the cells and then running a macro that counts the number of selected... KB#213507. Keywords: Errors, Formulas, Command Button, VBA, Macro, Object, Web

 
140. How to Use a Logical AND or OR in a SUM+IF Statement in Excel In Microsoft Excel, when you use the logical functions AND and/or OR inside a SUM+IF statement to test a range for more than one condition, it may not work as expected. A nested IF statement provides this functionality; however, this article... KB#267982. Keywords: Criteria, Dates, Arrays, Formulas, DSUM, DATEVALUE

 
141. How to Determine the Number of Unique Items in a List The purpose of this article is to describe how to determine the number of unique items contained in a list in Microsoft Office Excel... KB#823573. Keywords: Arrays, Formulas

 
142. How to Use the INDIRECT Function to Create References In Microsoft Excel, the INDIRECT worksheet function returns the contents of the specified reference and displays its contents. The INDIRECT worksheet function can be used to create linked references to other workbooks. Each attribute of the... KB#151323. Keywords: Errors, Formulas, Links, INDIRECT

 
143. Excel -- Conditional Formatting -- Examples Examples Hide Errors Hide Duplicate Values Highlight Duplicates in Column Highlight Lottery Numbers Highlight Upcoming Expiry Dates Hide Cell Contents When Printing Hide Errors You can use conditional formatting to check for errors, and change the font colour to match the cell colour. In this... Keywords: Sorting, Dates, Days, Download, Colors, Fonts, Format, Errors, Formulas, Printing, COUNTIF

 
144. How to Use Data Validation to Prevent Duplicate Entries This article explains how to use data validation to prevent you from making duplicate entries in a... KB#189005. Keywords: Styles, Errors, Formulas, Error Message, Toolbar, Validation

 
145. How to Count the Occurrences of a Number or Text in a Range This article explains how you can use worksheet functions in Excel 2000 and later to count the number of occurrences of a specific number or text string in a range of cells on a worksheet. The More Information" section of this article contains... KB#214153. Keywords: Wildcard, Arrays, Formulas, COUNTIF

 
146. Alternatives to Nested IF Functions Excel's IF function provides some simple decision-making capability to a worksheet. The IF function accepts three arguments: The formula below, for example, returns 1 if cell A1 contains "A". If cell A1 does not contain "A", the formula returns an empty string. =IF(A1="A",1,"") For more... Keywords: Charts, Format, Formulas, Printing, VBA, Web, VLOOKUP, CONCATENATE

 
147. Count Occurences of a Digit There were perhaps 6 columns (in the test file), each having from 3 to fifteen five-digit numbers. At the end of the test file, I found a new "wrinkle that I chose to ignore. It called for totals by... Keywords: Formulas, Links, VBA

 
148. Help using Excel spreadsheet functions - Using Array formulae An array is simply a series of data, which in a spreadsheet is normally stored in a range of adjacent cells. Excel however can do special things with arrays that may be used as a flexible alternative to the database functions such as DSUM. Many functions such as SUM or MAX can be combined with the... Keywords: SERIES, Criteria, Query, Arrays, Errors, Formulas, DSUM, MAX, COUNTIF, VALUE

 
149. Naming a Worksheet as the value of a target cell Use this... Keywords: Add-In, Errors, Formulas, Links, Macro, VBA, Web, INDIRECT

 
150. Excel -- Data Validation -- Hide Previous Selections Hide Previously Used Items in Dropdown Thanks to Excel MVP, Peo Sjoblom, for the brilliant array formula that makes this work! Set up the Main Table Create the List of Items Create the Validation List Apply the Data Validation Test the Data Validation You can limit the choices in a Data Validation... Keywords: Criteria, Download, Arrays, Formulas, Validation, INDIRECT, OFFSET

 

<< (Page 2)  Previous              Next  (Page 4) >>