Comments on: How to make formulas in Excel

The tutorial explains how to write formulas in Excel, beginning with very simple ones. You will learn how to create a formula in Excel using constants, cell references and defined names. Also, you will see how to make formulas using … Continue reading →

Comments page 2. Total comments: 89

  1. there is date of birth in a cell in figures how can we change it in words in another cell. Pl. let me know the formula.

  2. Hi, I am creating a spreadsheet mostly using the edate function to calculate how far in the future an activity should occur. Initially there is no date but when I drag down the formula that will give me a date, it fills in a date that ends in 1900. I don't want any date to show until the initial date is supplied. How do I write the fromula to include the edate function and be blank. This is the formula for the date to calculate[@[1st Quarter Student Eval Date]] (edate) and this is the formula for a cell to remain blank: =IF(OR(ISBLANK(D5)), "", D5+S5)

  3. Hello, I have a spreadsheet with height and width values of artworks. I want to sell those at different print sizes, so I would like to be able to enter a formula that calculates the width and height of each at 75% and 50%. Is this possible? Thank you.

  4. Dear Sir ,

    please formula give me A+B+C+D=F
    F@30% =B

  5. Hi
    Imagine I have two firms. the market size is 1000 and one firm has a price of $5 and one of $7. How do we split market share? Next one firm spends $30 on advertaing with a price of 45 and the other spends $10 (price $7). How do we combine the two variable price and advertising spend. and what about more variables. Is there a simple equation?

  6. I want to make a shop drawings log , need to calculate total number of drawings, Approval & comments by alphabetic words (exp. A for approval C for to be revised.)

    can some one help me please

  7. How can I add this no in excel
    110abc + 12

  8. Dear
    i am paying bounce on producton to my employees working in my factory.and i want a formula for this plz help me if production is 400,000 then bounce is 100 or if production is 425,000 then bounce is 150 or as below
    Production Bounce
    400,000 100
    425,000 150
    450,000 200
    475,000 250
    500,000 400

  9. Hi,

    * I had been looking for a new formula which"ll feed me with my kinda stuff.
    After several searches and experiments, i didn't stopped at nothing.
    I'm still in a thirst for it.

    * Its hard to explain, but lets see if i'm a good teacher

    * Consider a single cell containing a paragraph and there are "n" number of cells and what i need is to extract numbers or alpha's or both of a specific term which is repeated in each cell.

    * For Example, Consider 2 cells of paragraph containing Phone numbers
    In Cell 1 - ABCDEFGHIJKLMNOPQRSTUVWXYZ Ph no. 123456789 asdfasdfasdf
    In Cell 2 - ABCDEFGHIJKLMNOPQRSTUVWXYZ Ph no. 987654321 asdfasdfasdf
    and the formula i need is will be like ...

    =SEARCH("Ph no. ") and the main thing i'm looking to add with this is... i need all the numerics or alpha's set (till the position in which it is NOT seperated by a space or comma) to be derived to the cell i want

    * And now consider Cell 3 to be a place where i am entering the formula i need from Cell 1 and same with Cell 4 to be a place where i am entering the formula i need from Cell 2

    * With refence to above example Cell 3's visible data what i need is should be
    "Ph no. 123456789"
    * With refence to above example Cell 4's visible data what i need is should be
    "Ph no. 987654321"

    * Irrespective of the position where the " Ph. no " in a cell is placed

    * I know you might think im crazy LoL but it'd really be helpful to me if u help me in this..

  10. Hi I need help
    I would like to split 1.5x 2.5 which is in the same column.
    I want data to be in different column or seperated to be
    1.5
    2.5
    How can I do that

    1. Hi Madilin,
      Thank you for contacting us.

      If we understand your task correctly, please have a look at our Split Cells tool. It can help you separate your text into multiple columns or rows at a glance.

  11. I am trying to find a formula to figure out 6% sales tax.
    Sales ÷tax = expenses

    Thank you

    1. Carley:
      I think what you want is the formula:
      =sales cell address/.06

  12. I am trying to write the following formula in excel

    x=100[((4a+b+c+d)e)-6]/254 where a, b, c, d, e have numerical values in various cells.

    Can some help !

    Many thanks

    1. Mushtak:
      I think this will provide the result you're after:
      =100*(((4*(A1)+B1+C1+D1)*E1)-6)/254
      Note the A1, B1, C1, D1 and E1 are cell addresses that you can modify.
      Note the asterisk Excel uses to signify multiply.
      Note there are no square brackets.

  13. if i want that no.enter in the cell should be divided by 1000 and round upto one decimal place. like 6145 should be converted into 6.1
    what will be the formula .

    1. Virendra:
      The simplest way to get what you want is to apply some arithmetic and then formatting a cell as a number to display one digit. If you want to do this "automatically" you'll need to build some VBA code and that's beyond the scope of this blog. I'll show you the easy method.
      Let's say your number is in cell A2. Maybe it is 6145.
      First, in cell B2 enter =A2/1000.
      Next you'll format the cell B2 as a number with one decimal place.
      To format the cell, right click on B2, choose Format Cells, then choose Number, then select 1 decimal place. Click OK.
      The number displayed is "6.1".
      Some arithmetic, some formatting and you're home.

  14. if i want that no.enter in the cell should be divided by 1000 and round upto one decimal place. like 6145 should be converted into 6.1
    what will be the formula .

  15. Hi, Can anyone tell me how to program this: I want h31 to be multiplied by 3.5% that i have in G32 and want the total of h31 g32 to total in h32.

  16. In developing a formula to solve a mortgage question with time/term being the unknown, how do I use PMT & 'What If' to create it??? Or is there a better way??? ie:
    PV: $100,000
    I: 5.0%
    Pmyt: $536.83
    FV: $00
    Solve for Unknown 'N': ???

    Thank you for your insight & assistance, jt

  17. hellow i need some help!

    1) In regards with the formulas of Excel can i create my own formulas and How?

    2) I need to Insert Particular Page No of a Cell Located in Different Sheets
    for which i am unable to find the formulas in excel
    can i get some help

    will be Thankful for that

  18. Hi I’m trying to find a formula or combined for predicting a future date from a set date depending on a cell value in another work sheet.
    Example:
    01/01/2018 = future date?
    Based on a score of 1 with an argument of next date is 20 days when a score is between 1-5.
    6-10 =15 days

  19. Hello,
    I have a calculation, which needs to be integrated in sheet. I am unable to figure out the formula for it -
    EG:
    Freight (10 X 8)
    (+) 200
    Result of above * 16%
    (+) 50
    Result of above * 18%

  20. Hellow
    Im a beginer with the use of MS Excel.. Could you please help with these questions;
    Write an Ms Excel Formula for each of the following measures:
    a)the statistical range of all values in cells K2,L3,M4 and the cell range AZ4:BB6
    b)the interest given the principle in A2,the annual interest rate in A3 and the number of years in A4,where interest is compounded every two months.
    Your kind response in help will be much appreciated.

  21. Hi
    How can i write equation in excel?

  22. I need to have a date to mail out notices 90 days after a date in another column depending on if another column says needs testing.

    Is there a formula to do this, or do I just do this myself?

  23. hi, i have an issue writing a formula and am not sure if it could be done, but i would love to here your input.

    i have a spreadsheet made up for my work team and need a formula to turn particular arguments into a number.

    i have 5 guys on a team.
    when they come in for a shift i use a date picker to select the day which includes an abbreviation of the day i.e (wed)

    i also have to take into account if its a night shift or day shift so i fill the cell colour red for night and blue for day.

    i also need to add to these arguments a figure regarding to the day of the week (wed) and the colour of the cell (day / night shift).

    unless there is a date picker around i could modify so it would ask what shift was done on the day/date selected.

    so for example. if i selected a cell the right click and choose a date from the date picker, the formula would then calculate a figure answer with the day of week and shift. shifts have different values.

    is it possible? and if so how would i go about this?

    thanks your help will be greatly appreciated.

    regards Andrew.

  24. Hi,
    I'm looking for a formula that I have seen on google (mostly from your blog) but now I forgot and I searched lot on google but I didn't find what I'm looking for.
    In my formula I want to change/increase only row number/reference by '1' not column number/reference.
    ***Not cell value
    like this
    A1
    A2
    A3
    A4

  25. Thanks for sharing such type of informative post with us. Keep it up.

  26. please help me to find formulas that will help me to manage my whole sales and retail agro.chemical shop.

  27. please I need a formula that will help me to manage my whole sale and retail shop . in fact I'm wondering which formula to use.

  28. kindly reply

  29. I want a unique formula to create different formulas
    Example

    SUM .......
    Average
    Count

    I want a unique formula at the place of dots for all three calculations(after dragging in all three cells).
    it means "reference of the formula not value or range"
    Is it possible?

  30. good useful

  31. Hi..
    I have a task to use concatenate function with if function to solve simple arithmetic questions and I have to give ans with the step just like '5*6=30' how can I so this

  32. I am having trouble writing a formula and have become extremely frustrated. I know .17 is 17% but is written as 1.17 in the excel formula. Is this correct? Should I be using .17 (which makes my mark-up off by a lot)or 1.17? If I am to use 1.17 why? I do not understand. PLEASE HELP ME!!

    Formula

    =(gross pay + bonus)* 1.17)+ stipend + cost of insurance

    Trying to get the total w/ a 17% burden

  33. Hi Svetlana,

    Need a formula based on below criteria:
    I have two cells, A and B,
    if the difference btw A & B is less than 6 or equal to 6, "No Change"
    if greater than 6 and less than 9, "Slight Change"
    if greater than 10 "Significant Change"

    Also the formula should override the difference with positive numbers, even the B is larger than A, vice-versa

    Please help!!
    Reply

  34. Sales Person Margin A B C
    A 50 50
    B 100 100
    C 120 120
    A 110 110
    A 50 50
    a 60 60
    B 70 70
    B 20 20

    above table shows some Values i need automatically insert margins I column A, B and C

    1. Use text to columns function in the excel.

  35. I AM TRYING TO FIGURE A FORMULA THAT WILL SUBTRACT TO CELLS. WITH ADDING A UNCONSTANT $ AMOUNT (1.5 x?) AND DIVIDED THE ANWER BY THE $ BY THE SUBTRACTED CELLS.
    A1 pay B1 hours C1 last yr pay D1 Projected

    E1 How many ot hour worked
    ?
    A1$9.50 B1 40 C1 $20,097.25
    D1 19,760.00 E1?

  36. Irina,
    Please help me with a formula
    (1) If a cell value is less than or equals to 15000 then its 12% should be calculated.
    (2) If the cell value is greater than 15000 then no calculation should happen.
    Regards

    1. Hello Viswanath,

      Assuming that your values are in column E, here is the formula you need:
      =IF($E2<=15000,($E2*12%),$E2)

  37. I have a list of names that I want to match on another list. If it matches, I want it to enter a value in a cell. Example: I have a list of 50 names on one list. On the other I have another list of names with a column showing what group they are in (1, 2, 3 etc). I want to put a column next to the first list of names title group. I want the formula to go to the other list, find the name and enter the correct group number. Help?

    1. Hello Trudee,

      You need to use the VLOOKUP function. If we assume that the names are in column A, this is the formula you need:
      =IFERROR(VLOOKUP($A2,Sheet2!A2:B12, 2, FALSE),"")

      It compares the name in column A to the name in column A of Sheet2, and returns the corresponding group from the second column in Sheet2.

      You can find a detailed description of the VLOOKUP function in this blog post:
      https://www.ablebits.com/office-addins-blog/excel-vlookup-tutorial/

  38. Dear Mam,

    I have a issue of multiple line item of excel and want to make merge with the word document and also want to produce as a result which it should dispense to the single page of word document.

    awaiting for your reply.

    Thanks & Regards,
    ashish Kumar mahapatra
    Cell:+91-9437212127

    1. Dear Ashish,
      Integration of Excel with other applications can be done only with the help of VBA code.

      1. Hey ,
        When u type the formula =5! Y can’t we get the factorial y do we have to use the Fact function
        Also how do u use # operator in formula

  39. Hi Svetlana,

    Thank you very much! The VLOOKUP formula do work. :)

  40. Hi Svetlana,
    I would like to make denomination in Microsoft Excel 2010. I don't know how to do formula. Please help

    500 x
    100 x
    50 x
    20 x
    10 x
    5 x

    Thank you

    1. Hello Ahmed,
      It depends what you would like to transform. If you have a set range of values, you can use IF function, if the conditions are flexible, then you will need VBA code. If you can send a test spreadsheet to support@ablebits.com and describe the expected result, we'll do our best to help you.

  41. Hi Svetlana,

    Thank you very much for providing the link. I went trough whole formulas in the provided link, they are really helpful I really appreciate it but the formula I need is a little more complicated.

    Suppose we have 1000 villages and we give a single unique ID to each village, for example: The given unique ID for "Village1" is "0001", Unique ID for "village2" is "0002",... and Unique ID for "village1000" is "1000". Then we type the formula that if in column A1 I put the Unique ID "0001", in column B1 "Village1" should appear automatically and if I put the unique ID "0002" in column A2, in column B2 "Village2" should appear... and finally if I put the unique ID "1000" in Column A3, "Village1000" should appear in column B3.

    I have the list of all those 1000 villages and giving and ID is simple but the only problem is the formula that if we use =If formula for the mentioned purpose, we will need to type each 1000 villages with their Unique IDs, that will take to much time and there is a big possibility of more mistakes for typing such a long formula, so do we have a simpler formula for the mentioned purpose?

    And thank you very much, you really do a great job and please keep it on :).

    1. Hi Suliman,

      The IF function is not quite suitable for this task. What you need is a VLOOKUP formula that can search for a given ID (lookup value) across the ID's list and return a match from another column (village).

      Just be very careful when assigning ids like "0001". Excel cuts off leading zeros in numbers and this may cause problems.

      1. hello mam! do you know a formula for: enter cash total, result (cash pieces of denominations and denomination multiplied to pieces). Looking forward to your positive response. thank you!

  42. I want to schedule tasks/ chores (6) for my kids (5) is there any ease way to do this with a formal. Jake does chore 1 for 5 days and gets 2 days off etc; maddie does a chore for 6 days and get 3 days off and then the awful chore (bathrooms) just rotates through the kids . They key is to be FAIR
    Is there a formal I can use- to remove the kids thinking a love one more than the other

    1. Sorry, I cannot think of any formula that could do this. My first thought was using the RANDBEETWEEN function. Something like assigning a certain number to each chore, and then using a RANDBEETWEEN formula to pick up random numbers for each kid. Upon a second thought, however, this does not look like a good idea. The assigned chores would be random, but not evenly distributed. Really sorry for not being able to help with your task :(

  43. Hi Svetlana,

    Thanks for your contribution on Excel. It is amazing. I used the dynamic data validation process mentioned by you. Keep up the good work.

    1. Thank you Deepanshu! I'm glad my articles have helped in your work.

  44. Hello,

    I am a type of person who is always starving to learn something new of excel tricks and formulas.

    I searched a lot in Google but couldn't find the formula I am seeking. I had seen a finance voucher document that a kind of formula was used in, that suppose Different Cash Books, Vehicle Fuel and at last everything had a unique code. The formula was set in a way that if we put the unique code for Vehicle Fuel in cell no "C2", the text "Vehicle Fuel" will automatically appear in cell no "B2". A short example is added in below:

    C2 | C3
    USD Cash Book | 10010001
    EURO Cash Book | 10010002
    Vehicle Fuel | 10050001

    Is there a way to have an overview or get the lessons of the formula to how to use it? It will be much appreciated.

    Thanks,
    Suliman

    1. Hi Suliman,

      It looks like we have one thing in common. Like you, I also strive to learn something new every day :)

      As for the formula you described, it seems to be an IF function that checks for blank/non-blank cells, for example:

      =IF(C2<>"", "Vehicle Fuel", "")

      The formula reads as follows: if cell C2 is not empty, return the text "Vehicle Fuel", otherwise return an empty string (blank cell).

      For more IF formula examples, please see:
      https://www.ablebits.com/office-addins-blog/if-function-excel/

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