Comments on: Basic Excel formulas & functions with examples

Being primarily designed as a spreadsheet program, Microsoft Excel is extremely powerful and versatile when it comes to calculating numbers or solving math and engineering problems. This tutorial aims to teach you the essentials of Excel functions and show how to use basic formulas in Excel. Continue reading →

Comments page 12. Total comments: 413

  1. Pls what formula can I use to rearrange a database to the highest amount at the top and the lowest at the bottom

    1. Chukwuebuka:
      Select all the cells then go to Sort, Custom Sort and choose the column you want to sort by.

  2. Very Good introduction to basics of formula who are already using MS Excel.

    Thank U Svetlana

  3. Hello Friends,

    I have a few employee ( 1900000+ records)which include start date and end date and cost center,

    Empl.NumberEnd date Start Date Cost center
    12345 05-02-2016 16-01-2016 46049
    12345 23-02-2016 06-02-2016 46051
    89123 30-11-2007 04-06-2007 12202
    89123 01-02-2008 01-12-2007 12202
    89123 18-07-2008 02-02-2008 12214
    89123 29-08-2008 19-07-2008 12123
    89123 14-11-2008 30-08-2008 12213

    I want a record from latest date , that means latest available record for that employee.

    12345 23-02-2016 06-02-2016 46051
    89123 14-11-2008 30-08-2008 12213

    is there any formula for this

  4. HI,

    I have 1000 rows with repeated names so i just want to remove the duplicate once with IF formula can i?

    1. Sheshu:
      Which version of Excel are you using? Newer PC versions have a duplicate remover tool. Older versions need some formulas.

  5. Thank u very much

  6. Sir Excel me 100+5%=105 lane par kaun sa kaise formula lagega

  7. we have no of judgment in every row eg. in first row 7,second 4, third 4, in fourth row 6 and every judgment have 10 pages and the each photocopies price Rs.1.35/- what is the cost of photocopies and add other column in which show total cost of pages

    1. Ratna:
      I would create four cells to handle this.
      First cell would be "# Pages", second cell would be "RS Cost", third cell would be "Total Pages" and fourth cell would be "Total Cost RS". The "# Pages" is in B2, the "RS Cost" is in C2.
      Where the judgment values begin in cells begin in column A, row 2 the formula for "# Pages" in cell B2 is =A2*10, the formula for "RS Cost" which is in cell C2 is =B2*1.35. When this is complete you've got the number of pages and the cost for each judgement.
      Now you can sum the total number of pages and the total cost into their respective cells. You can either enter =SUM() in the cells at the bottom of the "# Pages" and "RS" columns or enter =SUM() in another location on the sheet where you can more easily see it.
      To sum these values you will enter the cell range that contains the numbers you want to sum.
      So, for the "Total Pages" you enter =SUM(B2:B10) where B10 is the last cell that contains a page count you need to total. You are telling Excel that the values you want to sum are in the cells B2 through B10. The cell range is shown like this B2:B10 using the ":" to tell Excel this is a range of cells with which you want it to work .
      You do the same thing for the "Total Cost RS". The formula would be =SUM(C2:C10) where C10 is the last cell that contains a cost value.

  8. i can't copy the formula by click and drag
    =IF(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1EoMw8AOge5wBiR6dwXjVO8CBhyWVCDmsYjAetoc_0yw/edit#gid=20494945","Sheet1!$i3")="fail",
    IMPORTRANGE("https://docs.google.com/spreadsheets/d/1EoMw8AOge5wBiR6dwXjVO8CBhyWVCDmsYjAetoc_0/edit#gid=2049494524","Sheet1!$A3:$k3"),"")

  9. FOR MAINTAINING ACCOUNTS IF I WANT TO SUBTRACT B1 FROM A1 IN THE COLOUM C FOR THAT IS THERE ANY PERMANENT FORMULA.

    1. Darshan:
      The formula in column C should be:
      =A1-B1.
      Then Copy the formula down the column by highlighting C1, then grab the solid little box on the lower right of that cell and when it turns into a cross click and drag down. After you go down as far as you want, each cell in column C will contain that formula.

  10. Hi, I'm creating a transcript template and need to know how to enter the GPA into the cell next to the letter grade.
    For instance if I enter any letter grade there is a corresponding GPA for it. I want the cell next to the letter grade to automatically enter the corresponding GPA value. A = 4.0, A- = 3.70, etc...

    Hope that makes sense.
    Thanks!

    1. Shannon:
      There are at least three techniques for getting the results you're after. Nested IF statements, VLOOKUP and INDEX-MATCH. IMO, in your case there are too many options to go the IF Statements route. But, because your data set is not that large I'll show you the VLOOKUP option. However, you could use INDEX-MATCH and if your data set got any larger I would recommend it.
      So, here's the VLOOKUP road.
      Start by creating a table that contains all your options. It looks like this:
      A 4
      A- 3.7
      B 3
      B- 2.7
      C 2
      C- 1.7
      D 1
      D- 0.7
      F 0
      This can be on another sheet in the same workbook or somewhere on the same sheet. Perhaps at the top off to the side. Anyway, wherever you put it you'll enter a reference to it in the formula. My example has the set on the same sheet. It looks like this:
      =VLOOKUP(A30,$E$31:$F$39,2)
      If the data table was on another sheet in the same workbook it might look like this:
      =VLOOKUP(A30,Sheet2!$A$47:$B$55,2
      So, this data set is on Sheet2 of the same workbook.
      The VLOOKUP syntax is: inside the () the first value is the cell that contains the data you want to lookup in the second value's range address and in that second range address you want to look at the 2nd column.
      So my test data in the first example is on the same sheet where the lookup table is entered, so just A30 suffices.
      Then because your score table is on the same sheet the range is $E$31:$F$39. The dollar signs in front of the addresses tells Excel that this range is fixed and you want Excel to only look at these cells for the matches. This is mandatory when you want to copy down the formula to show other matches as you fill in scores which is what you will do.
      The last number is a 2 and this tells Excel that you want to look in the second column of your range to return the value that matches the letter grade.
      OK, so what this formula is telling Excel is that the data I want it to find is in A30. Where I want it to look to find it is in on this same sheet in the range $E$31:$F$39 and the value I want returned is in the second column of that fixed range.
      So, in conclusion, you build your data set either on the same sheet or on another sheet in the same workbook.
      Then you build the lookup formula that references this data set's address. You might want to enter this formula in the cell directly adjacent to the cell that holds your letter grades.
      Then copy the formula down the column so that each of the cells will return the respective value for that letter grade. Copy the formula down by highlighting the first cell, then grab the solid little box on the lower right of that cell and when it turns into a cross click and drag down.
      In your case I would also format as Text the cells that hold the letter grades in the data set and in the cells you enter the letter grades. Only the cells that hold the letter grades.
      That's all there is to it. One way to get a solution to your question.

  11. I HAVE SIX winning NUMBERS FROM 1 THRU 44 IN SIX SEPARATE CELLS ON ONE LINE. I WOULD LIKE TO HAVE EACH OF THE VALUES IN THE SIX CELLS COMPARED TO THE VALUES 1 THRU 44 USED AS COLUMN HEADERS FOR that SAME LINE. IN EACH CASE WHERE THE VALUE IS EQUAL TO THE COLUMN HEADER, THE NUMBER ONE WOULD BE PLACED ON THE SAME LINE AS THE SIX NUMBERS UNDER THAT HEADER. I CAN DO THE =IF(B35=K32, "1") BUT AM LOST TO HAVE IT REPEAT FOR ALL SIX CELL numbers. I USE IT FOR LOTTO PREDICTION AND ENTERING EACH SIX NUMBERS BY DATE IS TIME CONSUMING when ENTERING NUMBERS FOR MULTIPLE DATES. SET UP EXAMPLE:

    SIX CELLS COLUMNS CONTAINING NUMBERS 1-44
    winning #s 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

    8 12 13 27 41 44 1 1 1

    A FORMULA THAT WOULD BE IN A STANDALONE CELL THAT WOULD REVIEW EACH OF THE SIX CELLS AGAINST THE 44 HEADER CELLSIS WHAT I THINK IS THE ANSWER HAVING WRITTEN THIS OUT. IT WOULD BE AN ACTIVE CELL FOR EACH LINE. THANKYOU FOR CONSIDERING THIS.

  12. i like the way that u explained and it work's.

  13. Hi there,
    Can you please suggest a formula for me?
    I have around 20 players playing bowls weekly for 20 weeks.
    They must play at least 8 weeks to qualify.
    At the end of the competition the winner will be the player with the highest total of the best 8 scores.
    Therefore the final column needs to total each player’s best 8 scores and ignore all others.
    Is it possible, please?
    Thanks for your help,
    Roger Cook.

    1. Roger:
      There are a couple of ways you can achieve your goal. I'll show you one.
      Assume you have entered 10 weeks of scores for 20 players in cells A2 through J21.
      If so, then enter this formula in K2:
      =SUM(LARGE(A2:J2,{1,2,3,4,5,6,7,8}))
      Highlight K2 and right click on the little box in the lower right corner of that cell using the black cross and copy it down to K21. If you enter more than 10 weeks of scores you need to change the J2 to another cell.
      Note this formula uses a hard-coded array constant inside the curly brackets. You can enter the curly bracket using the keyboard. This array constant tells Excel to sum the largest eight values in the row.
      If you use cell references and not an array constant with LARGE, you must enter this formula as an array formula by entering the cells and then Ctrl+Shift+Enter, or CSE.

  14. thanks for support

  15. Thakyou sir

  16. Hi frnds,
    i need to create a formula for this. can any one help me
    +*+=strong
    -*-=strength
    +*-=weakening
    -*+=weak

  17. Ma'am,
    How to highlight particular cell. When we drag down, the highlighted content must be on top. No need drag up and check column specification.
    For Example:

    Date Inv. No. Supplier Amount Description

    In case of excess amount of sale, no need drag up & check continuously about column specification.

  18. thank u,it was very useful to me
    please send me all excel formulas to my mail id ,plsssssssssssssssss

  19. I have a row of numbers and I need the total of the three lowest numbers some numbers may be repeated. example:5,6,7,5,4,3,2,10,8,2,3,5,4,6,6,5,4,8
    my three lowest numbers would be 2,2,3 for a total of 7 can you give me a formula.

    1. Marge:
      Here's a method to accomplish what you want. Not sure if it will fit your circumstance, but the method I used to sum the smallest two numbers in your list is:
      =SUM(SMALL(A18:A35,{1,2,3,4}))
      This will sum 2+2+3+3 the two smallest numbers in your list for a total of 10. You can sum different smallest numbers by entering more numbers after 4. For example this {1,2,3,4,5} will sum the five smallest numbers in your list for a total of 14 because it now includes one of the "4"'s. Like this 2+2+3+3+4 for a total of 14.
      Where A18:A35 are the cells that hold the data and {1,2,3,4} represents the array I want to total.
      This is an array, so you need to designate it as an array. Arrays need to be entered with Control + Shift + Enter not just typing curly brackets.
      If your list did not have multiple occurrences of the same number, you would build the array differently.
      Also, I entered the numbers from your list into separate rows then sorted this list highest to lowest so I could more easily work with the data.

  20. Here's one way to do it:
    =LEFT(A50,2)&MID(A50,4,3)&RIGHT(A50,5)
    Where A50 is the cell that holds the data.

  21. what is the formula to remove the dashes from this number:
    42-235-36180

  22. Part No L5A L5B L5C L5D Total Loc
    111086 9 9 L5A
    141713 1 1 L5A
    141755 24 36 30 30 120 L5A,L5B,L5C,L5D
    146000 2 2 L5D
    521219 12 6 18 L5C,L5D

  23. HI

    I had an requirement related to Excel formulas. If any one can help me out .Just have an try....

    Part No L5A L5B L5C L5D Total Loc
    111086 9 9 L5A
    141713 1 1 L5A
    141755 24 36 30 30 120 L5A,L5B,L5C,L5D
    146000 2 2 L5D
    521219 12 6 18 L5C,L5D

    Here Loc column should be come from the excel formula. Here the concept is that column part no was residing in the different locations like L5A,L5B,L5C .....I need the Loc column value should be automatically come with the excel formula that each part will reside in different locations. Those locations has to be come with one formula for every part number. Please help me out in this.

  24. formula for remaining days betwwen today and various Expiry dates of various products i.e in coloumn A3 I enter to today date and in other rows we mentioned expiry date of varoius products.

    1. How to calculate days until expiration.
      In cell A1 enter expiration date.
      In cell B1 enter "=A1-TODAY()". This will display a number.
      If the data has already passed, the number will be negative.
      Remember to format cells as dates if you need to show a date.
      You may also want to checkout EOMONTH function.

      To create expiration date.
      Enter date in cell C1.
      In cell D1 enter "=C1+90". This will create a date 90 days in the future.

  25. formula for remaining days betwwen today and various Expiry dates of various products

  26. if cell name (A1) in figure are 456, i want in A2 is 456/3

    how its possible

    note A1 cell (456) is total of sum cell, its change many time

  27. Thanks Sharing this Formulas, it's helpful for me and others...

  28. Very useful fa beginners tqsm but will updating more formulas fa all kinds of users

  29. how can i put the amount on another sheet

  30. Date deposit withdraw balance 01/10 12000 Nil. Nil. 03/01 Nil. 5000. _ what is the formula

  31. thankyou sir......it is really a useful formula....

  32. Pls tell me text formulas for multiple cell

  33. Please am just a beginer in Excel
    can i meet you on a prevate tutor?
    On ur convinient time pls?

  34. Good knowledge, please send excel formulas.

  35. can anyone give me formula for automatic grace marks for student who get the mark between 30 to 35.if passing mark is 35.
    condition is grace marks can be given only in three subject.
    please reply as soon as possible.
    thanks in advance

  36. can anyone give me formula for automatic grace marks for student who get the mark between 30 to 35.if passing mark is 35.
    condition is grace marks can be given only in three subject.

  37. can anyone give me a formula for getting an approved "A" result of the submittal.
    Example:

    A1 = C (Revised and resubmit)
    C1 = B (approved as noted)
    D1 = A (approved)

    I need an answer which ever revision is the latest.

    Thank you in Advance
    Jean

  38. its very informative and Thanx for this ..
    any body have any other formula regarding excel kindly mail me or whatsapp

  39. It's was to help ful to me

  40. how to fetch one table to another table data

  41. Say
    buyer X received 123 pcs from unit 1
    buyer X received 500 pcs from unit 2
    buyer X received 280 pcs from unit 3
    buyer y received 123 pcs from unit 3
    buyer y received 123 pcs from unit 1
    buyer Z received 123 pcs from unit 2
    buyer Z received 123 pcs from unit 1
    Please put formula who I calculate buyer wise and unit wise received total qty

  42. Give me the formula name for
    C3*C4%+C3

  43. Very good
    Can u teach us how to make general ledger tria balance

  44. SR NO FORUMAULA

  45. IF I HAVE TWO COLOMS A & B WHERE "A" HAS MATERIAL AND "B" HAS QTY. AND DON'T WANT TO FILTER TO COUNT PARTICULAR QTY FOR MATERIAL ,SO I WILL DO WORK IN A NEW COLOM "D" AS MATERIAL AND "E" QTY AND WHAT I NEED HELP THAT AUTOMATICALLY QTY ADD UP IN COLOM "D" & "E" WHEN I WILL DO ENTRY IN "A" & "B"..PLEASE REVERT ME IF YOU NEED SCREEN SHOT OF MY WORK...

  46. nice explanation

  47. if Total cost is 10000. i want to calculate what is the value which includes 12% in 10000. Tell me the formula pl.

    1. ex: TOTAL COST A1
      PRICE :=A1*100/112

  48. REALLY VERY HELPFUL THANKU

  49. =num2text FIGURES TO WORDS

  50. Hello,
    I have been asked to extract the number from the following list without using MID as this only works when all the results are the same type.
    TX TO 2014016 CHG ORG
    TX TO 1832298CHG ORG DEN
    TX TO 1854600 CHG ORG
    TX TO 1971447
    TX TO 1980547 CHG ORG
    Any help is appreciated

    1. Hello,

      If you need to avoid using the MID function to fulfill your task, then I can recommend you to try extracting numbers from your list of data with the help of the Extract Text tool.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)