Conditional Functions
Kuliah
10. Conditional Function
Please
have your note on hand!
What is a formula in Excel definition?
In Microsoft Excel, a formula is an expression that operates on values in a range of cells. These formulas return a result, even when it is an error. Excel formulas enable you to perform calculations such as addition, subtraction, multiplication, and division.
Excel has over 475 formulas in its Functions Library, from simple mathematics to very complex statistical, logical, and engineering tasks such as IF statements (one of our perennial favorite stories); AND, OR, NOT functions; COUNT, AVERAGE, and MIN/MAX.Mar 11, 2020
Column
x Row è Cell. A
group of cells è range
Formula è Arithmatics, Logics,
Strings (Text), Statistics, Database etc typed in a cell or range
Syntax begins with = (eq sign). Example è = D2 + E2
Consists of operators, functions, numeric, text etc
Cell Reference è (Relative and Absolute)
By default, a cell reference is a relative reference, which means that the reference is relative to the location of the cell. If, for example, you refer to cell A2 from cell C2, you are actually referring to a cell that is two columns to the left (C minus A)—in the same row (2).
For example, if you type the formula = A1+A2
into A3 and copy it to another location: B3, the formula will change to =
B1+B2.
But, if you type the formula as an absolute
reference such as = $A$1+$A$2, and then copy the formula to any other
location, it remains fixed as = $A$1+$A$2.
In
contrast, the definition of absolute cell reference is one that does
not change when it's moved, copied or filled. This way, the reference
points back to the same cell, no matter where it appears in the workbook. It's
indicated by a dollar sign in the column or row coordinate.
An
absolute reference in Excel is a reference that cannot be changed when copied,
so you won't see changes in rows or columns when you copy them. Absolute
references are used when you want to fix a cell location. These
cell references are preceded by a dollar sign.10Nov2022
simplilearn.com
Conditional
Functions
Conditional functions perform calculations on a cell or range of cells only if those cells meet a certain condition. These functions test a given range and determine if the condition is true or false before continuing.
Untuk pelengkap silahkan baca dari link2 di bawah ini untuk evaluasi di akhir kuliah (quiz):
https://e115.engr.ncsu.edu/spreadsheets/conditional-
functionshttps://uhlibraries.pressbooks.pub/mis3300excel/chapter/8-3-conditional-functions/
https://www.educba.com/best-basic-excel-formulas/?source=leftnav
Contoh sebuah soal conditional functions yang merupakan target terakhir yang harus mampu diselesaikan oleh mahasiswa:
| SUM | Adds values you enter in the formula. |
| SUMIF | Adds values that meet a single criterion. |
| SUMIFS | Adds all values that meet multiple criteria. |
| AVERAGE | Calculates the arithmetic mean of a range of values. |
| AVERAGEIF | Returns the average of all cells that meet a single criterion. |
| AVERAGEIFS | Returns the average of all cells that meet multiple criteria. |
| COUNT | Counts the number of cells that contain numbers. |
| COUNTIF | Counts cells using a single criterion. |
| COUNTIFS | Counts cells using multiple criteria. |
=SUMIF(RANGE, CRITERIA)
SUMIF essentially asks: what do you want to add up, based on what criteria. The SUMIF syntax starts with our function, then within the parentheses, we must tell Excel what is the range of values (text or numbers, blanks will be ignored) we want it to add up based on which criteria. Our criteria may be a single value like the number 42, or “>42″, the cell reference for where our criterion is located. If you use text or =, >, >=, etc. operators, then make sure to encase them between ” “, otherwise Excel will return an error.
Referred to https://basiccomputerapps.blogspot.com/2022/11/conditional-functions-2.html
For instance, we want to know what is the summary (amount) of undergraduates who have NILAI above 80.
=SUM (E4:E12,">80") ==> 341
=AVERAGEIF(RANGE, CRITERIA, [AVERAGE_RANGE])
The AVERAGEIF syntax will behave similarly to SUMIF. Within the parentheses, we must tell Excel what is the range of values that have our criteria, then in the optional argument in the brackets, we tell which range we want it to average if needed. For instance, we want to know what the average NILAI of students enrolled in Tourism PW programs in PNB.
=AVERAGEIF(C4:C12,"PW",F4:F12) ==> 85.33
The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met (support.office.com).
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
Refered to https://basiccomputerapps.blogspot.com/2022/11/conditional-functions-2.html
For instance, we want to know how many FEMALE undergraduates have NILAI above 80. Now, we want to use our conditional SUM function to return this value.
Write in any appropriate cell =COUNTIFS(D4:D12,"P",F4:F12,">80") ==> 2 (Elsa and Yuki)
Comments
Post a Comment