AVERAGEIFS

Excel Functions 🇮🇩 Bahasa Indonesia

Rata-rata sel yang cocok dengan beberapa kriteria

Syntax

EXCEL

=AVERAGEIFS(avg_rng, range1, criteria1, [range2], [criteria2], ...)

Arguments

Parameter	Deskripsi
`avg_rng`	The range to average.
`range1`	The first range to evaluate.
`criteria1`	The criteria to use on range1.
`range2`	[optional] The second range to evaluate.
`criteria2`	[optional] The criteria to use on range2.

Return Value

Rata-rata sel yang memenuhi semua kriteria

Details

Fungsi AVERAGEIFS menghitung rata-rata sel dalam rentang yang memenuhi beberapa kondisi, yang disebut kriteria. Setiap kondisi diberikan rentang dan kriteria tersendiri. Untuk menentukan kriteria, AVERAGEIFS mendukung berbagai operator logika (>,<,<>,=) dan wildcard (*,?,~). Fungsi AVERAGEIFS banyak digunakan di Excel dan dapat digunakan untuk membuat rata-rata sel berdasarkan tanggal, nilai teks, dan angka. Namun, sintaksis yang digunakan untuk menerapkan ketentuan agak rumit karena tidak biasa di Excel. Lihat di bawah untuk detailnya. Sintaks untuk fungsi AVERAGEIFS bergantung pada kriteria yang dievaluasi. Setiap kondisi terpisah memerlukan rentang dan kriteria. Sintaks umum untuk AVERAGEIFS terlihat seperti ini: Argumen pertama, avg_range, adalah rentang sel hingga rata-rata, yang harus berisi

Contoh

Syntax

The syntax for the AVERAGEIFS function depends on the criteria being evaluated. Each separate condition will require a range and criteria. The generic

EXCEL

=AVERAGEIFS(avg_range,range1,criteria1) // 1 condition
=AVERAGEIFS(avg_range,range1,criteria1,range2,criteria2) // 2 conditions

Examples

In the example shown, the formulas in H5:H7 are:

EXCEL

=AVERAGEIFS(C5:C15,C5:C15,">0")
=AVERAGEIFS(C5:C15,C5:C15,">0",C5:C15,"<500000")
=AVERAGEIFS(C5:C15,D5:D15,">=2",E5:E15,">1")

Double quotes ("") in criteria

In general, text values in criteria are enclosed in double quotes (""), and numbers are not. However, when a logical operator is included with a numbe

EXCEL

=AVERAGEIFS(C5:C15,D5:D15,2) // 2 bedrooms
=AVERAGEIFS(C5:C15,D5:D15,">=2") // 2+ bedrooms

Double quotes ("") in criteria

Double quotes are also used for text values. For example, to average values in B1:B10 when values in A1:A10 equal "red", you can use a formula like th

EXCEL

=AVERAGEIFS(B1:B10,A1:A10,"red")

Multiple criteria

Enter criteria in pairs [range, criteria]. For example, to average values in A1:A10, where B1:B10 = "A", and C1:C10 > 5, use:

EXCEL

=AVERAGEIFS(A1:A10,B1:B10,"A",C1:C10,">5")

Value from another cell

A value from another cell can be included in criteria using concatenation. In the example below, AVERAGEIFS will return the average of numbers in A1:A

EXCEL

=AVERAGEIFS(A1:A10,A1:A10,"<"&B1) // average values less than  B1

AVERAGEIFS

Syntax

Arguments

Return Value

Details

Contoh

See Also