[ Pobierz całość w formacie PDF ]
.Consider this formula:=(145*1.05)/A1If you want to convert just the expression within the parentheses to a value, get intocell edit mode and select the part that you want to evaluate.In this example, select145*1.05.Then, press F9 followed by Enter.Excel converts the formula to thefollowing:=(152.25)/A1When you edit a formula, you can select multiple characters by dragging themouse over them or by holding down Shift while you use the arrow keys.You canalso press Home or End to select from the cursor position to the beginning or endof the formula.If you use Ctrl+Shift, pressing the arrow keys allows you to select words within the formula.Suppose you have a lengthy formula that contains an error, and Excel won tlet you enter it because of the error.In this case, you can convert the formulato text and tackle it again later.To convert a formula to text, just remove theinitial equal sign (=).To try the formula again, insert the initial equal sign toconvert the cell contents back to a formula.Using Operators in FormulasAs previously discussed, an operator is the basic element of a formula.An operatoris a symbol that represents an operation.Excel supports the following operators: 4800-x Ch02.F 8/27/01 11:54 AM Page 3636 Part I: Basic Information+ Addition- Subtraction/ Division* Multiplication% Percent& Text concatenation^ Exponentiation= Logical comparison (equal to)> Logical comparison (greater than)= Logical comparison (greater than or equal to)0 ,data)-SUMIF(data, >5 ,data) 4800-x Ch15.F 8/27/01 11:57 AM Page 400400 Part IV: Array FormulasThis formula sums the values that are greater than zero, and then subtracts thesum of the values that are greater than 5.This can be confusing.Following is an array formula that performs the same calculation:{=SUM((Data>0)*(Data0,Data0,ROW(INDIRECT( 1: &ROWS(Data)))),ROW(INDIRECT( 1: &ROWS(Data))))), ,INDEX(Data,SMALL(IF(Data>0,ROW(INDIRECT( 1: &ROWS(Data)))),ROW(INDIRECT( 1: &ROWS(Data))))))} 4800-x Ch15.F 8/27/01 11:57 AM Page 415Chapter 15: Performing Magic with Array Formulas 415Figure 15-10: Using an array formula toreturn only the positive values in a rangeReturning Nonblank Cells from a RangeThe following formula is a variation on the formula in the previous section.Thisarray formula works with a single-column vertical range named Data.The arrayformula is entered into a range of the same size as Data, and returns only the non-blank cell in the Data range.{=IF(ISERR(SMALL(IF(Data ,ROW(INDIRECT( 1: &ROWS(Data)))),ROW(INDIRECT( 1: &ROWS(Data))))), ,INDEX(Data,SMALL(IF(Data ,ROW(INDIRECT( 1: &ROWS(Data)))),ROW(INDIRECT( 1: &ROWS(Data))))))}Reversing the Order of the Cells in a RangeThe following array formula works with a single-column vertical range (namedData).The array formula, which is entered into a range of the same size as Data,returns the values in Data, but in reverse order.{=IF(INDEX(Data,ROWS(data)-ROW(INDIRECT( 1: &ROWS(Data)))+1)= , ,INDEX(Data,ROWS(Data)-ROW(INDIRECT( 1: &ROWS(Data)))+1))}Figure 15-11 shows this formula in action.The range A2:A20 is named Data,and the array formula is entered into the range C2:C20. 4800-x Ch15.F 8/27/01 11:57 AM Page 416416 Part IV: Array FormulasFigure 15-11: A multicell array formula reversesthe order of the values in the range.Sorting a Range of Values DynamicallySuppose your worksheet contains a single-column vertical range named Data.Thefollowing array formula, entered into a range with the same number of rows asData, returns the values in Data, sorted from highest to lowest.This formula worksonly with numeric values, not with text.{=LARGE(Data,ROW(INDIRECT( 1: &ROWS(Data))))}To sort the values in Data from lowest to highest, use this array formula:{=SMALL(Data,ROW(INDIRECT( 1: &ROWS(Data))))}This formula can be useful if you need to have your data entry sorted immedi-ately.Start by defining the range name Data as your data entry range.Then enterthe array formula into another range with the same number of rows as Data
[ Pobierz całość w formacie PDF ]