英文:
Round a number by specific decimal value in excel
问题
我正在尝试对一个带有特定条件的小数进行四舍五入。如果第一个小数点大于或等于9，则向上舍入，否则向下舍入。需要帮助。
100.123 -> 100
100.8 -> 100
100.9999 -> 101
100.9001 -> 101
我知道在Excel中有roundup和rounddown函数，但它们是基于小数值是否大于或等于5来进行四舍五入或舍去的逻辑。
英文:
I am trying to round a decimal number with certain condition. If first decimal point is greater than or equal to 9 then round up else round down. Need help in this.
100.123 -> 100
100.8 -> 100
100.9999 -> 101
100.9001 -> 101
I am aware of roundup and rounddown functions in excel, but that works based on logic that if decimal value is greater than or equal to 5 then round up else round down.
答案1
得分: 3
IF "E5" 是十进制数：
=IF(NUMBERVALUE(LEFT(RIGHT(E5;FIND(".";E5)-1);1))>=9;ROUNDUP(E5;0);ROUNDDOWN(E5;0))
英文:
IF "E5" is the decimal number:
=IF(NUMBERVALUE(LEFT(RIGHT(E5;FIND(".";E5)-1);1))>=9;ROUNDUP(E5;0);ROUNDDOWN(E5;0))
答案2
得分: 3
以下是要翻译的内容：
这是我的提案。它与Toby的提案非常相似，但我将数字视为数字，而不是文本：
=IF(A1-ROUNDDOWN(A1,0)>=0.8,ROUNDUP(A1,0),ROUNDDOWN(A1,0))
它产生以下结果：
英文:
Hereby my proposal. It's very similar to the one from Toby, but I treat numbers as numbers, not as text:
=IF(A1-ROUNDDOWN(A1,0)>=0.8,ROUNDUP(A1,0),ROUNDDOWN(A1,0))
It gives following results:
答案3
得分: 3
Another option:
=INT(A1)+(MOD(A1,1)>=0.9)
英文:
Another option:
=INT(A1)+(MOD(A1,1)>=0.9)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论