Excel IF函数设置数值上下限返回FALSE问题求助

Hey there! Let's fix that formula issue you're having. The problem with your original formula is that you're misusing the AND function — it's meant to check multiple logical conditions, not specify values to return. Let's break down the correct solutions:

Solution 1: Corrected Nested IF Formula #

Your goal is to cap values between 0,05 (lower bound) and 0,4 (upper bound). A straightforward nested IF will do this cleanly:

=IF(I5<0,05;0,05;IF(I5>0,4;0,4;I5))

Here's how it works:

• First, check if I5 is less than 0,05: if yes, return 0,05.
• If not, check if I5 is greater than 0,4: if yes, return 0,4.
• If neither condition is true, return the original value in I5.

Solution 2: Simpler MEDIAN Function Shortcut #

For this kind of "capping" scenario, Excel's MEDIAN function is a clever shortcut that avoids nested IFs entirely. It returns the middle value of a set of numbers, which automatically handles the upper and lower bounds:

=MEDIAN(0,05;I5;0,4)

How this works:

• If I5 is below 0,05: the three values are 0,05, I5, 0,4 → the middle value is 0,05.
• If I5 is above 0,4: the three values are 0,05, 0,4, I5 → the middle value is 0,4.
• If I5 is between 0,05 and 0,4: the middle value is I5 itself.

Both formulas will give you the result you need, but the MEDIAN version is more concise and easier to read.

内容的提问来源于stack exchange，提问作者Anke Rojic Becker