Cell Formulas and Functions

Home > Documents and Diagrams Procedures > Sheet Components > Cell Formulas and Functions

Sheet components utilize cell formulas and functions similar to those found in Microsoft® Excel. You can use the formula bar at the top of each sheet to edit longer formulas, insert calculations, and sum columns, for example. As in Excel, you indicate that the cell contains a formula by entering an equals sign (“=”) before the formula.

In iGrafx sheets, when functions evaluate to True, they return a value of 1; when they evaluate to False, they return 0.

Like Excel, cells in sheet components can reference other cells. For example, =A1 is a relative reference to the value in cell A1. When the contents of a cell using a formula with a relative reference is copied or moved, the reference is updated. iGrafx sheets also use absolute references. A dollar sign ($) preceding a row letter or column number in a cell reference specifies an absolute reference. These references will not be updated when moved or copied to other cells. For example, =$A$1 always refers to that particular cell. You can also use mixed references, such as $A1. To refer to a range or block of cells, specify the top left cell and bottom right cell separated by a colon, such as A1:C10.

Named ranges you create can be used in place of typical cell ranges in functions that accept that type argument.

Function	Example Formula	Description and (Result)
ABS	=ABS(25)	Absolute value of 25 (25)
AND	=AND(1+2=3, 2+2=3) =AND(1+2=3, 2+2=4)	Any argument is false (0) All arguments are true (1)
AVERAGE	=AVERAGE(2,4,6)	Average of the numbers (4) You can also enter spreadsheet range.
COUNT	=COUNT(B4:B10) where B6 and B8 do not contain numbers	Counts the number of cells that contain numbers (5)
DATE	=DATE(A1,B1,C1) where A1 contains 2001,B1 contains 3, and C1 contains 7	Serial date for the date given (3/7/2001)
IF	=IF(A6>B6, (“Too Much”, “OK”)) where A6 is 17 and B6 45	Checks if the value in A6 (17) is larger than B6 (45). Returns “OK” because it is not.
INDIRECT	=INDIRECT($A$5) where cell A5 refers to cell B5, which contains 30	Value of the reference in cell A5 (30)
MAX	=MAX(A1:A3) where A1 contains 4, A2 contains 40, and A3 contains 25	Largest of the numbers in this cell range (40)
MEDIAN	=MEDIAN(A1:A3) where A1 contains 2, A2 contains 4, and A3 contains 6	Median of the numbers in this cell range (4)
MIN	=MIN(A1:A3) where A1 contains 4, A2 contains 40, and A3 contains 25	Smallest of the numbers in this cell range (4)
MOD	=MOD(4,2)	Remainder of 4/2 (0)
MODE	=MODE(A1:A5) where A2, A3, and A4 contain 8	Mode, or most frequently occurring number (8)
NOT	=NOT(3+3=6)	Reverses True (0)
OR	=OR(1+1=1, 1+1=2)	Returns True if any argument is True.
PRODUCT	=PRODUCT(A1:A3) where A1, A2, and A3 contain 2	Multiplies the numbers in the cell range (8)
SUM	=SUM(A1:A3) where A1, A2, and A3 contain 2	Adds numbers in the cell range (6)
SUMIF	=SUMIF(A1:A3,”>25”,B1:B3) where A1 contains 15, A2 contains 40, A3 contains 50, B1 contains 30, B2 contains 35, and B3 contains 15	Adds the cells specified by a given criteria. In our example, the Sum of B2 and B3 is given, because A2 and A3 cell values are > 25 (50). The criteria can be in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks.
SUMPRODUCT	=SUMPRODUCT(A1:B2, C3:D4) where A1 contains 1, B2 contains 2, C3 contains 3, and D4 contain4	Multiplies the first element of the first range with the first element of the second range, then multiplies the second element of the first range with the second element of the second range. These two products are then added together (11)