EXCEL FUNCTIONS_Basic
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.
More Excel Study from Eunice..
https://eunice0121.com/category/excel/
More Excel Information…