http://www.mrexcel.com/tip096.shtml
http://www.exceluser.com/explore/arrays1.htm
Let's begin our examination of this data by summarizing the sales for Jill and Joe. Cells J3 and J4 perform this summary by using array formulas.
http://www.i3bar.com/
http://www.exceluser.com/explore/arrays1.htm
Introducing Array Formulas, Example 1
Let's begin our examination of this data by summarizing the sales for Jill and Joe. Cells J3 and J4 perform this summary by using array formulas.
Here's the formula for the cell shown:
J3: {=SUM(IF(Seller=$I3,Total,0))}
Notice the braces that surround this formula. You do NOT enter those characters when you key in the formula. Instead, you type the formula shown within the braces. But when you're done typing you don't press Enter. Instead, you hold down the Ctrl and Shift keys, then press Enter. After you do so, Excel displays the formula in the formula bar with the braces, as shown above.
Here's the key to understanding how array formulas work: Each array formula creates temporary arrays in memory, and then the outside function returns the results from that array.
To illustrate, the "Seller=$I3" part of the formula creates a temporary array like this: {FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE; FALSE;TRUE;FALSE;TRUE;FALSE;FALSE} Here, the second element in the array is TRUE. That is, the second cell of the Seller range, cell A3 contains the text "Joe".
The formula says that wherever TRUE appears, return the corresponding value from the Total column. Therefore, the statement
"IF(Seller=$I3,Total,0)" returns the temporary array: {0;12600;0;0;0; 5060;0;1980;0;1540;0;4500;0;0}
"IF(Seller=$I3,Total,0)" returns the temporary array: {0;12600;0;0;0; 5060;0;1980;0;1540;0;4500;0;0}
Finally, the SUM function returns the sum of this temporary array: 25,680.
The second formula is similar:
J3: {=SUM(IF(Seller=$I4,Total,0))}
You can copy this formula from cell I3, or enter it for the practice. Again, if you enter it, do NOT manually enter the braces. Instead, when you enter the formula using Ctrl-Shift Enter, the braces will appear automatically.
http://www.i3bar.com/
Spreadsheets remain the predominant tool for reporting, analysis, budgeting and forecasting purposes by all companies, however, few are trained to realize its full potential.
The i³BAR provides the methodology and know-how to enable users to build integrated and interactive Excel models to meet their unique business challenges.
