1

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?

0

2 Answers 2

1

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.

screenshot of included sample spreadsheet's tab serving as the Penalties Calculator

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.

screenshot of demo Sheets file with the above formula in action on a range of scores and absences

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%
)))
2
  • 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.
    – Joel Reid
    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.
    – Blindspots
    Commented Sep 10, 2023 at 22:31
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.