Excel多区间数值舍入咨询：按指定规则处理计算后的单元格结果

Hey there! Let's work through this custom rounding requirement you've got. It's a multi-tier rule set, so we can implement it cleanly in tools like Excel (since you mentioned cells) or Python for data processing. Here's how to do it:

Excel Implementation #

For spreadsheet cells, a nested IF formula paired with Excel's MROUND function will handle all your rules perfectly. Just drop this into the cell where you want the rounded result (replace A1 with your target cell reference):

=IF(A1<5, MROUND(A1, 0.5), IF(AND(A1>=5, A1<20), MROUND(A1, 1), IF(AND(A1>=20, A1<50), MROUND(A1,5), MROUND(A1,10))))

How this breaks down: #

• Values less than 5: MROUND(A1, 0.5) rounds to the nearest 0.5 (e.g., 2.3 → 2.0, 4.6 → 4.5)
• Values 5 to 19.999...: MROUND(A1,1) rounds to the nearest whole number (e.g., 8.4 → 8, 19.7 → 20)
• Values 20 to 49.999...: MROUND(A1,5) rounds to the nearest multiple of 5 (e.g., 22 → 20, 47 → 45)
• Values 50 and above: MROUND(A1,10) rounds to the nearest multiple of 10 (e.g., 54 → 50, 78 → 80)

Note: If you're using an older Excel version where MROUND isn't available, you can replace each MROUND call with manual rounding math. For example, ROUND(A1*2,0)/2 works as a substitute for rounding to 0.5.

Python Implementation #

If you're processing data programmatically (like with a Pandas DataFrame of cell values), here's a custom function that applies your exact rules:

def custom_round(value):
    if value < 5:
        return round(value * 2) / 2  # Rounds to nearest 0.5
    elif 5 <= value < 20:
        return round(value)
    elif 20 <= value < 50:
        return round(value / 5) * 5
    else:  # For values >= 50
        return round(value / 10) * 10

# Test it out with sample values
test_values = [3.2, 7.5, 26, 59]
for val in test_values:
    print(f"Original: {val} → Rounded: {custom_round(val)}")

Sample Output: #

Original: 3.2 → Rounded: 3.0
Original: 7.5 → Rounded: 8
Original: 26 → Rounded: 25
Original: 59 → Rounded: 60

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