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

**Array Formulas**

Array formulas are confirmed by pressingCtrl+Shift+Enterrather than simpleENTER.

**Download Workbook**

**Example I**

Array Formula Examples |

=SUM((A3:A8)*(B3:B8))is equivalent to [(A3*B3)+ (A4*B4)+....+ (A8*B8)]

HitCTRL SHIFT ENTERto confirm =SUM((A3:A8)*(B3:B8)). If you hit it correctly, you would see the formula wrapped incurley brackets {}.

**Example II**

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 toROW(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

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

ReplyDeleteThank you for stopping by my blog. Cheers!

Deletethanks my name amarnathverma@outlook.com

ReplyDeleteThanks. Know I have got the better undersetting of Array.

ReplyDeletein example 2, why are we dividing by 10 and raise it to 5?thanks

ReplyDeleteINDEX($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))

ReplyDeleteWould you mind to explain 10^5 within this formula please as I am trying to understand where does it from + (A$1:A1)) as the cells is empty