Google Sheets脚本开发：基于分层规则的总计值计算需求

First, let's make sure we're aligned on your requirements:

• Tiered unit rule: Values in 0-100 count as 1 unit, 101-200 count as 2 units, and so on (each 100-number interval increases the unit count by 1)
• Calculate total units between a start and end value
• Adjust the total with a factor, ensuring the final cost never drops below 1
• If the adjustment factor is less than 1, we'll cap it at 1 (since a factor <1 would lower the total, and we need to maintain the minimum cost requirement)

Step-by-Step Formula Implementation #

We'll use Google Sheets' LET function to keep the formula clean and readable (this is supported in all modern versions of Google Sheets).

Assume:

• Start value is in cell A1
• End value is in cell B1
• Adjustment factor is in cell C1

Here's the full formula:

=LET(
  a, MIN(A1, B1),
  b, MAX(A1, B1),
  k_start, CEILING(MAX(a, 1)/100, 1),
  k_end, CEILING(MAX(b, 1)/100, 1),
  total_units, IF(
    k_start = k_end,
    (b - a + 1)*k_start,
    (100*k_start - a + 1)*k_start +
    100*((k_end + k_start)*(k_end - k_start - 1)/2) +
    (b - 100*(k_end - 1))*k_end
  ),
  adjusted_factor, MAX(C1, 1),
  total_cost, total_units * adjusted_factor,
  MAX(total_cost, 1)
)

Breakdown of Each Part #

1. Normalize Start/End Values:

   • a = MIN(A1, B1) and b = MAX(A1, B1) ensure we always calculate from the smaller to larger value, even if you accidentally enter start > end.

2. Calculate Tier for Start/End:

   • k_start = CEILING(MAX(a, 1)/100, 1): Fixes edge cases like a=0 (which should count as 1 unit, not 0) and correctly maps values to their tier (100 → 1 unit, 101 → 2 units, etc.).
   • k_end uses the same logic for the end value.

3. Calculate Total Units:

   • If start and end are in the same tier (k_start = k_end), multiply the number of values in the range by the tier number.
   • If they're in different tiers, split the calculation into three parts:
     • Values from start to the end of its tier
     • Full middle tiers (each contributes 100 values multiplied by their tier number)
     • Values from the start of the end tier to the end value

4. Adjustment Factor Handling:

   • adjusted_factor = MAX(C1, 1) ensures we never use a factor less than 1, as requested.

5. Final Cost with Minimum:

   • MAX(total_cost, 1) guarantees the final result is at least 1, no matter what the intermediate calculation gives.

Alternative: If Start/End Are Individual Values (Not a Range) #

If you meant calculating units for two separate values (start and end) instead of a range between them, use this simplified formula:

=LET(
  unit_start, CEILING(MAX(A1,1)/100,1),
  unit_end, CEILING(MAX(B1,1)/100,1),
  total_units, unit_start + unit_end,
  adjusted_factor, MAX(C1,1),
  total_cost, total_units * adjusted_factor,
  MAX(total_cost,1)
)

Example Test Cases #

All these cases follow your tier rules, handle adjustment factors <1, and maintain the minimum cost of 1.

内容的提问来源于stack exchange，提问作者Josh