Microsoft Office Tutorials and References
In Depth Information
Megaformula Examples
Enter the following array formula into the range A5:A20. This array formula returns a series of 16
consecutive integers:
{=ROW(INDIRECT(“1:16”))}
1. For column B, select B5:B20 and enter the following array formula, which extracts the
digits from the credit card number:
{=MID(\$F\$2,A5:A20,1)}
2. Column C requires an array formula that generates alternating values of 2 and 1.
Such a formula, entered into the range C5:C20, is shown here:
{=(MOD(ROW(INDIRECT(“1:16”)),2)+1)}
3. For column D, select D5:D20 and enter the following array formula:
{=B5:B20*C5:C20}
4. Select E5:E20 and enter this array formula:
{=INT((D5:D20/10)+MOD((D5:D20),10))}
Now the worksheet contains five columns of 16 rows, but only five actual formulas (which are
multicell array formulas).
Build the megaformula
To create the megaformula for this task, start with cell G1, which is the cell that has the final
result. The original formula in G1 is
=IF(MOD(E21,10)=0,”VALID”,”INVALID”)
1. Replace the reference to cell E21 with the formula in E21.
Doing so results in the following formula in cell G1:
=IF(MOD(SUM(E5:E20),10)=0,”VALID”,”INVALID”)
2. Replace the reference to E5:E20 with the array formula contained in that range. Now the
formula becomes an array formula, so you must enter it by pressing Ctrl+Shift+Enter.
After the replacement, the formula in G1 is as follows:
{=IF(MOD(SUM(INT((D5:D20/10)+MOD((D5:D20),10))),10)=0,
“VALID”,”INVALID”)}

Search JabSto ::

Custom Search