Microsoft Office Tutorials and References
In Depth Information
Megaformula Examples
Column A contains a series of integers from 1 to 16, each representing the digit positions of the
credit card.
Column B contains formulas that extract each digit from cell F2. For example, the formula in cell
B5 is as follows:
=MID($F$2,A5,1)
Column C contains the multipliers for each digit: alternating 2s and 1s.
Column D contains formulas that multiply the digit in column B by the multiplier in column C. For
example, the formula in cell D5 is
=B5*C5
Column E contains formulas that sum the digits displayed in column D. A single digit value in
column D is returned directly. For two-digit values, the sum of the digits is displayed in Column E.
For example, if column D displays 12, the formula in column E returns 3: that is, 1 + 2. The formula
that accomplishes this is as follows:
=INT((D5/10)+MOD((D5),10))
Cell E21 contains a simple SUM formula to add the values in column E:
=SUM(E5:E20)
The formula in cell G1, which follows, calculates the remainder when cell E21 is divided by 10. If
the remainder is 0, the card number is valid, and the formula displays VALID. Otherwise, the
formula displays INVALID.
=IF(MOD(E21,10)=0,”VALID”,”INVALID”)
Convert to array formulas
The megaformula that performs all of these calculations will be an array formula because the
intermediary formulas occupy multiple rows.
First, you need to convert all the formulas to array formulas. Note that columns A and C consist of
values, not formulas. To use the values in a megaformula, they must be generated by formulas —
more specifically, array formulas.
 
Search JabSto ::




Custom Search