Microsoft Office Tutorials and References
In Depth Information
{=IF(n=0,0,SUM(IF(MOD(ROW(INDIRECT(“1:”&COUNT(data)))–1,n)=0,data,””)))}
This formula works only when the Data range consists of a single column of values. It does not work for a mul-
ticolumn range or for a single row of values.
To make the formula work with a horizontal range, you need to transpose the array of integers generated by the
ROW function. Excel's TRANPOSE function is just the ticket. The modified array formula that follows works
only with a horizontal Data range:
{=IF(n=0,0,SUM(IF(MOD(TRANSPOSE(ROW(INDIRECT(“1:”&COUNT(Data))))–1,n)=0,Data,””)))}
Using Excel's Formula Evaluator
If you would like to better understand how some of these complex array formulas work, consider using a handy
tool: the Formula Evaluator. Select the cell that contains the formula and then choose Formulas⇒Formula Audit-
ing⇒Evaluate Formula. You'll see the Evaluate Formula dialog box as shown in the figure.
Click the Evaluate button repeatedly to see the intermediate results as the formula is being calculated. It's like
watching a formula calculate in slow motion.
Removing nonnumeric characters from a string
The following array formula extracts a number from a string that contains text. For example, consider the string
ABC145Z . The formula returns the numeric part, 145 .
{=MID(A1,MATCH(0,(ISERROR(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)*1)*1),0),LEN(A1)–SUM((ISERROR(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)*1)*1)))}
This formula works only with a single embedded number. For example, it gives an incorrect result with a string
like X45Z99 because the string contains two embedded numbers.
Determining the closest value in a range
Search JabSto ::




Custom Search