I have two columns in Google Sheets, where A = score and B = number of absences. After each absence, A will decrease cumulatively by 8%. For example, if A=100 and B=2, then the answer should be 79.28 (100-.08(100)=92, 92-.16(92)=79.28). Is there any way to automate this easily? Thanks!
Google-sheets – Cumulative Percentage Subtraction Google Sheets
google sheets
Related Topic
- Google Sheets – Automatically Transpose Imported TXT File Rows to Columns
- Google-sheets – How to get sum of largest numbers in each individual row in Google Sheets
- Google-sheets – Google Sheets – Nested If/Not Blank to Fill a Value
- Google Sheets – Calculate Remaining Duration Based on Percentage
- Google-sheets – Google Sheets – Selective Totals
- Google-sheets – Google sheets convenience question: consolidating a function
- Google Sheets – Conditional Formatting Guide
- How to Score Weighted Assessments in Google Sheets
Best Answer
First, at the time of my writing this, the question states that "if A=100 and B=2, then the answer should be 79.28" and then "92-.16(92)=79.28" But since 92-0.16(92) is actually 77.28, I'm hoping this was just a typo and my answer continues on the assumption there isn't some other tricky math going on.
Second, this seems to me a very odd way of calculating penalties. I could imagine it being compounding, where each absence deducts another eight percent. I could imagine it being increasing, where the penalty is eight percent for every absence. But the question's described procedure does both. This has some peculiar side effects, like the penalty peaking at four absences, since after that so much score has been removed that the initially-dominant and ever-increasing "percent to remove" cannot keep pace with the ever-shrinking "remaining score" from which it's removed.
The math to do this automatically is so unwieldy that I fear it would be unmaintainable. Conversely, the math to calculate in advance the cumulative, compounded, final multiplier for every given possible number of absences until every score is zero is very straightforward in a spreadsheet. Therefore, for clarity, I'd strongly recommend just having a "manual" rather than "calculating" style of formula.
This could be done, say, on a separate sheet in your Sheets file. See this demonstration Sheets file.
Each of the "Remaining Score" values is what percent of their score was left with the previous (one fewer) number of absences, minus the input penalty of eight percent times the current number of absences times what percent of their score was left with one fewer absence.
Since the resulting values are percents, they will work as multipliers for the input scores back in your original score and absence sheet. If you had the above sheet named PenaltiesCalculator, with absences in A and penalties in B as pictured, a formula to refer to it and generate a new column of final scores next to your input scores and absences could be:
This just looks up the pre-calculated total penalty for each row's absence count and multiplies the score times the matching remaining percent after the penalty. As it's an array formula taking all of A and B as input, entering it into C2 would populate the entire column.
A second alternative—since that earlier table of calculated penalties are just percents, and only depend on the absence count—as long as the "eight percent penalty multiplier" will never change the penalties will never change, so we don't need a calculator. We could also just drop the extra calculator sheet and embed the penalties directly into a formula in C2 like: