Excel Array Formulas Examples

Best Online Course : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects & Case Studies
- Job Placement Assistance
- Get 10% off till Oct 26, 2017
- Batch starts from October 28, 2017

This tutorial explains how to use array formulas in real-world data problems.

Array Formulas
Array formulas are confirmed by pressing Ctrl+Shift+Enter rather than simple ENTER.
Download Workbook

Example I

Suppose you are asked to calculate the sum of the products of column A and column B.
Array Formula Examples
=SUM((A3:A8)*(B3:B8)) is equivalent to [(A3*B3)+ (A4*B4)+....+ (A8*B8)]
Hit CTRL SHIFT ENTER to confirm =SUM((A3:A8)*(B3:B8)). If you hit it correctly, you would see the formula wrapped in curley brackets {}.
 Example II

Suppose you are asked to maximum value based on multiple criteria. The data are shown below -
Maximum Value based on Multiple Conditions
=MAX(IF((A3:A11="High")*(B3:B11="Low"),C3:C11))
Example III

Suppose you are asked to find out the top3 scorers in a class given the students can have same score.
Array Formulas Examples
=INDEX($A$3:$A$9,MATCH(LARGE($B$3:$B$9-ROW($B$3:$B$9)/10^5,ROWS(A$1:A1)),$B$3:$B$9-ROW($B$3:$B$9)/10^5,0))

1. INDEX- MATCH looks for a value in the right column of a table and returns a value in the same row from a column you specify.

2. MATCH always return the first occurrence so we first need to create unique values in range.
$B$3:$B$9-ROW($B$3:$B$9)/10^5
See how the above formula evaluates -
B3 - ROW(B3)/10^5 = 77-(3/100000)
B4 - ROW(B4)/10^5 = 95-(4/100000)
.
.
B9 - ROW(B9)/10^5 = 85-(9/100000)

It gives us unique values -
(76.99997, 94.99996,88.99995,52.99994,94.99993,48.99992,84.99991)

Example IV

We need to extract UNIQUE names from the list.
Extract Unique List
Enter the following formula in cell C3
=INDEX($A$3:$A$9,MATCH(0,COUNTIF($C$2:$C2,$A$3:$A$9),0))

Example V : Sum of Digits
Sum of Digits
=SUM(MID(B3,ROW(INDIRECT("1:" & LEN(B3))),1)*1)
 The INDIRECT function returns a set of text values, in this case the values 1 through number of digits in a cell. The ROW function in turn generates a  multi-cell column array.
ROW(INDIRECT("1:" & LEN(B3)) evaluates to ROW(1:5) as the number of digits in cell B3 is 5.

Example VI : Count Uppercase and lowercase letters
Count Uppercase and Lowercase Letters
Count Uppercase letters
=SUMPRODUCT(LEN(A3)-LEN(SUBSTITUTE(A3,CHAR(ROW(INDIRECT("65:90"))),""))) 
CHAR(ROW(INDIRECT("65:90"))) generates capital A through Z.

Count Lowercase letters
=SUMPRODUCT(LEN(A3)-LEN(SUBSTITUTE(A3,CHAR(ROW(INDIRECT("97:122"))),""))) 
CHAR(ROW(INDIRECT("97:122"))) generates lowercase a through z.

Download Workbook

Excel Tutorials : 100 Excel Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

4 Responses to "Excel Array Formulas Examples"

  1. Nice post. Never tried going into array formulas but this helped a lot to understand the basics of it.

    ReplyDelete
    Replies
    1. Thank you for stopping by my blog. Cheers!

      Delete
  2. thanks my name amarnathverma@outlook.com

    ReplyDelete
  3. Thanks. Know I have got the better undersetting of Array.

    ReplyDelete

Next → ← Prev