SimpleArrayExamples

SimpleArrayExamples - ExcelArrayFormulas Excel array...

Info iconThis preview shows pages 1–7. Sign up to view the full content.

View Full Document Right Arrow Icon
Excel Array Formulas Excel array formulas allow you to write concise formulas that perform operations that might not be included as Excel built-in functions. This workbook contains several examples of using array formulas.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Operation Examples Multiply all elements of an array constant by 2. 2 4 6 8 10 12 14 16 18 20 22 24 Use the SQRT function on a 5-value array constant. 1.0000 1.4142 1.7321 2.0000 2.2361 Use the SQRT function on a range of 5 values. 1 2 3 4 5 -the range 1.0000 Err:522 Err:522 Err:522 2.2361 -the result
Background image of page 2
Example: Using an array formula        to eliminate intermediate formulas Product Units Sold Unit PriceNormal Calcs Widgets 3 $50 $150 Ridgets 10 $100 $1,000 Lidgets 5 $20 $100 Kidgets 9 $10 $90 Fidgets 3 $60 $180 Zidgets 1 $200 $200 $1,720 -SUM formula  $1,720  -a single array formula that can replace the SUM formula and the six  formulas in D4:D9.
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Conditional Sum Example with an Array Find the total of sales made by salesperson Smith. Name Sale Smith $880 Excel built-in function: The SumIF method Jones $350  $1,896  Wicket $441 Pickford $280 Aguila $385 The array formula method Fortes $281  $1,896  Jordan $300 Michard $250 Smith $201 Willard $398 Ellis $440 Smith $595 Pickford $560 Smith $220 Another method: A Pivot Table Sum of Sale Name Total Smith $1,896
Background image of page 4
An array formula: Calculate a single result 2000 Electronics    Europe 161,000    NA 198,200 Motors    Europe 35,000    NA 60,000   Add this new data into the mix. Acoustics   The array formula handles it.    Europe 160,700 The average function doesn't.    NA 190,100   Studio      Europe 153,900    NA 190,700 Array formula Using the AVERAGE Function Average Europe 158,533 158,533 Average NA 193,000 193,000 Use array formulas to find the average revenue for all divisions in both sales areas.
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
An array formula: SUM plus SMALL Sum the 3 smallest values in a range 12 -5 3 Sum of smallest: -3 2 18 4 5 25 Note: 15 The syntax of the SMALL function is =SMALL(array,k) where array  7 is a range of numerical data for which you want to find the kth smallest value. 99 23 This generates a new array that consists of the three smallest values in the range.  6 This array is then passed to the SUM function, and it returns the sum of the values  14 in the new array; in this example, -3. This is the processing Excel goes through: 28 Step 1. Excel evaluates the SMALL function using a second argument of 1. It returns –5. 0 Step 2. Excel evaluates the SMALL function using a second argument of 2. It returns 0.
Background image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 7

SimpleArrayExamples - ExcelArrayFormulas Excel array...

This preview shows document pages 1 - 7. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online