Excel双刻度线性插值公式编写求助：已知端点对应值求中间结果

Got it, let's break this down since you already handle single-variable linear calculations—this just extends that logic to two variables with a combined linear relationship.

Step 1: Understand the Linear Relationship #

We have two input ranges:

• First scale: 85% to 93% (let’s assume this value lives in cell A1)
• Second scale: 91% to 94% (value in cell B1)

Known endpoints:

• When A1=85% and B1=91%, the result is 50%
• When A1=93% and B1=94%, the result is 71%

The total change in result is 21% (71% - 50%), driven by an 8% shift in the first scale and a 3% shift in the second. Since we’re assuming combined linear growth, the result will be a linear combination of changes in both scales.

Step 2: The Excel Formula #

We can simplify this into a single formula that accounts for both variables' linear contributions. Here are two equivalent versions (use whichever feels more intuitive):

Using percentage values:

=50% + (21%/11%)*((A1 - 85%) + (B1 - 91%))

Using decimal values (often more reliable in Excel to avoid formatting quirks):

=0.5 + (21/11)*((A1 - 0.85) + (B1 - 0.91))

Step 3: How It Works & Example Check #

Let’s test with your example inputs (A1=86%, B1=92%):

1. Calculate individual shifts: 86% - 85% = 1% and 92% - 91% = 1%
2. Total combined shift: 1% + 1% = 2%
3. Result increment: (21%/11%)*2% ≈ 3.82%
4. Final result: 50% + 3.82% ≈ 53.82%

For the endpoint check (A1=93%, B1=94%):

• Total combined shift: 8% + 3% = 11%
• Result increment: (21%/11%)*11% = 21%
• Final result: 50% + 21% = 71% (matches your requirement perfectly)

This formula keeps the single-scale linear behavior you’re familiar with—if you fix one variable, the other’s impact will follow a straight line, just like your existing single-scale calculations.

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