Home > Excel Help Portal > Using Excel Formulas

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

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.

1. Excel Statistical Functions: LINEST The purpose of this article is to describe the LINEST function in Microsoft Office Excel 2003, to illustrate how the function is used, and to compare the results of the function for Excel 2003 with the results of LINEST in earlier versions of... KB#828533. Keywords: Add-In, Analysis ToolPak, Solver, Rank, Linear Regression, Statistics, Format, Arrays, Errors, Formulas, Iteration, LINEST, RAND, AVERAGE, SQRT, MMULT

 
2. Proper, and other Text changes -- Use of Special Cells If you want the contents of formulas to also be converted use Bill Manville's code or a variation. (see related below) This page contains some VBA macros. If you need assistance to install or to use a macro please refer to my «Getting Started with Macros« or delve into it deeper on my Install page.... Keywords: AutoCorrect, Sorting, Colors, Fonts, Format, Symbol, Errors, Formulas, Printing, VBA, Macro, Object, Files, Toolbar, HTML, Web, Comments, LOWER, PROPER, FIND, UPPER, TRIM, SUBSTITUTE

 
3. Posting to Newsgroups From: chpearson@gvi.net (Chip Pearson) Date: 1998/01/08 Newsgroups: microsoft.public.excel.misc, microsoft.public.excel.worksheetfunctions, microsoft.public.excel.programming Turning MIME off in Netscape Netscape Navigator 3.0 Options -- Mail & News preferences -- composition -- allow 8-bit turn... Keywords: Query, Sorting, Days, Months, Download, Fonts, Format, Errors, Formulas, Links, Macro, VBA, Setup, Error Message, HTML, Web, Comments, Window, RAND, IS

 
4. SORTING, some notes on sorting in Excel Material on backups has been moved to it's own page. This page contains or refers to some text pages containing VBA macros and User Defined Functions. If you need assistance to install or to use a macro or function please refer to my «Getting Started with Macros« or delve into it deeper on my... Keywords: SERIES, Criteria, Sorting, Days, Download, Borders, Format, Outline, Arrays, Errors, Formulas, Links, VBA, Macro, VBE, Toolbar, Web, Comments, CHAR, LEN, TEXT, LEFT, RIGHT, REPLACE, REPT, VLOOKUP, MATCH, CHOOSE, TRIM

 
5. Install Macros and User Defined Functions The content of this page was formerly part of my Formula and Format page. Install a Macro or User... Keywords: Add-In, OLE, Format, Errors, Formulas, Links, Class, VBA, Macro, Object, UDF, User Defined Function, VBE, Files, Toolbar, Web, Comments, Window, CALL

 
6. Excel Statistical Functions: Implications of Statistical Function Improvements for Analysis ToolPak This article describes the affect of numeric improvements in Microsoft Office Excel 2003 statistical functions on Analysis ToolPak (ATP) tools. Most ATP tools call Excel statistical functions in the process of calculating results. In many cases... KB#829208. Keywords: Analysis ToolPak, Statistics, Errors, Formulas, Error Message, Comments, CORREL, COVAR, RAND, CRITBINOM, NORMINV, VLOOKUP, GAMMAINV, LINEST

 
7. Macro Examples Using XLODBC Functions In the versions of Microsoft Excel mentioned at the beginning of this article, you can use the Microsoft ODBC add-in for Microsoft Excel (XLODBC.XLA) to create macros that interact directly with ODBC through the ODBC driver manager.... KB#124218. Keywords: Add-In, Query, SQL, Arrays, Errors, Class, VBA, Macro, Error Message, Files, Web

 
8. How to Use "On Error" to Handle Errors in a Macro In Microsoft Excel, you can use the On Error statement in a Microsoft Visual Basic for Applications macro (Sub procedure) to trap errors and direct procedure flow to the error-handling statements in a procedure. By using error handling, you make... KB#141571. Keywords: Errors, VBA, Macro, Error Message, Web

 
9. How to Use "On Error" to Handle Errors in a Macro In Microsoft Excel, you can use the On Error statement in a Microsoft Visual Basic for Applications macro ( Sub procedure) to trap errors and direct procedure flow to the error-handling statements in a procedure. By using error-handling, you make... KB#213637. Keywords: Errors, VBA, Macro, Error Message, Web

 
10. Excel FAQ - Excel Application and Files When I start XL, I get the error message "Compile error in Hidden Module" An add-in with a programming error is causing problems. Got the error? Uncheck the one you last checked. If no luck, see if there's an .xla file in the startup directory (see previous tip). Scanner software typically place... Keywords: Add-In, AutoFilter, Dates, Colors, Fonts, Format, Errors, Formulas, Links, Sounds, Startup, VBA, Macro, VBE, AutoSave, Error Message, Files, Toolbar, Hyperlinks, Web, Window

 
11. How to Determine If Your Workbook Contains Links to External Data Excel workbooks can contain external data ranges that retrieve data from the following data sources: Microsoft SQL Server OLAP Services (OLAP provider) Microsoft Access dBASE Microsoft FoxPro Microsoft Excel Oracle Paradox SQL Server... KB#330383. Keywords: Query, DAO, SQL, Fonts, Arrays, Errors, Formulas, Links, Defined Name, PivotTables, VBA, Macro, Web

 
12. FloatingPoint Arithmetic May Give Inaccurate Results This article discusses how Microsoft Excel stores and calculates floating-point numbers. This may affect the results of some numbers or formulas due to rounding and/or data truncation. Overview Microsoft Excel was designed around the IEEE... KB#78113. Keywords: Format, Errors, Formulas, Printing, VBA, Web, ROUND

 
13. How to Create a Visual Basic Automation Addin for Excel Worksheet Functions In Microsoft Excel 2000, you cannot call a function directly in a Component Object Model (COM) Add-in from a worksheet cell formula. Instead, you must create a Visual Basic for Applications (VBA) wrapper for the COM Add-in function so that... KB#285337. Keywords: Add-In, DLL, Arrays, Errors, Formulas, VBA, Object, Registry, Web, RAND

 
14. Excel FAQ - Macros A macro has been added and then removed, leaving an empty module. Empty modules trigger the macro query, as does an actual macro. A macro to remove all VBA code in a workbook is available at Chip Pearson's web site: http://www.cpearson.com/excel/vbe.htm Application.DisplayAlerts = False 'code to... Keywords: Export, Fonts, Errors, Formulas, Password, Class, VBA, Macro, Object, UDF, User Defined Function, UserForm, VBE, Files, Toolbar, Web

 
15. How to Troubleshoot Startup Problems in Microsoft Excel There are several reasons why you may experience problems when you start Microsoft Excel. This article discusses how to troubleshoot the cause of the startup problem, and possible remedies to correct the problem. The article also contains links... KB#280504. Keywords: Errors, Links, Startup, Corrupted, Error Message, File Name, Files, Menu Command, Registry, Toolbar, Web

 
16. How to Create a RealTimeData Server for Excel Microsoft Excel provides a new worksheet function, RTD, that allows you to call a Component Object Model (COM) Automation server for the purpose of retrieving data real-time. This article describes how to use Visual Basic to create a... KB#285339. Keywords: Dates, ActiveX, DLL, Arrays, Errors, Formulas, Class, VBA, Object, Window, RTD

 
17. How to use Global and Local References in Formulas in Excel 2000 In Microsoft Excel, you can define names on a worksheet as either global names or local names. This article explains what global names and local names are. It also shows you how to create global names and local names, as well as how to use them... KB#274504. Keywords: Sales, Errors, Formulas, Defined Name

 
18. How to configure Office applications to run under a specific user account We do not recommend or support automation to a Microsoft Office application from an unattended user account. on why Microsoft does not recommend automation under this context 257757 Considerations for server-side automation of Office For... KB#288367. Keywords: Microsoft Access, Errors, Password, Startup, Printing, Object, VBA, CD-ROM, Registry, Web

 
19. How to handle events for Excel 2003 by using Visual Basic .NET This step-by-step article describes how to handle Microsoft Office Excel 2003 events from an Automation client that you develop by using Visual Basic... KB#822750. Keywords: Arrays, Errors, Class, VBA, Object, Error Message, Web, Window

 
20. How to Return Data from a Dialog Box to a Worksheet In Microsoft Excel, each type of control on a custom dialog box (a drop- down list, an edit box, and others) has a distinct return value that can be returned to a worksheet. This article describes how to create a custom dialog box and contains... KB#141685. Keywords: Fonts, Arrays, Formulas, VBA, Macro, Object, Toolbar, Web

 
21. How to how to Apply Different Passwords or Permissions to Separate Ranges in Workbooks in Excel 2003 This article discusses how to apply different passwords to protect specific ranges of a worksheet in Microsoft Office Excel 2003. In Microsoft Excel 2002 and later versions, you can use passwords to protect specific ranges in your worksheet. This is... KB#826924. Keywords: Errors, Formulas, Password, Error Message, Files

 
22. How to handle Events for Excel by Using Visual Basic .NET This step-by-step article describes how to handle Excel events from an Automation client that is developed with Visual Basic .NET. Overview of Event Handling You can construct an event handler with Visual Basic .NET in the following two... KB#302814. Keywords: Download, Arrays, Errors, Class, VBA, Object, Templates, Web, Window

 
23. How to eliminate redundant data in an Excel list Bill asked this week's question about redundant data. I build a monthly transaction list. At the end of the month, I need to eliminate the redundant data and come up with a total by account code. Each account code can occur multiple times. Bill then described his current methodology which is... Keywords: Advanced Filter, Consolidate, Sorting, Days, Years, Arrays, Formulas, Defined Name, PivotTables, Printing, VBA, Web

 
24. How to Return Data from a Dialog Box to a Worksheet In Microsoft Excel, each type of control on a custom dialog box (a drop-down list, an edit box, and so forth) has a distinct return value that can be returned to a worksheet. This article explains how you can create a custom dialog box; it... KB#213632. Keywords: Fonts, Arrays, Formulas, VBA, Macro, Object, UserForm, Toolbar, Web

 
25. How to use the Excel Lookup Wizard (Lookup.xla) in Excel 2000 This step-by-step article shows you how to use the Lookup Wizard (Lookup.xla) in Excel 2000. The Lookup Wizard is part of a series of add-in wizards that Microsoft has made available to enhance your use of Microsoft Excel. The Lookup Wizard helps... KB#214252. Keywords: Add-In, SERIES, Formulas, Files, INDEX, MATCH

 
26. How to Use the Conditional Sum Wizard in Microsoft Excel This article shows you how to use the Conditional Sum Wizard in Microsoft Excel. The Conditional Sum Wizard helps you calculate the sums of values that meet specified conditions. It is included with Microsoft Excel versions 97 and later. The... KB#214249. Keywords: Add-In, Conditional Sum, Sales, Formulas, Files

 
27. Excel Statistical Functions: PEARSON This article describes the PEARSON function in Microsoft Excel. This article discusses how to use the function, and this article compares results of PEARSON in Excel 2003 with the results of PEARSON in earlier versions of... KB#828129. Keywords: Analysis ToolPak, Format, Arrays, Errors, Formulas, CORREL

 
28. How to apply different passwords or permissions to separate ranges in worksheets in Excel This article shows you how to apply different passwords to protect specific ranges of a worksheet. If you use Microsoft Windows 2000, you can assign permissions rather than passwords to separate ranges of your worksheets. In Microsoft Excel 2002... KB#289270. Keywords: Errors, Password, Error Message

 
29. Excel - adding data to a list People often want to display a form to spreadsheet users in which they can enter data which is then added to a list, possibly on another worksheet. The form may incorporate some form of checking to ensure that the new record is valid. There are two processes described here, one using a visual basic... Keywords: SERIES, Data Table, Format, Errors, Formulas, VBA, Macro, Toolbar, Links, Web, Window

 
30. How to Run Office Macros by Using Automation from Visual Basic .NET This step-by-step article describes how to call Office macros from a Visual Basic .NET Automation client. You can use Microsoft Office Automation to open a document or create a new document that contains a Visual Basic for Applications (VBA) macro... KB#306682. Keywords: Download, Microsoft Access, Styles, Errors, Combo Box, VBA, Macro, Object, Error Message, Web

 
31. Excel Statistical Functions: INTERCEPT This article discusses the INTERCEPT function in Microsoft Excel, illustrates how to use the function, and compares its results for Excel 2003 with its results in earlier versions of... KB#828234. Keywords: Analysis ToolPak, Plot, Linear Regression, Format, Arrays, Errors, Formulas, INTERCEPT, AVERAGE

 
32. How to use Natural Language Formulas in Excel 2000 This step-by-step article shows you how to use natural language formulas in Excel 2000. Natural language formulas are a method of referring to a cell range in a table by using row or column labels as the reference name. Natural language formulas... KB#279412. Keywords: Sales, Format, Arrays, Errors, Formulas, Defined Name, Natural language, Toolbar

 
33. How to Automate Excel from Visual Basic .NET to Fill or to Obtain Data in a Range by Using Arrays This article demonstrates how to automate Microsoft Excel and how to fill a multi-cell range with an array of values. This article also illustrates how to retrieve a multi-cell range as an array by using... KB#302094. Keywords: Download, Arrays, Errors, VBA, Object, Window

 
34. How to Use Natural Language Formulas Microsoft Excel 97 introduces natural language formulas, which is a method of referring to a cell range in a table by using row or column labels as the reference name. Natural language formulas use the intersection feature that is available in... KB#161881. Keywords: Format, Arrays, Errors, Formulas, Defined Name, Natural language, Toolbar

 
35. How to Use Input Boxes with Visual Basic for Applications In Microsoft Excel, you can create a Microsoft Visual Basic for Applications procedure that uses an input box to gather data from a user. To create an input box, you can use the InputBox method or the InputBox function. The main difference between... KB#142141. Keywords: SERIES, Errors, VBA, Macro, Object, Error Message, Web

 
36. Worksheet Functions, HELP Keywords: Download, Format, Formulas, Links, Macro, Object, VBA, VBE, Toolbar, Web, Comments, Window

 
37. How to Use Dynamic Arrays in a Macro In Microsoft Excel, an array can be declared to be dynamic so that the number of elements and dimensions can be changed later while the code is... KB#142134. Keywords: Arrays, VBA, Macro, Web

 
38. How to use the Excel Formula Evaluator in Excel 2002 This step-by-step article explains how to use the new Formula Evaluator feature in Microsoft Excel 2002. When a cell contains a complex formula, you may find it difficult to determine whether or not it returns the correct value, and if not, which... KB#289245. Keywords: Errors, Formulas, Auditing, RAND, CHOOSE

 
39. How to Use Dynamic Arrays in a Macro In Microsoft Excel, an array can be declared to be dynamic so that the number of elements and dimensions can be changed later while the code is... KB#213619. Keywords: Arrays, VBA, Macro, Web

 
40. How to Use a Custom Function in Another Workbook In Microsoft Excel, you can create custom Microsoft Visual Basic for Applications functions to perform calculations. By default, these custom functions are available to all worksheets in the workbook that contains the custom function. Therefore,... KB#141288. Keywords: Errors, Formulas, VBA, Web, Window

 
41. About This Site Thanks for your interest in my web site. I began putting this site together in January of 1998, mostly as a way to learn Microsoft's FrontPage program. I had no idea that it would become so large, or generate so much interest. In June of 1998, I added a hit counter to the main page. Since then,... Keywords: Add-In, Sorting, Format, Formulas, Links, VBA, Templates, Web

 
42. Supercharge your formulas 95% of Excel users do not know about CSE Formulas. When most people hear their real name, they think "That doesn't sound the least bit useful" and never bother to learn about them. If you think SumIf and CountIf are cool, you will soon discover that Ctrl+Shift+Enter (CSE) formulas will run circles... Keywords: Conditional Sum, Arrays, Errors, Formulas, Sounds, VBA, Files, Web, AVERAGE

 
43. How to Use Input Boxes with Visual Basic for Applications In Microsoft Excel, you can create a Microsoft Visual Basic for Applications procedure that uses an input box to gather data from a user. To create an input box, you can use the InputBox method or the InputBox function. The main difference between... KB#213614. Keywords: SERIES, Errors, VBA, Macro, Object, Error Message, Web

 
44. Excel -- Comments -- Programming Programming You can add any of the following macros to a workbook that opens automatically when Excel opens (e.g. Personal.xls), then add a toolbar button or shortcut key to run it. Change the User Name Insert a Plain Comment Insert a Formatted Comment Reset Comments to Original Position Resize... Keywords: Arrays, Errors, VBA, Macro, Object, Toolbar, Comments

 
45. Excel 2002 PostService Pack 2 Hotfix Package: July 11, 2003 This article describes the Microsoft Excel 2002 issues that are fixed in the Microsoft Excel 2002 Post-Service Pack 2 Hotfix package that is dated July 11, 2003. Issues That Are Fixed in the Hotfix Package The following issues are fixed in this... KB#823338. Keywords: Dates, DDE, Arrays, Formulas, Links, VBA, File Name, Files, Registry, Web

 
46. Paste / Pasting Keywords: Borders, Format, Patterns, Errors, Formulas, Sounds, Clipboard, VBA, Macro, Toolbar, Web, Comments

 
47. Excel -- Pivot Tables -- Show/Hide Items Show and Hide Items Show All Items -- Excel 2002 Each field button has an arrow that opens a dropdown list of items. Hide Items -- Excel 2002 You can hide all except one of the items in a field. Show All Items -- Excel 97/Excel 2000 In previous versions of Excel, where there is no Show All... Keywords: Charts, Download, Errors, PivotTables, PivotChart, Class, VBA

 
48. SUMIF Example Using SUMIF to get Subtotals by Date Sum all the amounts per date using a solution provided by Bill Manville (Oxford, England) May 7, 1998 in the microsoft.public.excel.misc newsgroup. caution: In order for this example to work the dates must be sorted or arranged so that same dates appear next to... Keywords: Criteria, Dates, Months, Download, Sales, Arrays, Formulas, CD-ROM, Web, Comments, COUNTIF, INDIRECT

 
49. How to Create and Use OneInput Data Tables in Microsoft Excel This article describes how to create and use one-input tables in Microsoft Excel, which allow you to test how changes in one variable affect a... KB#282855. Keywords: Format, Arrays, Formulas

 
50. Multiple Documents Opened or Errors When Excel Starts When you start Microsoft Excel, Excel attempts to open multiple documents and you may receive one or more of the following error messages: This file is not in a recognizable format. If you know the file is from another program which is... KB#258781. Keywords: Add-In, Format, Errors, Startup, Error Message, Files, Templates

 

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