+ Reply to Thread
Results 1 to 8 of 8

10 Excel formulas that everyone should know

  1. #1
    Registered User
    Join Date
    07-22-2016
    Location
    New Delhi -110092
    MS-Off Ver
    2010
    Posts
    2

    10 Excel formulas that everyone should know

    Does Microsoft Excel seem too complicated? In this article we present you 10 useful and interesting Excel formulas , easy to remember and ready to apply in your spreadsheets.

    Microsoft Excel is the spreadsheet program par excellence. Praised for its wide variety of functions, it can be a bit challenging for all those who use it for the first time.

    This labyrinthine program can perform almost any task and offers numerous options. To make matters worse, in Office this application has become even more powerful.

    The simple sum (SUM)

    It allows adding various numerical data, such as at school. There are two ways to do it.

    The first allows you to add contiguous cells (in a row or in a column):

    Type = SUM (first_cell: last_cell) in the cell where you want the result to appear.

    Variant: This formula also allows you to add all the numbers contained in a specific rectangular area (formed by several rows and columns), if you consider as the first cell the one located in the upper left corner and as the last one that is in the lower vertex law.

    In the following examples, "first_cell: last_cell" will always be designated with the term "range".

    The second method will allow you to add individual (non-contiguous) cells:

    Type = SUM (cell1; cell2; cell3; cell4) in the cell where you want the result to appear.

    Example:

    To add the content of the column on the left, write in a cell of your choice = SUM (A1: A5) .

    To add the content of the entire table, write in a cell = SUM (A1: A5) .

    To add only cells that have a negative number, write in a cell

    = SUM (A1: B3: B5) .

    Add according to a certain criterion (SUMAR.SI)

    Note that this feature is only available as of Excel 2007 .

    A table can be presented in the form of a list with different data, even if you do not want to work with all of them at the same time.

    If this list is long, it can be tedious to select the identical items one by one to add them up. In this case, it is possible to order Excel to find them and then add them with the following formula:

    = SUM.SI (range; "criterion"; range_sum) .

    The "criterion" is the condition to look for in the table. The range is the range of cells that contain the criteria to evaluate. Sum_range is the range of cells where the numbers you want to add are found.

    Example:

    In this example, the criterion we are looking for is "red".

    To just calculate the number of "reds", write the following formula in a cell: = SUMIF (B1: B5; "red"; A1: A5) .

    Add according to several criteria (SUMAR.SI.CONJUNTO)

    A table can provide a wide variety of information. Excel allows you to add numerical data that meet different criteria, thanks to this special formula:

    = SUMAR.SI.CONJUNTO (suma_suma; range_criterios1; criteria1; range_criterios2; criteria2; ....) .

    Sum_range is the range of cells where the numbers you want to add are found.

    Criteria_range1 represents the range of cells that contain the data referred to the first criterion, criteria_range2 refers to the second criterion, etc.

    Criterion1 is the first condition to look for in the table, etc.

    To know the total points obtained by the members of the yellow team, write the following formula in a cell of your choice:

    = SUM.CONJUNTO (D1: D13; C1: C13; C1; B1: B13; B1) .

    Variant : It is not mandatory to select the first and second cells of a column or row, you can also select the entire column . Instead of B1: B13, it indicates only B: B.

    Count the cells that meet a criterion (COUNTIF)

    When it comes to statistics, you may want to know how many times information is repeated throughout a column. In this case, we use the following formula:

    = COUNTIF (range; "criterion") .

    The range represents the range of cells that contain the numerical data to analyze. Criterion is the condition to look for in the table.

    Example :

    In the previous table, we wanted to know how many women participated in the game. Then, we write the following formula in a cell: = COUNTIF (B1: B13; "Woman") .

    Add the cells that meet various conditions (SUMPRODUCT)


    This formula gives you an exact statistic to answer this question: How many times a series of specific conditions are fulfilled in your table . This formula will give you the answer:

    = SUMAPRODUCT ((range_criteria1 = "criterion1") * (range_criteria2 = "criterion2")) .

    Criteria_range1 represents the range of cells that contain the data referring to the first criterion, criteria_range2 refers to the second criterion, etc. Criterion1 is the first condition to look for in the table, etc.

    Example :

    If we use this function in the preceding table, we will be able to determine, for example, how many men are part of the yellow team. We only have to write in a cell the following formula:

    = SUMPRODUCT ((B1: B13 = "Man") * (C1: C13 = "yellow")) .


    Add the cells that verify two specific conditions (SUMAPRODUCT)
    As we have already seen, SUMAPRODUCT is mainly a statistical tool capable of determining how many times certain conditions are met. It will allow you to know how many numbers are included between two specific values, thanks to the following formula: = SUMAPRODUCT ((range> = minimum) * (range <= maximum)) .

    Range represents the range of cells that contain the numeric data to analyze. Minimum and maximum are the values ​​between which the numbers we are looking for should be.

    Example :

    Suppose that in the preceding table we want to know how many players scored between 150 and 200 points. Then, we must write in a cell the following formula:

    = SUMPRODUCT ((D1: D13> = 150) * (D1: D13 <= 200)) .


    The average (AVERAGE)

    It would be a waste of time to repeat the definition of average. Mainly, because it is very simple to calculate it with the following formula: = AVERAGE (rank)

    The range represents the range of cells that contain the numerical data to analyze.

    Example:

    Moyenne Excel

    To calculate the average of all these numbers, write the following formula in a cell: = AVERAGE (A1: A5) . As in previous cases, it also works with a range of numbers in several columns or separated by commas.

    Maximum and minimum (MAX and MIN)

    Finding the largest or smallest value among a large amount of data can be a real nuisance. Happily, Excel provides a formula that does the work for you. It is very easy to use and is written as follows:

    = MAX (range)

    = MIN (range)

    The range represents the range of cells that contain the numerical data to analyze.

    Variant: You can apply the search to several cell ranges. In that case, the formulas would be:

    = MAX (rank1; rank2)

    = MIN (rank1, rank2)

    Example :

    If you want to know the highest value of the preceding table, you must write this formula in a cell: = MAX (A1: A6) .

    If, on the other hand, you want to know the smallest numerical value, write: = MIN (A1: A6) .

    Of course, these formulas are especially useful in large tables.

    Bonus Track: How to calculate percentages in Excel

    Before finishing we will make a brief review of the world of percentages in Excel. This section does not intend to delve deeply into the subject, but we invite you to leave any doubt about it in the comments. If we see enough interest we will consider making a specific tutorial.

    If you want to obtain the percentage of a certain amount it is as easy as multiplying it by the percentage that we want to obtain.

    Example:

    Imagine that we want to find out what is 75% of 300, which is the value of cell A2. Simply enter the desired percentage in cell B2 while in cell C2 we introduce a formula that multiplies both values. The resulting value should leave 225. Here we leave you three multiplication formulas that you can use.

    = 300 * 75%

    = 300 * 75/100

    = 300 * 0.75

    To end

    Although at first glance they may seem totally inscrutable, these Excel formulas will save you an incredible amount of time if you regularly use this program.
    Regards,
    Nishant Agrawal,
    Talent Magnifier

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    6,588

    Re: 10 Excel formulas that everyone should know

    I think you've put this in the wrong place. Ask a mod to move it to the tips and tutorials board

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    16,069

    Re: 10 Excel formulas that everyone should know

    I'm not aware of a SUMAR.SI function or SUM.SI or SUMAR.SI.CONJUNTO in the English version of Excel 2007 - are you referring to a non-English version? If so, it is strange that the long narrative is in English.

    Pete

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    27,249

    Re: 10 Excel formulas that everyone should know

    I suspect that posts from this user are intended as subtle advertisement of his company. I expect that when he's posted enough threads, we'll find links are added. Or maybe I’m just being cynical.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    1,820

    Re: 10 Excel formulas that everyone should know

    Searching the content of his sig on Google -- "Nishant Agrawal,Talent Magnifier" -- indicates that "Talent Magnifier" is a company in New Dehli and that Nishant is their Digital Marketing Analyst. (Also the founder if the twitter profile is to believed.)

    In addition to Trevor's suspicion that it's advertising, this might also be intended to boost connections to their website as part of some SEO.

    Well, if they're legit then more power to them, I guess.

    But they're also kind of tripping some flags that maybe they're one of those scams trying to fleece unemployed people for "training", so be warned, other Internet Randos reading this.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    Cool Re: 10 Excel formulas that everyone should know

    @ben,
    Enough to link eg. your profile image to their site in background and it will do the same
    Every refresh of page with your profile image will do one hit more to them
    edit: it all depends on the popularity / activity of a given profile

    this is example only
    Last edited by sandy666; 03-08-2018 at 03:43 PM.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  7. #7
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    1,820

    Re: 10 Excel formulas that everyone should know

    Think I should kill the links in my post then?

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    Re: 10 Excel formulas that everyone should know

    I didn't say that
    this is a link / links to something on the net from there to here - not from here to there.
    I said about eg. your profile pic (or anyone else). Usually enough link pic with 1 pixel size

    if your links in the post are not against forum rules......

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1