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 2. Total comments: 198

  1. Whats the formula of

    plus 1% per 14

      1. If I were to make a formula in excel how can I come up with it?

        If I put X I would like to know how many percent it is in total. If 14 is equal to 1%

        1. If I understand you correctly, this formula will help:
          =(B3*C2)/B2

          Where B3 is your X, C2 is 1%, B2 is 14.

          1. It works perfectly! You're good at this and thank you for your help Natalia! More power to your ablebits team

  2. I am using a formula to add 2 percentages =1.45+2.41. However, when it completes the formula it gives me 386.00% instead of 3.86%. Right now I'm dividing by 100 to get the correct number, but I've never had this happen before. What could cause this?

    1. Hello Sarah,

      This happens because 1.45 and 2.41 are numbers. They total in 3.86 that you format as percent. This turns them into 386.00%

      I'd advise you to enter 1.45 and 2.41 to separate cells and format those cells as percent first. Then sum them up. Otherwise, dividing the total by 100 is a correct solution.

  3. Hello,

    I have averages from scores that I need to combine into a range and then create a chart with three different x-axis data points for numbers in the defined ranges. On the y-axis, I need the percentage out of 100%. Example (I have 100 scores with averages from 0-5. Scores that fall between 0-3 = never, 3-3.49 = sometimes, 3.5-5 = often). I need the chart to group all of the scores for those three ranges (x-axis), and I need the percentages on the y-axis to be out of all 100 scores

    1. Hello Green,

      Sorry, your task is not quite clear. You can read about Google Sheets Charts and their possible settings in this tutorial.

      Alternatively, please consider sharing a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. The result sheet is of great importance as it gives us a better understanding than any text description. I kindly ask you to shorten the tables to 10-20 rows. I'll look into it.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

  4. Hello, this is a helpful tutorial, but I'm not finding what I need.
    Is there a way to turn a decimal into a payment factor?

    Example:
    6.5 into 0.065
    10 into 0.10
    9.75 into 0.0975

    Any insight would be appreciated

    1. Hello Erik,

      I'm not sure about your whole task but from what I can see, I can suggest dividing by 100.

  5. Hi - the formula for percentage increase/decrease is working perfectly for the majority of my data, but some of the numbers involved are zeros, which brings up an error as you can't divide by zero. Is there any way to fix this?
    e.g.
    2021 = 0
    2022 = 13
    How can I calculate the percentage increase from 2021 o 2022 when the =(2022 value - 2021 value) / 2021 value brings up a #DIV/0 error?
    Thanks in advance!!

    1. Hi Emily,

      This depends on what you want to see as a result. In both cases it's the IFERROR function that will help you. Please see the formula examples here (the article is for Excel but it works the same in Google Sheets).

  6. hello ma’am. if i have over 10 columns with checkboxes, how do i make the total of these columns equals to 100%? in my example, i have 13 columns and the total is 130% when i check all the boxes.

    1. Hello ariana,

      Use a formula like this:
      =COUNTIF(A1:M10,TRUE)/COUNTA(A1:M10)

      Make sure to format a cell with the formula as Percent.

  7. Hey,
    I was just wondering how you would get google sheets to show something out of a total. for example, I have checkboxes in separate cells and if I check say 3 out of 10 of the boxes, is there a formula to show that in a separate cell? (eg. 3/10) So if I checked another box, it would automatically change instead of having to change it manually.
    Thanks

    1. Hey Darsha,

      Assuming your checkboxes are in column A, use this formula:
      =COUNTIF(A2:A11,TRUE)&"/10"

  8. Dear Sir,
    I got a formula from here, I really appreciate you for these formulas.

    Thank you very much.

  9. Hi;

    Thanks for this post. I am trying to implement this tax bracket as a formula for my family Budget Spreadsheet- I need this >>>( $9,615 plus 22% of the amount over $83,550) .. can someone help me write this formula to add to my yearly income.

    1. Hi Emanuel,

      Assuming $9,615 is in A2 and $83,550 is in B2, here's a formula:
      =A2+IF(B2>83550,B2*0.22,"")

  10. Hello I'm wondering if anyone knows the formula

    I want to be able to type a monetary amount into a single cell and have it decreased by 20% In that cell
    For example
    I want to type $100 but it automatically deduct 20% of that and shows the deduction in the same cell not the $100
    Is that possible?

    1. Hello Lori,

      Due to the nature of Google Sheets, you can't enter a value into a cell and have a working formula in that cell at the same time. It's either one or another I'm afraid. Hence, for your task, you need to have two columns (they can be on different sheets): one with the value to enter, another with a formula to decrease that value by percent.

  11. I am new to SHEETS and need help with the following. I have searched for hours and can only figure out half of what I need.

    If the Value of C13 is 20% OR HIGHER than the value of C20 = Red (I have this part figured out with =C13>=.80*C20)

    If the Value of C13 is 20% or more in the OPPOSITE direction of C20 = Green *Please help with this*

  12. Hi there, I have a Sheet with loads of data (for a game) and need to make a Query.

    I want to input a number (say 1000) and then get a output of all thats in the database thats between 10% lower and 10%higher than the 1000 and output it for me, the 1000 will become a variable input so I can use the formula for different searches in my database (over 40000 entries)
    (I am using sheets, can convert to excel if needed)
    Thank you

    1. Hi Aylana,

      I believe you need to incorporate the percentage formulas to the QUERY function. There's even a tool that will ease the job.

      Yet, if you're still unable to make it work, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. The result sheet is of great importance as it gives us a better understanding than any text description.
      I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into your task and try to help.

  13. Hi, I want to get the formula to calculate the overall increase/decrease in profit% showing it row by row if that makes sense.

    example: row 1 is not profitable so 0% would be shown on that row.

    row 2 is profitable so % would increase to 50% on that row as one out of the two rows is profitable.

    row 3 is profitable so % would increase to 66.6% on that row as two out of the three rows are profitable.

    row 4 is not profitable so % would decrease back to 50% on this row and so on.

    Hopefully this makes sense, Any help is appreciated thanks.

      1. Hi,

        That worked perfectly, Thank you very much for the help!

  14. I'm trying to use Google sheets for pricing on my products I have fortnightly to monthly specials where between 2500 to 5000 products are affected. some of my suppliers release a downloadable file with their special prices for the promo period.

    I add 2 columns E and F as below

    Column D Cost of product column E 20% Column F Sell price

    =D2*(1+E2) works for 1 product answer showing in F2

    but how do you do entire columns at a time? Rows 2 to 2500 instead 1 row at a time.

    1. Hello!
      Write the formula in a cell. Double-click the fill handle. Fill handle is a small square that appears in the bottom-right corner when you select a cell or range. The formula will be copied down as many rows as there is data in adjacent left column.

      1. Thank you so much

  15. Hi,

    I'm trying to do the percentage of a sum and I don't know which function to use for it. Here's my example:
    Column A=10.62
    Column B=5.50
    Column C= 7.99 Percent of (A+B), 7.99% of (16.12) = 1.29 (rounded)

    Does this make sense? I want C to add A and B together then find the percent of it.

    Thanks!

  16. hello! mam,
    i have a some values as a,
    no. of quantity 12 and 150 rate and percent 30% = ans
    means that
    12*150/30%=ans
    how to used formula.

    1. Hello SHIVKUMAR,

      I'm sorry, your question is not quite clear. If you specify what you're trying to calculate, I may be able to suggest a formula.

  17. Hi Natalia,
    14.75 13.25 12.30 11.40 10.75 10.10 9.50 8.95
    15.25 13.70 12.75 11.80 11.10 10.45 9.85 9.25
    15.75 14.20 13.20 12.25 11.55 10.85 10.20 9.60
    - .80 - .30 -.30 -.10 -.10 -.10 -.10

    This is a sample of the sheet I am doing. The first column is the main number each column after follows an =A1-(then amount on the bottom row) however, I need to have that formula less than 5% as well. I can't seem to get the additional 5% subtracted from that formula. How do I do that?

  18. After searching found this post with variety of cases and detailed explaination.. very useful.

    I was searching variation of "Work out percent change from column to column" mentioned above but containing both positive and negative numbers in column. for example say [B ,C] columns cosist of
    [-16,-13], [1,0.1],[-4,-8], [0.5,-1], [2,4],[-0.1, 0.5]. Could you please review and share your suggestion?

    1. Hello Jagdish,

      Thank you for your feedback!

      Try dividing the subtraction to the absolute number using the ABS function:
      =(C2-B2)/ABS(B2)

      Or try a formula like this:
      =(C8-B8)/AVERAGE(B8:C8)

      Whichever one you choose, don't forget to apply the percent format.

  19. How do I calculate 1 cell as a % of another cell? e.g. I should have 100 units but only have 80 units so the unit accuracy is 80%

  20. HI, super helpful, I need to see what 7% or any percentage eventually of a total volume goal. Each rep is assigned a goal, one reps is let's say 2000 for the month. how do a find a formula that will calculate each ones goal? hope that makes sense. thank you in advance

  21. Hello!
    I am making a budget sheet, where I have a total income box and several different expense boxes, with the percent of total income for each. Is there a way to change the percent of one of the expense boxes and have one of the others automatically adjust its percent/dollar value so that total expenses don't exceed total income?
    Thanks!

    1. Hello William,

      I'd advise you to look at the IF function. It looks like it should help you set up the correct percentage to display based on values from other cells.

  22. if I want to calculate a % based on wins vs losses how would I do that?

    I have a column of W's and L's for wins and losses and I want a formula that calculates the win % of all those cells

  23. I'm trying to add 20% to prices but want the people to just type in the amount and it to automatically add the 20% to it, how do I do this?
    Example: the price is $10 but need to add 20% to make the amount show $12. Is there a formula I can put in the column to make this happen without adding another column to do the calculations in?

    1. Hello Teri,

      I'm afraid Google Sheets doesn't work like this. You can't enter the value and the formula for that value in the same cell. It's either one, so you will have to use an extra column with formulas referencing other cells.

  24. I am trying to either create a pie chart or a percentage of the contents of a specific column. I have used conditional formatting and data validation to color the values in each row based on 5 possible answers. Now I need to know the percentage of each possible value.

    For example, In one row I have several names, all different. In the next row, I have their choice: Red, blue, Green. How do I create a pie chart to show how many chose each color, or at least calculate a percentage?

    Thank you!

  25. Hi

    I'm trying to calculate the following, how can I do it.

    I have a product's cost price, and selling price and would like to know what margin has been put on it, so for example if the cost price is $11.65 and the selling price is $18.95 what is the % profit made here.

    Thanks very much
    Abraham

  26. I am fairly new with google sheets, but I was wondering if it would be possible to track sales over $12 and calculate a 12% tax on those sales? Basically we have a space that is rented to vendors, and any item priced $12 and above would be taxed a 12% fee. We have separate codes for each vender, and I am not entirely sure how we would input this into google sheets. If you could possibly help that would be wonderful! Thank you!

  27. Hi! I'm creating a spreadsheet to track our yearly sales in Real Estate.
    Column B is Commission %, Column C is the Gross Sale Price and Column D is the actual dollar amount of the commission made.

    How would I set something like that up to automatically calculate Column D? Thank you so much!

  28. Hi there,
    What would be the formula for having three columns of data (# of tickets sold today, # of tickets sold yesterday, % of total tickets available sold) and THEN a 4th column for percent increase or decrease from the 3rd column for that day?
    So if our venue has a total of 580 seats and yesterday we were at 120 tickets sold (21%) and today we are at 150 tickets sold (25%), how can I show that ticket sales went up 4%?
    I want to keep the column that shows today's percentage, but also shows how much ticket sales increased/decreased from yesterday.
    Make sense? Thank you!

  29. Thank for the great resource!

    I'm creating a simple budgeting spreadsheet for the wife and me. I can easily reference differences from one sheet to another (Example: (B4-('2021-11'!B4)) , where '2021-11' is the sheet for November 2021 and B4 is the cell I want a differential for).

    Question: Is there a way to make this formula "portable" to auto-reference the sheet before it? I would like to not have to change the sheet name every month when updating.

    Thank you in advance!

    1. Thank you for the lovely words, Matt.

      Yes, there's a way. You can incorporate INDIRECT, MONTH and TODAY functions to always subtract 1 from the today's month and reference the sheet for the previous month, like this:
      =B4-INDIRECT("2021-"&(MONTH(TODAY())-1)&"!B4")

  30. do you know how to sort by percentage? like 0.00% to 100.00%?
    thanks

  31. Is there a way I can format a percentage through a formula/function instead of doing it through the formatting toolbar?

    I have this formula and want to make sure the % output is formatted as having two decimal places.
    ex. 1.12% instead of just outputting 1%.

    I have the formatting set to "Automatic" as the same cell is used to output a $ amount depending on the contents of another cell. When the contents of that cell has a certain criterion, a % will show up, which it is doing so correctly, but it's showing up as rounded and i want it to show two decimal places.

    to_percent(A2/A1)

    Thanks
    Jenny

  32. I got this from investopedia.com which says you have to switch numbers in the formula if the price decreases or increases:
    Understanding Percentage Change
    Percentage change can be applied to any quantity that you measure over time. Let's say you are tracking the quoted price of a security. If the price increased, use the formula [(New Price - Old Price)/Old Price] and then multiply that number by 100. If the price decreased, use the formula [(Old Price - New Price)/Old Price] and multiply that number by 100.

    this is totally confusing....

  33. Hello!
    Amazing article that's been super helpful!

    I'm trying to make a tracker on sheets that follow progress of workouts for a group.

    Having to do pull ups, push ups sit ups, etc.. then trying to equal that into a 100%

    Example:
    5 of 15 push ups, 10 of 10 sit ups, and 15 of 15 pull ups = 75%(?) Not great at math...

    Thanks for taking a look at my request for help!

    1. Thank you for your feedback, Jon!

      Since I don't know how you arrange your data, I can only hint on the steps to calculate. You'll need:

      1. a total number of repetitions from all sets (15+10+15 = 40)
      2. a total of repetitions actually done (5+10+15 = 30)

      Then you'll be able to find the percentage as described here.

  34. My Rat Turd sized brain still doesn't understand plz help

  35. If I wanted to create a budget spreadsheet, and have Google Sheets calculate the percentages of my check to put away, which one would I use above? So for example, if I want to put in my income as $2000 and have it calculate 70% savings, 20% bills and 10% entertainment for me. Thanks!

  36. Hi,
    I am trying to highlight the cells in column L that DO NOT fall within a certain percentage : between -30% of J2 and +30% of J2

    basically, if value in L2 is NOT within a 30% range (under or over) the value in J2, Then turn the row in RED

    I tried this =and(L2>=0.9*J2,L2>=1.1*J2) in Conditional formatting, but it doesn't seem to be working

    Any help will be greatly appreciated.

  37. can someone help me with this formula ?

    =ARRAY_CONSTRAIN(ARRAYFORMULA(IF($E3:E970*F$1>0, IF($D3:$D970<=EDATE($C3:C970,12),$E3:E970*F$1,""),"")), 968, 1)

  38. i figured it out, sort of lol

    =to_percent((b105:b135-D105:D135)/D105:D135)

    just had to put it in every cell instead of (g) instead of doing a range

    1. Hello Mike,

      To check your cells for conditions first (if X sold N last week and N this week), you need to incorporate the IF function. The % increase will be used as a second argument and will happen only if the conditions are true.

      Also, you can calculate this for all rows at once by enfolding the formula in ARRAYFORMULA.

  39. i am trying to figure out a way to make a contest for my employess by amount of sales weekly percentage increase (or decrease)

    i.e. if bob (B105) sold $19876 (D105) last week, and sold 21000 (E105) this week, his % increase would be =X

    is there a way to do this for a whole range... or just line by line?

    as if there were 33 employees, bob being first on 105, and debra being last on 138

    i hope this makes sense?!?!

    thanks in advance!

    1. i figured it out, sort of lol

      =to_percent((b105:b135-D105:D135)/D105:D135)

      just had to put it in every cell instead of (g) instead of doing a range

  40. Hello! Thank you for this wonderful resource. I have a chart I need to frequently generate, and am stumped with how to auto populate a certain column. Column A is a date range (fine), Column B is the days within Column A as just numbered 1-->whatever (fine), Column C is data I enter with daily measurements (fine) but the starting number is what controls what I need in Column D and subsequent columns with varying percentage reduction. What I need to figure out is how to take a starting number (call it SN, which begins all the columns I need to auto populate) then take a percentage of that, divide that amount by the number of days in the date range, and subtract sequentially down that column. Example: 3956 SN, take 18% of that (712.08), then divide that amount by 12 days (59.34), each day subtracting another 59.34 while generating a line. The purpose of this is to have a quick, and visual, representation of how daily measurements compare to several ideal percentage reductions, allowing me to adjust the environmental parameters in the project. I am embarrassed to admit I have been doing the calculations and manually entering the results in each cell! This is not good. It's been decades since I've been regularly doing work with spreadsheets and this old brain needs help. Thank you.

    1. Nevermind . . . I figured it out! Thanks again for having this site.

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

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

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

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

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

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

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

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

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

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

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