Comments on: Google Sheets percentage formulas - usage and examples

You may think percentage calculations are useful only if you use them for work. But in reality, they help you in everyday life. Do you know how to tip properly? Is this discount a real deal? How much will you pay with this interest rate? Come find the answers to these and other similar questions in this article. Continue reading

Comments page 4. Total comments: 198

  1. I am trying to convert my grades to percents using this form. For example, the sheets generate the score as a number out of the total like: 4/20. How do I get it to create the percentage for each score?

    1. Hello Beckie,

      Assuming your total is generated in A2, a formula like this should help:
      =INDEX(SPLIT(A2,"/"),1,1)/INDEX(SPLIT(A2,"/"),1,2)

      Don't forget to apply the Percentage format to your cell with the formula.

  2. Your calculation to find the original price for a 40% discounted scooter is incorrect. Your calculation is 1500/.4 and it should be 1500/.6.

  3. =SOM(B1-A1)/B1 x100
    not working

    4,1 - 3,5 = 0,6 : 3,5 = 0,171x 100 = 17%

    Or :

    3,5 -4,1 = -0,6 : 4,1= -0,146x100=-14,6%

    1. Hi Ruud,

      Your formula is written incorrectly:

      1. The function should be SUM, not SOM.
      2. There's no need to SUM if you simply subtract one number from the other.
      3. The multiplication sign in formulas is *, not x.

      Hence, here's how a correct formula should look:
      =(B1-A1)/B1*100

  4. I don't understand

    1. Hello Amir,

      You're welcome to describe your task here, I'll see if there's an easy solution and suggest where to look.

  5. Hello,

    I am looking for A1 to show a percent value of B1 after it surpasses 5000. I also only want the percent value to be of all values added after 5000.

    A1=25%B1>5000

    As in:

    If B1=6000 then A1=250

    Is this possible? I'm a bit new to this level of function use and am having trouble sorting through what I am able to do. So far I have had to create a separate key of cells that show the percentage I want to apply to B1 per other cell I want to show said value. For reference this is meant for a royalties accounting spread sheet that has several individuals receiving a different percent of profit per music release so there are a lot of variables and it makes it really messy to try and have a separate key for percentages and alternate cell values. Not sure if this all makes sense? Thank you for this awesome resource!!

  6. A very good explanation of formulas. This will help to a great extent those who are working on excel. Truly fantastic article. Thanks for sharing this. Kudos!!!

  7. hey there!
    I need help regarding a problem I'm having with sheets. I have a table that has Months (from Jan to Dec) in the X axis and the name of the asset in the Y axis. I want to add the new value of the asset under each month and I want sheets to indicate (in a column at the end of the table) the growth rate it has had from the very first month. I would like if I didn't have to keep changing manually the formula of said column. Is there someway it can do so automatically?

    I would really appreciate your replies,

    Cheers!

    1. Hey Deidara,

      If I understand you correctly, you can use ArrayFormula in the last column to calculate all rows, existing and future ones, automatically. If this is not exactly what you need, please describe the task in more detail.

      You can also share an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: a copy of your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.

  8. مرحبا
    H3=مبلغ
    I3=5% نسبة
    =H3*I3
    يعطني خطاء
    لماذا

  9. Hi there,
    Thank you so much for creating useful content and helping me thereby solving my google spreadsheet question. Thanks again!

  10. Hello,
    I have the same problem as the person below did on 11/19/19. I will enclose my formulas.
    These formulas gave me 98% COUNTIF(E5:E107,"No")/COUNTA(E5:E107,"no") =COUNTIF(E5:E107,"Yes")/COUNTA(E5:E107,"Yes")

    This formula gave me a 99% =COUNTIF(H5:H107,"No")/COUNTA(H5:H107,"No")
    =COUNTIF(H5:H107,"Yes")/COUNTA(H5:H107,"Yes")

    Hi,
    I'm trying to keep track of attendance using a google sheet with checkboxes. Here is the formula for what I have: =COUNTIF(C6:X6,"TRUE")/COUNTA(C6:X6,"FALSE")
    My issue is, if all of the boxes are checked, the percent should be 100% but it's only 95.65%.

    1. Hello Martha,

      COUNTA counts all non-empty cells within the range no matter their contents. The values you're using as the second argument ("FALSE" or "no") don't tell what to count. The second argument is also a range to count, so these records are actually counted as "1" mixing the numbers up.
      So please change all your COUNTA to COUNTIF and everything should work correctly then.

  11. Thanks for providing a great resource. I have a table of columns and rows with sales figures that go up and down. Using formulas, the table displays monthly changes in sales as percentages. I have them color-coded so sales increases display in green and decreases display in red. The decreases show a minus character (-) automatically. Is there a way to automatically display an addition character (+) for a percentage increase? Thanks!

    1. Thank you for your feedback, Trevor.

      You will need to select your cells with the percentage increase and create a custom number format like this: "+"0%

      1. Thank you. Is there any way to make the "+" automatically appear only in cases where a percentage exceeds 0.00%?

  12. Hi -
    I have a number of identical laid-out sheets and have the same metric in each = open rate as a percent.
    In my summary sheet, I want to calculate the average percent across the sheets.

  13. Hello!

    I'm looking to calculate a shopping spreadsheet and would like to calculate discounts & taxes. What formula can I use to calculate total + 15% tax for example?

    1. Hello Elyse,

      Assuming your total is in column A and tax is in column B, you can try this formula:
      =A2+(A2*B2)

      The multiplication in brackets is used to find the amount of the tax, then it is added to the total in column A giving you a sum you will need to pay.

  14. Such a great article. I'm trying to increase a number in cell by a percentage and it doesn't seem to be working. i used the fomula you stated but it adds the percentage as 100% and not 10%. Any ideas as to why that is?

    I appreciate it,

    Adkins

    1. Hello Adkins,

      Can you please provide the exact formula you use in your sheet that returns the incorrect result?

  15. need to calculate percentage markup

    row of cells: cost retail %markup

    need to multiply cost x100 then divide by retail? just can't figure out order

  16. Hi! thank you for this tutorial!

    You may be able to help me, I am doing a spreadsheet where I track my business profits on a daily basis.
    I already get my daily % growth, but I would like to do a TOTAL percentage, from my 1st day to today.
    The trick is that I want it to fill itself, and in only 1 cell. I want the result of this growth to always appear in the 'k5' cell, for example, and for it to always take the last input and the very 1st one without me having to change the formula every day.

    If you can help, I'll take it!
    Thank you very much.

    1. Hi Kuro,

      thank you for the feedback!
      I'm afraid I'll need more details on your data to help you out. Can you please describe where it's located in the spreadsheet in detail (Sheet? Column? The number of rows?) and give an example of how it looks?
      What formula do you use and what do you update in it constantly?

  17. My family and I have a weekly running nfl pool. Each week column input and results of course change. I use check boxes to say who wins and who loses and the sumif function at the end of the column to give me the needed results. I decided to test the workings of the sheet and everything works fine except for one thing. When I attempt to divide cells d22(total wins) with e22(total losses) To get the percentage needed I have used the formula =(D22+E22)/D22 which does not work. What am I doing wrong?

    1. Joseph,

      Could you please specify what are you trying to get? The percent of losses or wins from the complete total? Or something else?
      If it's possible, please also share your sample data with us (support@4-bits.com) and reply to my comment when we can take a look. Thank you.

  18. I disabled my ad blocker, so much good content thanks for sharing.

  19. Hi,
    I'm trying to keep track of attendance using a google sheet with checkboxes. Here is the formula for what I have: =COUNTIF(C6:X6,"TRUE")/COUNTA(C6:X6,"FALSE")
    My issue is, if all of the boxes are checked, the percent should be 100% but it's only 95.65%.

    Any ideas?

    1. Hi Lisa,

      Would you mind sharing your sample data with us (support@4-bits.com) so we could have a look?
      Note. That email address' Inbox is unmonitored. Once you share the file, please confirm by replying here. Thank you.

  20. Hi,
    How to do calculate =a2*b2 for multiple cells ex:a2-a5*b2-b5 etc...

    1. Hi Matt,

      If I understand you correctly, this formula should do the trick:
      =ARRAYFORMULA(A2:A5*C2:C5)

  21. Why does google sheets add 2 zeros to my values when I try to apply the "percent" format?? I just want the % to appear next to the number already in the cell.

    1. Hello Diana,

      That's simply the way Google Sheets works.

      In your turn, you can adjust the number of zeros using the corresponding options from the Sheets toolbar. Look for two buttons with zeros right under the Format option: Decrease decimal places and Increase decimal places. The tip from this point of the article shows them.

  22. or do a vidio please

    1. Hello Tyler,

      Could you please specify what points of the article remain unclear?
      At the moment I don't believe the video would feature something other than the same formulas.
      If you could describe the task you're dealing with, I would gain a better understanding of what to improve.

      Thank you.

      1. Hey so I run a business and I pay my drivers 30% of the income. Lets say a driver makes on a week $4560 I need to pay 30% of that to him. Is there a formula that can help me find the value of that 30%? Thank you.

  23. Can you do a dumbeddown version of this please because my penut sized brain dosent understand

  24. Great stuff!

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