Scribd
Upload
4 views8 pages

Array Formula

An array formula in Excel allows for calculations that return results across a range of cells, requiring the use of Ctrl+Shift+Enter to activate. Common array functions include SUMIF, TRANSPOSE, AVERAGE, and FILTER, each serving different purposes such as summing values based on criteria or transposing data. The IMPORTRANGE function in Google Sheets enables users to import data from one spreadsheet to another, maintaining a live connection for automatic updates.

Uploaded by

fathimanadha9430
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views8 pages

Array Formula

An array formula in Excel allows for calculations that return results across a range of cells, requiring the use of Ctrl+Shift+Enter to activate. Common array functions include SUMIF, TRANSPOSE, AVERAGE, and FILTER, each serving different purposes such as summing values based on criteria or transposing data. The IMPORTRANGE function in Google Sheets enables users to import data from one spreadsheet to another, maintaining a live connection for automatic updates.

Uploaded by

fathimanadha9430
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Array formula

Array formula refers to evaluations whose results are placed in the


range of cells.

Ctrl+Shift+Enter

A group of bracket will automatically enclose the formula to remind


the user that it is an array formula. Some Excel functions are perform
matrix operations such as multiplication ,inverse and transpose and
are implemented as array formula.

Some array formula functions

A. Sumproduct
B. SumIf
C. Average
D. Transpose
E. Array multiplication
F. Filter
G. importRange

Enter an array formula

1. Select the cells where you want to see your results.

2. Enter formula

3. Press Ctrl+Shift +Enter fills each of the cells you selected with the
result.
SUMIF

Product (A) Sales(B)


Apple 100
orange 50
Apple 75
Bananas 40
Apple 25

=sumif(A1:A5,”Apple”,B1:B5)

Result : 200

Ie(100+75+25=100)

TRANSPOSE
To transpose a matrix (or array of data) in Excel using array formulas,
select the desired output range, enter the =TRANSPOSE() formula with
the source range as the argument, and then press Ctrl+Shift+Enter.
Average
The AVERAGE function is one of the simplest , most widely used
statistical functions in spreadsheets. It calculates the mean of a range
of numbers
Array multiplication
Array multiplication allows you to multiply corresponding elements in
two or more ranges (array) and then perform further calculations in
one formula
Filter function
The filter function is one of the most powerful modern dynamic array
tool in spreadsheet. It extract only the rows that match a specific
condition and display them automatically. FILTER produce a clean
separate table containing only the required data without changing or
deleting anything in the original dataset

Syntax

=FILTER(array,include_condition,*if_empty+)

Arguments

Array -> the full data range you want to filter

Include -> The logical test applied to each row

If_empty -> optional message shown if no row match


Name Mark
Asha 55
John 72
Meera 88
Amit 45
Akhil 95

=FILTER(A2:B6,B2:B6>60)

Name marks
john 72
Meera 88
Akhil 95

IMPORTRANGE
The IMPORTRANGE function is powerful tool in Google sheets that
allow you to pull data from one spreadsheet into another. It create a
live connection between files. So whenever the source file change
the imported data update automatically

This function is widely used in department, schools , business terms,


project groups and cloud- based collaboration where multiple users
enter data in different sheets

Syntax

=IMPORTRANGE(“URL”,”SheetNameRange”)

Arguments
“URL” -> The web link of the Google sheet from which you want to
import data

“SheetNameRange” -> The tab name and the exact cell range to be
imported

You might also like