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

  1. Good Afternoon,

    Please show me the formula I need to use to add columns B11 thru H11 in addition to adding 10% all in one cell.
    Thank you,

    Noreen

    1. Hello, Noreen,
      If we understand your task correctly, please use the following formula to solve it:

      =SUM(B11:H11)+SUM(B11:H11) *10%

      Hope this is what you need.

  2. I have in a cell a range date for example
    1-12 - 1-19 and I would like to change to a Jan-12 - Jan 19
    What is the formula for it?
    Thanks

    1. Hello, Olga,
      Thank you for your interesting question.

      Please try the following formula:

      =CONCATENATE(INDEX({"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"}, MID(A1, 1, FIND("-", A1, 1)-1)), MID(RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1), 1, FIND(" - ", RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1))+2),INDEX({"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"},LEFT(MID(RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1),FIND(" - ", RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1))+3, 10),FIND("-",MID(RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1),FIND(" - ", RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1))+3,10))-1)),MID(MID(RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1),FIND(" - ", RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1))+3, 10),FIND("-", MID(RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1),FIND(" - ", RIGHT(A1, LEN(A1)-FIND("-", A1, 1)+1))+3, 10)), 10))

      Please note that this is an array formula. You should enter this formula into a cell in any column and hit Ctrl + Shift + Enter to complete it. Copy the formula down along the column if necessary by selecting the cell where you've entered the formula and drag the fill handle (a small square at the lower right-hand corner of the selected cell) down.

      Hope it will help you.

  3. Good

  4. hi

    could you please have you tel me where i get more information about condition formatting

  5. Update to clarify my request:

    The "300" in the input cell could be increased manually to any number 500, 1200 ..... or 5000
    A1 = "input cell", the next 12 cells B1 to M1: each cell should be filled with a 100 as long A1 increased by 100
    if A1= 1043; B1=C1=D1=E1=F1=G1=H1=I1=J1=K1=100; but L1=M1=0 & N1=43
    N1= A1-(sum(B1:M1)) if A1 >1200 or N1= 0 if A1 <= 1200

  6. Hi, I'm tring to find a formela to divide the input value to the next cells as 100s.
    example: if A2= 200, then B2=100, C2=100, D2=0 and E2=0
    and if A2 changed to 300 then B2=100, C2=100, D2=100 and E2=0.

    Best regard.

  7. Display the highest and lowest marks in each test also give one appropriate leading

  8. I have a problem in my excel coding

    (this coding
    Function Rup(amt As Variant) As Variant
    Dim FIGURE As Variant
    Dim LENFIG As Integer
    Dim i As Integer
    Dim WORDs(19) As String
    Dim tens(9) As String
    WORDs(1) = "ONE"
    WORDs(2) = "TWO"
    WORDs(3) = "TRE"
    WORDs(4) = "FUR"
    WORDs(5) = "FIV"
    WORDs(6) = "SIX"
    WORDs(7) = "SVN"
    WORDs(8) = "EIT"
    WORDs(9) = "NIN"
    WORDs(0) = "ZER"
    WORDs(11) = "Eleven"
    WORDs(12) = "Twelve"
    WORDs(13) = "Thirteen"
    WORDs(14) = "Fourteen"
    WORDs(15) = "Fifteen"
    WORDs(16) = "Sixteen"
    WORDs(17) = "Seventeen"
    WORDs(18) = "Eighteen"
    WORDs(19) = "Nineteen"
    tens(2) = "Twenty"
    tens(3) = "Thirty"
    tens(4) = "Fourty"
    tens(5) = "Fifty"
    tens(6) = "Sixty"
    tens(7) = "Seventy"
    tens(8) = "Eighty"
    tens(9) = "Ninety"
    FIGURE = amt
    FIGURE = Format(FIGURE, "FIXED")
    FIGLEN = Len(FIGURE)
    If FIGLEN < 12 Then
    FIGURE = Space(12 - FIGLEN) & FIGURE
    End If
    For i = 1 To 3
    If Val(Left(FIGURE, 2)) 0 Then
    Rup = Rup & WORDs(Val(Left(FIGURE, 2)))
    ElseIf Val(Left(FIGURE, 2)) > 19 Then
    Rup = Rup & tens(Val(Left(FIGURE, 1)))
    Rup = Rup & WORDs(Val(Right(Left(FIGURE, 2), 1)))
    End If
    If i = 1 And Val(Left(FIGURE, 2)) > 0 Then
    Rup = Rup & " Crore "
    ElseIf i = 2 And Val(Left(FIGURE, 2)) > 0 Then
    Rup = Rup & " Lakh "
    ElseIf i = 3 And Val(Left(FIGURE, 2)) > 0 Then
    Rup = Rup & " Thousand "
    End If
    FIGURE = Mid(FIGURE, 3)
    Next i
    If Val(Left(FIGURE, 1)) > 0 Then
    Rup = Rup & WORDs(Val(Left(FIGURE, 1))) + " Hundred "
    End If
    FIGURE = Mid(FIGURE, 2)
    If Val(Left(FIGURE, 2)) 0 Then
    Rup = Rup & WORDs(Val(Left(FIGURE, 2)))
    ElseIf Val(Left(FIGURE, 2)) > 19 Then
    Rup = Rup & tens(Val(Left(FIGURE, 1)))
    Rup = Rup & WORDs(Val(Right(Left(FIGURE, 2), 1)))
    End If
    FIGURE = Mid(FIGURE, 4)
    If Val(FIGURE) > 0 Then
    Rup = Rup & " Paise "
    If Val(Left(FIGURE, 2)) 0 Then
    Rup = Rup & WORDs(Val(Left(FIGURE, 2)))
    ElseIf Val(Left(FIGURE, 2)) > 19 Then
    Rup = Rup & tens(Val(Left(FIGURE, 1)))
    Rup = Rup & WORDs(Val(Right(Left(FIGURE, 2), 1)))
    End If
    End If
    FIGURE = amt
    FIGURE = Format(FIGURE, "FIXED")
    End Function
    in this coding i want 0 gentrate as ZER its not coming its coming like 0 as 0 how to i get it

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

  10. How you use percentage formula in Excel.

  11. Hello,

    Can you please send me a link for a tutorial on creating a sales lead template with if and then being a 2 week window for contacting those leads?

  12. GOOD

  13. Hi,

    I have filled the colour to the one of the cell in excel.
    if I doubleclick the coloured cell. it should show the value which I assighed it.
    my question: there is any formula to add value, example the value should be in background. by doubleclick the colourd cell, it shows the value.

    thank you in advance

  14. Thanks , so helpful.

  15. Hii
    Excel sheet formula
    Please information

  16. How can some one understand all this?

  17. SIR WITH EXAMPLES SHOW IN EXCEL HOW TO CALCULATE COMPOUND INTEREST.

    FOR EXAMPLE PRINCIPAL AMOUNT RS.10,00,000/-
    DELAY DAYS 200 FOR WHICH INTEREST 15% PER ANNUM
    COMPOUNDED QUARTERLY
    INTEREST RATE 15% PER ANNUM COMPOUNDED QUARTERLY

    SIR KINDLY DO THE ABOVE CALCULATION IN EXCEL AND GUIDE ME

  18. I want know how hloockup work in excel sheet and what is the use of this formulas .

  19. Dear Svetlana,

    i have gone through yours tutorial it is amazing thanking you too make our life easy. i have one more request from you, i do asset validation checking all the asset is correctly captured such that they can be processed in software to make life easy to our organization.
    Please suggest me number of formulas and Technic i can used to do my validation more accurate.
    currently i am using vlookup, pivot table, mid, clean, trim, iferrror, and many more.

  20. i need one formula if we entering the employer code that page have to show name and his detail's

  21. Nice sir ji

  22. plz gv mi da formula of RANKing

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

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

  25. Help me understand the formulas of MS-Excel

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

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

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

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

  30. Thanks Very Useful

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

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

    thanks
    RAMNIVAS VERMA

    9315509039

  33. Good five

  34. 5290 x 69 + 10%

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

  35. easy to understand this function. lot of thanks

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

  37. FANTASTICS

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

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

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

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

  42. thanx for this information

  43. Thanks, very useful piece of information

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

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

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

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

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

  49. Thank you sir

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

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