Setting boundaries for a formula result within formula

Question

Level 1

13 points

Setting boundaries for a formula result within formula

I fired up Backyard Football 2002 on ScummVM earlier this week and decided to track my season stats in Numbers... they're all pretty straightforward except one–passer rating.

tl;dr there's an official, very specific formula used by the NFL that requires four calculations for a formula to produce a number from 0 to 158.3. (Here's a link with a calculator for the official passer rating formula.) I'm looking for help because there's a few conditions to calculating it and I don't know how to integrate them into a Numbers formula.

...So what are the conditions?

If any of the four numbers turn out to be greater than 2.375 (why that specific? dunno–ask the NFL), then they should be capped at 2.375.
If any turn out to be less than 0, they should be defaulted to 0.

Anyway, is there a way to do this within a formula in Numbers? If so, how? I'll continue in a second post with what I've got so far and a few more details.

Posted on Oct 4, 2021 11:24 PM

Reply

Answer 1

Best reply

DreamOnMan92 Author

Level 1

13 points

Oct 5, 2021 12:04 AM in response to DreamOnMan92

Here's my results through 3 games of a 14-game season. The stats given are only the ones relevant in passer rating calculation, plus the raw results for each step in the calculation process. (I formatted the non-whole-number columns as single-digit decimal numbers instead of keeping them Automatic for simplicity's sake.)

Where columns H-K (RtgA, RtgB, RtgC, and RtgD) are the four inputs used in the final calculation, column L (Rtg/6) is the sum of those four divided by 6, and column M (FinRtg) is that result multiplied by 100–the actual Passer Rating. You can see that every result from B, C and D (columns I-K) except one is outside the boundaries of the valid PR input rules (0≥x≤2.375) and that throws everything off.

My current formulas for inputs (using first line as example)

RtgA = (($B2÷$C2)−0.3)×5

RtgB = ($E2−3)÷4

RtgC = ($F2÷$C2)×20

RtgD = 2.375−(($G2÷$C2)×25)

Reply