music equalizer
Results 1 to 8 of 8

Thread: Learning Excel quickly - which formulas do I need to know?

  1. #1
    Junior Member
    Join Date
    Dec 2013
    Posts
    9

    Learning Excel quickly - which formulas do I need to know?

    I have to use Excel for a new job, and need to quickly get my Excel skills up to speed.

    I only have a basic knowledge of Excel.

    Can someone tell me the essential functions I need to know to learn Excel fast?

    Thanks a lot.

  2. #2
    The most useful/powerful functions (though not necessarily the simplest) are:
    • vlookup()
    • countif()
    • sumif()
    • dcount()
    • dsum()
    • dcounta()

    If you can learn to use these you will be able to a lot more with Excel.

    See this thread for some in-depth Excel tips

  3. #3
    Member
    Join Date
    Jul 2013
    Location
    Newcastle
    Posts
    40
    It depends what sort of work you’re doing, and whether you’re working primarily with text or numbers.
    However, I recommend learning these Excel formulas:
    Mid(), left(), right()
    Len()
    Char()
    Trim()
    If()
    Vlookup()
    Round()
    Value()
    Round()
    Min()
    Max()

  4. #4
    Junior Member
    Join Date
    Dec 2013
    Posts
    9
    Thanks a lot to everybody.
    Would you mind giving a quick tutorial to explain how to use these formulas?

  5. #5
    Senior Member
    Join Date
    Jul 2013
    Posts
    229
    Here's how to use COUNTIF()

    COUNTIF() is used to count the number of cells that fit a criterion, for example the number of cells above or below a number, or the number of cells containing a particular string of text.

    To use a specific example, if we want to count the number of cells with a value greater than 50, and we have these cells from A1:A4:
    345
    32
    46
    124
    Then our formula would be =COUNTIF(A1:A4, ">50")
    This formula would return the number 2, because there are two numbers (345 and 124) greater than 50.

    Be sure to include quotation marks "" around the logical >< operator and the number. If you don't Excel will say "The formula you typed contains an error".

    COUNTIF() can also be used for text. If you want to count the number of people called 'Alan' in a list of names from A1:A6 like this:
    Alan
    Tom
    Alan
    Fred
    Alan
    Henry
    Then our formula would be =COUNTIF(A1:A6, "Alan")
    This formula would return 3, because there are three cells containing the exact text "Alan".

    I hope this helps.

  6. #6
    Senior Member
    Join Date
    Jul 2013
    Posts
    229
    SUMIF() works like COUNTIF(), but instead of counting the number of instances it sums their value above.

    To use the same example as above, imagine we want to sum all cells with a value above 50, and we have these cells from A1:A4:
    345
    32
    46
    124
    Then our formula would be =SUMIF(A1:A4,">50")

    This would return 469, as this is the sum of the two values above 50, which are 345 and 124.

  7. #7
    Senior Member
    Join Date
    Jul 2013
    Posts
    229
    DCOUNT() Tutorial

    Here’s a quick tutorial for DCOUNT().

    This function is used in databases to retrieve specific information from a database.

    Here’s an example:
    Imagine we have a large database of student names, courses, and the marks each student got in each course. We want to know the number of courses in which a particular student, say John, got over 75%.

    We can use DCOUNT() to do this.

    Our table is set up as follows:



    You can see that we have added the criteria in cells F4:G6 that John’s mark must be over75, as we only want DCOUNT() to count instances where John received over 75%.

    Our formula to compute this looks like this:
    =DCOUNT(A1:C8,3,F4:G5)

    This will return the number 2, as John got over 75% in 2 courses.

    The number 3 is used in the formula as we want Excel to look in the third column, or column C, to find the John’s marks.

    So this formula contains three parts:
    1. A1:C8, which is the table we are looking in.
    2. 3, which is the column we want to check for marks in.
    3. F4:G5, which is where our criteria is.


  8. #8
    Member
    Join Date
    Jul 2013
    Location
    Newcastle
    Posts
    40
    DSUM() works essentially the same way as DCOUNT(), but instead of returning the number of instances it returns their sum.
    DSUM() is to DCOUNT() what SUMIF() is to COUNTIF()

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •