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

  1. Nice sir ji

  2. plz gv mi da formula of RANKing

  3. Sub Button1_Click()
    Dim Str As String
    StrFolder = "C:\Users\heywh\Videos\Assorrted Credits\"
    ActiveWorkbook.FollowHyperlink Address:=StrFolder, NewWindow:=True
    End Sub

    How would i use an If Statement in the above Macro

    I want to show a msgbox so that if the c:\folder above nothing is selected with in the sub folder to this msgbox "No Month Was Selected" show in a MsgBox and if a Month is selected it goes right to the month selected.

    Thank You For your Time.

  4. I have a spreadsheet with imported values from a bank statement. It’s saved as a spreadsheet not csv data. The first column is the date, the second is the transaction the third is the amount etc till the sixth which is my own description that I added. Is there a way to pick the whole line out and place it in another row with all the same data? Example, the last column could be house, apartment, nm house, condo or trailer park.

  5. Help me understand the formulas of MS-Excel

  6. sir i convert to numeric to text formulla,
    means 549556 i change auto five lac fourty nine thousand five hundred fifty six only

  7. If 1kg of tomato costs 200 rupees, what will be the cost of 700 grams
    how to calculate in excel what formula use for this problem

    1. MD:
      I think what you're after is:
      (200*.7)/1000

      1. 1 kg tamato =200
        1kg=1000gm
        1 gm= .2 (200/1000)=.2
        so,
        cost of 700gm
        =700gm*.2rs=140

  8. Can you use formulas to calculate hours worked? I do lot of timesheet work and be good to know. For example 7.50am start to 6.20pm finish ?
    Thanks

  9. Dear Sir
    These Formulas are really helpfull .Please give detail info of If Formula.

  10. Thanks Very Useful

  11. V=0.30/3[A1+A2]ROOT[A1XA2]

  12. please send me simple formula for excel in my mail id .

    thanks
    RAMNIVAS VERMA

    9315509039

  13. Good five

  14. 5290 x 69 + 10%

    1. Laxmikant:
      =(5290*69)+((5290*69)*0.01)

  15. easy to understand this function. lot of thanks

  16. hi, may I know that if a column have 2 words, but I want to separate it to 2 columns, what is the fomula?
    E.g:
    in column:
    123456 abc

    then I want to separate 123456 and abc to 2 columns.
    Thanks for the help in advance.

    1. Al:
      If you don't have too many of these things to separate, then just use the Text-to-Columns tool.
      Under Data select the Text-to-Columns and with your data use the Delimited option and select the space checkbox as the delimiter. That will separate the data into two columns.
      Otherwise where the data is in A1 you might want to use RIGHT(A1,3) to get the abc characters into a cell.
      There are several techniques to split text strings. AbleBits has a couple of good articles that explain some of these techniques.

  17. FANTASTICS

  18. Hi Svetlana,
    The (G40"",G3,"") would work if I didn't have a formula in G40. I think the formula that's puling data from a different tab was being recognized.

    I was able to find a work around. Instead of using cell G40 I went to the Tab I was getting the information and went to the cell that G40's formula was populating from. This is what I came up with that worked:
    =IF(Data!B34"",G3,"")

    Thanks,

    Jared

  19. Your board isn't posting the greater than and less then signs that would come after the G40 and before the "" in the 2nd formula. That formula still doesn't work with those included.

    1. Hi Jared,

      Sorry for messing around with your formula, it's because of a silly bug in our blog engine that we are unable to fix.

      =IF(G40<>"", G3, "") seems to work fine for me except when G40 returns an error. That is:

      if G40 has a value, G39 displays G3.
      if G40 is an empty string, G39 is blank (empty string).
      if G40 has an error, G39 displays an error.

      What is wrong about this behavior? Please clarify.

      1. Hi,
        How to use pipe formula for series of invoice NOs.

        D0123
        D0124
        D0125
        D0126
        D0127

  20. Typo in the second formula which should read:
    =IF(G40"",G3,"")
    Still doesn't work

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

  22. thanx for this information

  23. Thanks, very useful piece of information

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

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

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

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

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

  29. Thank you sir

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

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

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

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

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

  35. 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"))

  36. 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 ,

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

  38. Hi,

    Instead of CONCATENATE you can use & also..

    eg: =F2&H2

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

  39. please provide new excel formula

  40. Hi,
    Really helpful.
    Thanks

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

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

    Thank U Svetlana

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

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

  45. Thank u very much

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

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

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

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

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

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