Thursday, August 28, 2008
HOME

.NET Product Center SPREAD FOR .NET BUNDLE SPREAD FOR WINDOWS FORMS SPREAD FOR ASP.NET INPUT PRO FOR WIN FORMS SUBSCRIPTIONS    SOA Center SPREAD FOR BIZTALK SERVER SPREAD FOR SHAREPOINT SERVER    COM Product Center DEVELOPER TOOLBOX DATABASE PACK SPREAD TAB PRO INPUT PRO LIST PRO CALENDAR OBJX BUTTON OBJX    Download Center TRIALS UPDATES    Purchase Center ORDER NOW GOVT / GSA LICENSE AGREEMENT WORLDWIDE RESELLERS CORPORATE DISCOUNTS WHY BUY?    Support Center TECHNICAL SUPPORT FORUM SUPPORT OPTIONS VIDEO LIBRARY SECTION 508 TRAINING CLASSES    Our Company CUSTOMERS SAY... ABOUT US CONTACT US JOB OPENINGS AWARDS CASE STUDIES WHY CHOOSE FARPOINT  FarPoint Technologies  (800) 645-5913  fpsales@fpoint.com

»Spread for Windows Forms » Product Tour » Formulas » Formulas Managing Formulas   Back to Top  Placing a Formula in Cells You can add a formula to a cell or range of cells. You can also add a formula to all the cells in a row or column. The formula is a string with the expression of the formula, typically containing a combination of functions, operators, and constants. // C# FpSpread1.ActiveSheet.Cells[4, 0].Formula = "SUM(A1:A4)"; ' VB FpSpread1.ActiveSheet.Cells(4, 0).Formula = "SUM(A1:A4)"   Back to Top  Specifying a Cell Reference in a Formula Besides values, operators, and functions, a formula can contain references to values in other cells. For example, to find the sum of the values in two cells, the formula can refer to the cell coordinates by row and column. You can use an absolute cell reference (with the actual coordinates of the row and column) or a relative cell reference (with the coordinates relative to the current cell). You choose which type of cell reference for the sheet by using the RefStyle property. // Set the reference style fpSpread1.Sheets[0].ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1; ' Set the reference style FpSpread1.Sheets(0).ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1   Back to Top  Using a Circular Reference in a Formula You can refer to a formula in the cell that contains that formula. This is a circular reference. This is done typically to recurse on a function to approach an optimum value by iterating on the same function. You can select how many times a function iterates on itself (recurses) by setting the recalculation iteration count property. // Set circular reference fpSpread1.ActiveSheet.Iteration = true; fpSpread1.ActiveSheet.SetValue(0, 1, 20); fpSpread1.ActiveSheet.MaximumChange = 5; fpSpread1.ActiveSheet.MaximumIterations = 5; fpSpread1.ActiveSheet.SetFormula(0, 2, "A1*3"); fpSpread1.ActiveSheet.SetFormula(0, 0, "B1+C1"); ' Set circular reference FpSpread1.ActiveSheet.Iteration = True FpSpread1.ActiveSheet.SetValue(0, 1, 20) FpSpread1.ActiveSheet.MaximumChange = 5 FpSpread1.ActiveSheet.MaximumIterations = 5 FpSpread1.ActiveSheet.SetFormula(0, 0, "B1+C1") FpSpread1.ActiveSheet.SetFormula(0, 2, "A1*3")   Back to Top  Nesting Functions in a Formula You can nest a function within another function in a formula. In this example the sum of the value in two cells (found by using the SUM function) is embedded in a PRODUCT formula. First the cell types are set and the values of the cells are set. // C# fpSpread1.Sheets[0].Cells[3, 1].Formula = "PRODUCT(A1, SUM(A2,A3))"; ' VB FpSpread1.Sheets(0).Cells(3, 1).Formula = "PRODUCT(A1, SUM(A2,A3))"   Back to Top  Recalculating and Updating Formulas Automatically By default, the spreadsheet recalculates formulas in the spreadsheet when the contents of dependent cells change. You can turn this recalculation off. You can also recalculate an individual cell. Also by default, the spreadsheet updates formulas when you insert or delete columns or rows or when you move or swap blocks of cells. You can turn off these automatic formula updates. Use the AutoCalculation property to turn on or off the automatic recalculation of formulas. Use the Recalculate and RecalculateAll methods for recalculating formulas.   Back to Top  Allowing the User to Enter Formulas In many of the cell types, users can type in a formula by simply starting with an equals sign (=). No special property needs to be set to allow this. // Allow users to enter formulas fpSpread1.AllowUserFormulas = true; ' Allow users to enter formulas FpSpread1.AllowUserFormulas = True   Back to Top  Creating and Using a Custom Name Custom, user-defined names are identifiers to represent information in the spreadsheet, used mostly in formulas. A custom name can refer to a cell, a range of cells, a computed value, or a formula. You can define a custom name and then use the name in formulas. When the formula is evaluated, the custom name's value is referenced and evaluated. // Add custom name 'test' FpSpread1.Sheets[0].AddCustomName("test", "$B$1", 0, 0); ' Add custom name 'test' FpSpread1.Sheets(0).AddCustomName("test", "$B$1", 0, 0)   Back to Top  Creating and Using a Custom Function If you have functions that you use on a regular basis that are not in the built-in functions or if you wish to combine some of the built-in functions into a single function, you can do so by defining your own custom functions. They can be called as you would call any of the built-in functions. A custom function can have the same name as a built-in function. The custom function takes priority over the built-in function. Custom functions are dynamically linked at evaluation time. Thus, the application can redefine an existing custom function. To use a custom function: Define the custom function(s). Register the function(s) in the sheet. Use the custom function(s). Managing Formulas

Have a question? RSS Subscribe     Tour Home Overview Appearance User Interaction Cell Types Formulas Managing Data Data Binding Import/Export Printing Keyboard Interaction Shapes Models Spread Designer   Map

Copyright © 1991-2007 FarPoint Technologies, Inc. All rights reserved. All names are property of their respective owners.