+ Reply to Thread
Results 1 to 4 of 4

Automated percentage calculation on Google Sheets

  1. #1
    Registered User
    Join Date
    08-14-2021
    Location
    Australia
    MS-Off Ver
    Google Sheets
    Posts
    5

    Automated percentage calculation on Google Sheets

    I have 150 clients who have bi-weekly changing account balances and who each have their own google sheet to keep track of their updated data. The way their balances change is by percentage and generally it's the same percentage alteration only a different percentage each time. For example, this week all of the clients had a 4% increase in their balances across the board. So what I generally would do is painstakingly work out one by one 4% of each client's balance and add that to their total balance and go into their sheet and manually update this to get their total balance. Also enter other details like the date.. in their sheet to be reflected on a graph. This has only taken a few hours or so twice a week until recently but suddenly the client base has begun to explode and I'm really looking for a way (if there even is one) to automate as much of what I have to do as possible in order to speed things up. At 500 clients obviously the way I'm doing it now will quickly become a whole lot of work. Not being familiar with app script or any formulas or anything like that I was wondering if there might be a way to at least have a formula in place to automatically work out and change a percentage addition (or subtraction) to a cell(s) to save me having to do the maths each time. Or is what I'm asking impossible because the percentages are changing every time? My thinking was if there was a formula or script in place I could go into that script and change the percentage to be worked out appropriately each time and the rest would happen in an automated way.

    As you have probably gathered I'm well out of my depth Thought I'd ask though because you guys have been so helpful before.

  2. #2
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Automated percentage calculation on Google Sheets

    if ALL clients ALWAYS have the same change, then you could create a cell where you put the percentage, and give it a Named Range (for example percentChange), add in a new column to give the new total, so lets say the sheet has a header row and the original amount is in column A, in cell B2 you would then put this formula:
    Please Login or Register  to view this content.
    after the change is done, then you can copy the data, and Paste Special > Values Only to anywhere else you like.

    if on the other hand the percentChange is NOT the same for all clients, the you could create a column in which to individually input the percent next to the original amount, then in a third column would be the new value.

  3. #3
    Registered User
    Join Date
    08-14-2021
    Location
    Australia
    MS-Off Ver
    Google Sheets
    Posts
    5

    Re: Automated percentage calculation on Google Sheets

    Excellent. Thanks so much! I think I'm with you just when I add the named range what am I putting in there as the formula?
    Last edited by djmedzy; 08-18-2021 at 03:18 AM.

  4. #4
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Automated percentage calculation on Google Sheets

    sorry, i missed to respond to your question about formula for named range.... you dont give any formula, you just select the range that you want to give the name to, so that when you use the formula, it will have the named range instead of the cell range.... basically i gave this type of solution because i can not see where the range of values are and a named range can be applied to whichever cells you need them to.

    also, i realised the above given formula is not correct, it should be as follows:
    Please Login or Register  to view this content.
    Last edited by janmorris; 09-15-2021 at 10:12 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Google Sheets: Condition formatting in Google sheet
    By cyee in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 02-18-2021, 05:35 PM
  2. [SOLVED] Google sheets script to download file and save it to google drive folder and use it's data
    By western in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 5
    Last Post: 01-18-2021, 01:31 PM
  3. [SOLVED] Google sheets vba to download file and save it to google drive folder and use it's data
    By western in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-14-2021, 08:48 AM
  4. Google Apps Script for Google Sheets Pulling Formulas from Master to Several Slave Sheets
    By excelroofing in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 08-22-2018, 02:06 AM
  5. Automated recurring calculation. No-one at my job can help me with this.
    By Mr Sandman in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-19-2018, 02:14 PM
  6. email row contents based on cell values (google sheets populated by google forms)
    By reedg in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 01-13-2016, 02:55 PM
  7. [SOLVED] Google Trends; Automated download; Strange result
    By Lloyd Blankfein in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 10-15-2014, 04:49 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1