We use the formula for cell C2 as; = IF (B2> = 5, "Do", "Slide").
SUMIF function structure: = SUMIF (range, criteria, sum_range)
Inside:
For example: You have the sales table as shown below, and need to calculate the total sales of Hanoi branch, You type the formula into a cell in Excel spreadsheet = SUMIF (B3: B12, "Hanoi Branch", G3 : G12).
SUMIFS function structure: = SUMIFS sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], .)
Inside:
With the above sales example, we need to calculate the sales of Hanoi Branch in the first quarter of 2018, we use the formula: = SUMIFS (G3: G12, B3: B12, "Hanoi Branch", C3: C12 , "1/2018").
Function structure: = AND (Logical1; [Logical2]; [Logical3]; .)
Inside:
Function structure: = OR (Logical1; [Logical2]; [Logical3]; .)
Inside:
Examples distinguish AND and OR:
AND (TRUE, FALSE) = FALSE
OR (TRUE, FALSE) = TRUE.
Function structure: = MIN ( number 1, number 2, .)
Inside:
Function structure: = MAX ( number 1, number 2, .)
Inside:
For example: MIN (10,5,16) = 5 and MAX (10,5,16) = 16.
The LEFT, RIGHT, MID functions allow the user to retrieve some characters from the string.
Syntax:
= LEFT (text, [num_chars]) .
= RIGHT (text, [num_chars]) .
= MID (text, start_num, num_chars).
Inside:
For example, you have the string "TipsMake.VN", you take 5 characters on the left, 5 characters on the right, and 5 characters from the 4th character;
= LEFT ("TipsMake.VN", 5) => the result is: ThuTh;
= RIGHT ("TipsMake.VN", 5) => the result is: em.VN;
= MID ("TipsMake.VN", 4,5) => The result is: Thuat .
Syntax: = TEXT (number, format)
Inside:
The TEXT function is used to convert numbers to regular letters, or to format numbers as dates, percentages, currencies, etc.
Here are some common format codes that Software Tips introduces to you:
Description
Some sample code format
"# .000" :
Rounds the number and represents the 3rd fraction (automatically adds the zero to the missing position).
Example: TEXT (2.7, "#. 000") = 2,700
"#. ##"
Rounds a number and shows it to the 2nd fraction and does not show 0.
For example: TEXT (2,7856, "#. ##") = 2.79
"?????. ??"
Add leading and rounding spaces, representing the 2nd fraction.
For example: TEXT (2.7, "?????. ??")) = ˽˽˽˽2.7
"#, ###. 00"
Use thousands and 2 decimal separators
Example: TEXT (1234567.99, "###, ###, ###. 00") = 1,234,567.99
"$ #, ###. 00"
Add currency format with thousand separator and two decimal places.
Example: = TEXT (1234567.99, "$ #, ###. 00") = $ 1,234,567.99
"% .00"
Add currency format with two decimal places.
For example: = TEXT (2.7, "% .00") =% 270.00
"MM / DD / YY"
Format month / day / year.
For example: TEXT (TODAY (), "MM / DD / YY") = March 7, 19
"H: MM AM / PM"
Time format: minute am / pm.
For example: TEXT (NOW (), "H: MM AM / PM") = 0:41 PM.
Above Software Tips guide you some of the functions commonly used in accounting. Hope this article will be helpful for you. Good luck!