Cap & Floor Collar: Clamp Numbers Between Min & Max Bounds (MIN & MAX)

Keep units consistent

B2, D2 and E2 are ordinary numbers expressed in percentage points in this example: 4.85 means 4.85%, not the spreadsheet fraction 0.0485. Do not apply percentage formatting to 4.85, which would display 485%.

You can instead divide all three inputs by 100 and use percentage formatting. Never mix these representations in the same calculation. This is arithmetic clamping, not a complete financial contract valuation.

=IF(COUNT(B2,D2,E2)<>3,"Enter three numeric values",IF(D2>E2,"Invalid bounds",MAX(D2,MIN(E2,B2))))

Validate before comparing methods

Require three numeric values and floor<=cap. MIN limits the upper end and MAX enforces the lower end. MEDIAN returns the same value for valid ordered bounds.

Both versions below reject inverted bounds. Without that guard MEDIAN silently sorts the bounds while the MIN/MAX form does something different. Equal bounds are valid and give that fixed value.

=IF(COUNT(B2,D2,E2)<>3,"Enter three numeric values",IF(D2>E2,"Invalid bounds",MEDIAN(D2,B2,E2)))

Try below, inside and above the interval

With bounds 3.5 and 6.5, input2.1 gives3.5, input4.85 stays4.85 and input8.4 gives6.5. Test exact endpoints, equal bounds, negative numbers, blank cells and numeric text. The validation rejects text rather than allowing aggregation functions to silently ignore it.

Further reading

• Microsoft Support — MIN function
• Microsoft Support — MEDIAN function