EXCEL FUNCTIONS_Basic

https://www.w3schools.com/excel/ 

AND

returns “TRUE” or “FALSE” based on two or more conditions.

=AND(B2=”Fire”;C2>70)

=IF(AND(B10=”Fire”;C10>70);”Yes”;”No”)

AVERAGE

calculates the average 

=AVERAGE(B2:E2)

AVERAGEIF

calculates the average of a range based on a true or false condition.

=AVERAGEIF(range, criteria, [average_range])

AVERAGEIFS

the average of a range based on one or more true or false condition.

=AVERAGEIFS(average_range, criteria_range1, criteria1, …)

CONCAT

to link something together.

=CONCAT(A2,” “,A3)

=CONCAT(A2,”and”,A3)

COUNT

only counts cells with numbers, not cells with letters.

=COUNT(D2:D21)

COUNTA

counts all cells in a range that has values, both numbers and letters.

=COUNTA(A2:A21)

COUNTBLANK

counts blank cells in a range.

COUNTIF

counts cells as specified.

COUNTIFS

counts cells in a range based on one or more true or false condition.

IF

returns values based on a true or false condition

IFS

returns values based on one or more true or false conditions.

XOR

returns TRUE or FALSE based on two or more conditions.

RIGHT

use the RIGHT function with a defined length of characters

=RIGHT(A2,3)

STDEV.P  표준편차

calculates the Standard Deviation (Std) for the entire population.

=STDEV.P(E2:E755)

STDEV.S

calculates the Standard Deviation (Std) for a sample.

=STDEV.P(E2:E21)   range of the sample

SUMIF

the sum of values in a range based on a true or false condition.

SUMIFS

calculates the sum of a range based on one or more true or false condition.

TRIM

remove irregular text spacing and keep single spaces between words.

=TRIM(A2:C21)

VLOOKUP

allows searches across columns.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

=VLOOKUP(H3;A2:E21;2;1)

for selecting col_index_number 2.
next – 1 (True) is entered as range_lookup. This is because the most left column has numbers only. If it was text, 0 (False) would have been used.