# Cumulative Percentage Subtraction Google Sheets

In Google Sheets I have two columns of numbers. Column A is labeled `score` and column B is labeled `absences`

I am trying to create a formula that will return an adjusted `score` for each row based on the value in `absences` and a common penalty `percentage` such as 8%.

My challenge is that I use a cumulative approach when applying the `percentage` so each additional absence implies an exponentially greater cumulative penalty.

## Sample Calculations

Example adjusted `score` calculation based on the number of `absences`. For reasons of space the table ends at 4 but in practice there is no cap and the pattern continues to grow for higher numbers.

`s=score`    `p=percentage`:

absences calculation
0 s
1 s * (1-p)
2 s * (1-p) * (1-2p)
3 s * (1-p) * (1-2p) * (1-3p)
4 s * (1-p) * (1-2p) * (1-3p) * (1-4p)
...and so on

Example calculations based on the table above:

``````# s=100  p=8%
# if absences=1

=s * (1-p)
=100 * (1-8%)
=92

# if absences=2

=s * (1-p) * (1-2p)
=100 * (1-8%) * (1-(2 * 8%))
=100 * 92% * 84%
=77.28
``````

In other words one absence incurs a penalty of `8%` but for two absences it jumps to `22.62%` as opposed to `16%`.

Is there a way to automate this calculation?

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:

``````=ARRAYFORMULA(IF(NOT(LEN(A2:A)),"",A2:A*VLOOKUP(B2:B,PenaltiesCalculator!A:B,2,FALSE)))
``````

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:

``````=ARRAYFORMULA(IF(NOT(LEN(A2:A)),"",A2:A*IFS(
B2:B=0,100.00%,
B2:B=1, 92.00%,
B2:B=2, 77.28%,
B2:B=3, 58.73%,
B2:B=4, 39.94%,
B2:B=5, 23.96%,
B2:B=6, 12.46%,
B2:B=7,  5.48%,
B2:B=8,  1.97%,
B2:B=9,  0.55%,
B2:B=10, 0.11%,
B2:B=11, 0.01%,
B2:B=12, 0.00%
)))
``````
• I'm engaging in a bit of "nerd sniping" here, eagerly awaiting the master formula that does compounding-per-period plus increasing-percent-per-period in one. Had to answer in the meantime though. Commented May 3, 2019 at 16:49
• I think my answer might be on the right path but I'll let you decide. It isn't really fair because REDUCE and LAMBDA didn't exist when you answered. It also might need some tweaks as used my phone to write it. Commented Sep 10, 2023 at 22:31

This is an excellent candidate for the REDUCE function.

## Core Formula

``````=REDUCE(score, SEQUENCE(absences),
LAMBDA(s, a, s*(1-a*pct)))
``````

## Exception Handling (Shortform)

``````=IF(score>0, IFERROR(
[core_formula_here],
score), IFNA(#N/A))
``````
1. IFNA returns nothing if `score=0` (or blank)
2. IFERROR returns `score` if `score>0` and `absences=0` (or blank).

## Muli-Row (Shortform)

``````=MAP(score_rng, absences_rng,
LAMBDA(score, absences,
[exception_formula_here]))
``````
1. Uses the MAP function to pass a pair of values (one from each column) row-by-row into a LAMBDA function.
2. Replace `score_rng` and `absences_rng` with the actual refferences to the `score` and `absences` ranges.
• Ranges can be located anywhere but must be the same height.
• For example, `Sheet1!A2:A10` and `G32:G40`are fine (9 rows each), but `A2:A` and `B:B` are not because `B:B` contains an extra row.

## Multi-Row (Longform)

``````=MAP(score_rng, absences_rng,
LAMBDA(score, absences, IF(score>0,
IFERROR(
REDUCE(score, SEQUENCE(absences),
LAMBDA(s, a, s*(1-a*pct))),
score),
IFNA(#N/A))))
``````
1. Replace `score_rng` and `absences_rng` per notes above (shortform version).
2. `pct` is a hard coded percentage or refference to a cell containing one.
eg `H32` or `8%` or `0.08`