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 11. Total comments: 413

  1. Hi Svetlana,
    I am trying to figure out how to get excel to recognize if a cell populates or not.

    I want cell G39 to read cell G40. G40 has a formula that sometimes populates from a data worksheet. When it populates I want G39 to display cell G3. If it doesn't I want it to stay blank.

    What I have tried so far and both have not worked:
    =IF(NOT(ISBLANK(G40)),G3,"") This doesn't work because it reads the formula so the cell isn't really blank.
    =IF(G40"",G3,"") This seems to have the same problem.

    Not Sure what else to try...

  2. thanx for this information

  3. Thanks, very useful piece of information

  4. how to display sheet 2 value in sheet 1 by formula

    1. =SUMIF(DF!B:B,SFD!C5,DF!F:F)
      =SUMIF(range,criteria,sum_range)

  5. Good morning. Is there a way to add letters into a cell after a simple sum calculation? ie I sum a list of hectare totals and now I want to add "ha" after. Thanks

    =SUM(L8:L161)

    1. Hi Tom,

      You can use the CONCATENATE function or concatenate operator (&) like this:
      =SUM(L8:L161)&"ha"

      If you want a space between the number and text, use this formula:
      =SUM(L8:L161)&" ha"

      Please note, concatenation turns the output into a text string, and you won't be able to use the result in further calculations.

  6. Dear Sir/ Mam,

    --
    I like information, you providing,, Further request you to send me detail formulas specially for the condition "IF".I think it will help in future.
    --
    Kedar

      1. can you help me in above formula i am applying this formula but didnot show good results

  7. I want to know if I type in the Cell/B2(Jan-18) and now i want to type the Cell/B2 in the Cell/D32 what the formulas or solution if any...

    1. Virender:
      Enter =B2 in cell D32.

      1. Ok that is for one cell And if I need the same value to be added in Cell/ D33, D44, D55 and so on.

  8. I want To formula sent me some formulas please i am waiting your mail sir.

  9. Thank you sir

  10. Hi
    MAY know formula about values in row
    58 150 300 500 400 350 200
    In this how could know fall down 50% of peak value (500) plz explain

  11. Pl sir tell me about the formula
    if employye is skilled and working days is 26or27 days then salary = 9270, if employye is un-skilled and working days is 26or27 then salary = 7661

    1. Somnath:
      I think this will work:
      =IF(AND(B60>=26,C60="S"),B60*9270,B60*7661)
      Where A60 holds the employee name, B60 holds the days worked and C60 holds the skill level.
      Just enter this in C60 and copy it down.
      Of course you can change the addresses to suit your needs.

  12. if employye is skilled and working days is 26or27 then salary = 9270, if employye is un-skilled and working days is 26or27 then salary = 7661

  13. How to calculate the data from various data sheets to a single cell? Pls help

  14. Hi, please what fourmula can use for minusing which means coulmn A1 is as inbound quantity column B 1 is current quantity, column C1 is out going quantity so when I mark a number in the column C1, column B1 should be minused which means current quantity need to minus pls help

  15. KINDLY EXPLAIN THIS FORMULA PLEASE. IT IS THE FORMULA WRITTEN ON COLUMN NO ZU2 IN THE PREVIOUS COMMENT

    =SUM(($C$27:$C$19996="A/C")*($F$27:$F$19996="E/L"))

  16. i want interconnect 2 or 03 sheets in a workbook and when i make changes in one sheet i need corresponding changes automatically in other sheets . i have a worksheet named "parade" and 3 sheets in it named "out persnl list", N-Roll", paradestmnt", when i make changes in out persnl sheet corresponding changes should be occurred in paradestmnt in a particular coulmn and i found a formula written in paradestmnt like ='Out persl.str list'!ZU2 what type of formula is this ,

  17. THANK YOU TO THE WHOLE TEAM IT WAS VERY USEFUL FOR ME AS A BEGINNER,
    THE EXAMPLES AND WAY OF EXPLAINING IS GOOD.

  18. Hi,

    Instead of CONCATENATE you can use & also..

    eg: =F2&H2

    Instead of =TODAY() you can use CTRL+; and SHIFT+CTRL+; for Current Time

  19. please provide new excel formula

  20. Hi,
    Really helpful.
    Thanks

  21. 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.

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

    Thank U Svetlana

  23. 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

  24. 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.

  25. Thank u very much

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

  27. 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.

  28. 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"),"")

  29. 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.

  30. 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.

  31. 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.

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

  33. 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.

  34. thanks for support

  35. Thakyou sir

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

  37. 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.

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

  39. 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.

  40. 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.

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

  42. 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

  43. 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.

  44. 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.

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

  46. 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

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

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

  49. how can i put the amount on another sheet

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

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 :)