1 / 14

Week 03 : Excel Formula (Fundamentals)

Week 03 : Excel Formula (Fundamentals). PCB - Knowledge Sharing session. 10 Formulas that help you k eep your job. SUM COUNT COUNTA LEN TRIM RIGHT, LEFT, MID VLOOKUP IF SUMIF, COUNTIF, AVERAGEIF CONCATENATE. SUM. Adds 2 or more values together.

brac
Télécharger la présentation

Week 03 : Excel Formula (Fundamentals)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Week 03 : Excel Formula (Fundamentals) PCB - Knowledge Sharing session

  2. 10 Formulas that help you keep your job • SUM • COUNT • COUNTA • LEN • TRIM • RIGHT, LEFT, MID • VLOOKUP • IF • SUMIF, COUNTIF, AVERAGEIF • CONCATENATE

  3. SUM Adds 2 or more values together. Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5) • At the formula bar, press = • Enter SUM • Select a region (using SHIFT or CTRL)

  4. COUNT Counts the number of cells in a range that contains numbers only Formula: =COUNT(A1:A10) • At the formula bar, press = • Enter COUNT • Select a region (using SHIFT or CTRL)

  5. COUNTA Counts the number of cells in a range that contains any values as long it is not empty Formula: =COUNTA(A1:A10) • At the formula bar, press = • Enter COUNTA • Select a region (using SHIFT or CTRL)

  6. LEN Counts the number of characters in a cell (LENGTH) and spaces are taken into account Formula: =LEN(A1) • At the formula bar, press = • Enter LEN • Select a cell

  7. TRIM Removes extra spaces in cell, except for single spaces between words Formula: =TRIM(A1) • At the formula bar, press = • Enter TRIM • Select a cell

  8. RIGHT, LEFT, MID Removes a certain range of value based on different starting location • RIGHT • Formula: =RIGHT(A1,3) • Select 3 characters from the right • LEFT • Formula: =LEFT(A1,3) • Select 3 characters from the left • MID • Formula: =MID(A1,6,3) • Select 3 characters from the 6th character onwards

  9. VLOOKUP Looks for a value in the left most column of the table and returns a value in the same row =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) • lookup_value - Select a cell as the lookup value (Value used to search, usually a unique key/index) • table_array– Range to search from • col_index_num – Column number to lookup from • range_lookup – Approximate or exact?

  10. VLOOKUP We are getting the intersecting values that match EXACTLY with Table 1(tbl_1) and Table 2(tbl_2) • Enter =VLOOKUP( • Select the lookup field (unique,ID,index) • Select search target data (could be a selected table or predefined table) • Enter the column position of the value you want to obtain. (ie: if it is the fourth column of the table, enter the value “4”) • In this context, enter “0” or FALSE to make an EXACT match.

  11. IF Formula: =IF(logical_statement, TRUE outcome, FALSE outcome)

  12. SUMIF, AVERAGEIF, COUNTIF Formulas: =SUMIF(range, criteria, sum_range), =COUNTIF(range, criteria), =AVERAGEIF(range, criteria, average_range)

  13. CONCATENATE Formula: = CONCATENATE(B6 & " " & C6)

  14. That’s it! • Thanks for your kind attention and please stay tuned for the Week 4 session next week. • Good day! • Prepared by : Jermaine

More Related