跳到主要内容

如何在Excel中将公式结果限制为最大值或最小值？

这里需要输入一些单元格，现在我想使用公式对单元格求和，但将结果限制为最大值（例如100）。换句话说，如果总和小于100，则显示总和，否则显示100。

将公式结果限制为最大值或最小值

将公式结果限制为最大值或最小值

要处理此任务，只需在Excel中应用“最大值”或“最小值”功能。

将公式结果限制为最大值（100）

选择要在其中放置公式的单元格，键入此公式 = MIN（100，（SUM（A5：A10））），A5：A10是您要汇总的单元格范围，然后按 输入。 现在，如果总和大于100，它将显示100，如果不是，则显示总和。

求和大于100，显示为100
doc limi最大值最小值1
总和小于100，显示总和
doc limi最大值最小值2

将公式结果限制为最小值（20）

选择一个放置公式的单元格，键入 = MaX（20，（SUM（A5：A10））），A5：A10是您要汇总的单元格范围，然后按 输入。 现在，如果总和小于20，它将显示20；否则，将显示XNUMX。 如果不是，则显示总和。

总计小于20，显示20
doc limi最大值最小值3
总和大于20，显示总和
doc limi最大值最小值4

最佳办公生产力工具

🤖 Kutools 人工智能助手：基于以下内容彻底改变数据分析： 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup  |   多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧   |  工作簿和工作表管理器   |  资源库 （自动文本）   |  日期选择器   |  合并工作表   |  加密/解密单元格   |  按列表发送电子邮件   |  超级筛选   |   特殊过滤器 （过滤粗体/斜体/删除线...）...
前 15 个工具集:  12 文本 工具 (添加文本, 删除字符，...）   |   50+ 图表 类型 (甘特图，...）   |   40+ 实用 公式 (根据生日计算年龄，...）   |   19 插入 工具 (插入二维码, 从路径插入图片，...）   |   12 转化 工具 (小写金额转大写, 货币兑换，...）   |   7 合并与拆分 工具 (高级组合行, 分裂细胞，...）   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能，体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述

Office Tab 为 Office 带来选项卡式界面，让您的工作更加轻松

  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
Comments (24)
No ratings yet. Be the first to rate!
JP
JP
This comment was minimized by the moderator on the site
I would like the cell to return the value calculated, but is less than the minimum, it will only show the minimum but if greater than the maximum it will only show the maximum, but if in between the true value would appear. But the range may changed on the level chosen. D4 - insert current salary / D6 choose level / D7 would show minimum of that level and D8 would show the maximum of that level. D9 would be the percentage in increase and E9 would show the new calculcation. D11 titled new salary would display the calculation but if less than the minimum on show the minimum value, if greater the maximum only show the maximum value
Reply
0
0
Ola
Ola
This comment was minimized by the moderator on the site
If the summation is more than this sum up the tale invoices below the target amount

Merchant Name Recoveries from Merchant
Baxter Plc ₦1,001,000.00 ₦4,634,642.50
Baxter Plc ₦59,197.50
Baxter Plc ₦641,000.00
Baxter Plc ₦1,751,000.00
Baxter Plc ₦101,000.00
Baxter Plc ₦1,021,000.00
Baxter Plc ₦101,000.00
Baxter Plc ₦746,000.00
Reply
0
0
Ola
Ola     Ola
This comment was minimized by the moderator on the site
=IF(SUMIFS('FR-4---Recoveries from Merchant'!$K:$K,'FR-4---Recoveries from Merchant'!$E:$E,$A15)>$C15,SUMPRODUCT(SMALL(INDEX(('FR-4---Recoveries from Merchant'!$K$8:$K$27)+('FR-4---Recoveries from Merchant'!$E$8:$E$27<>$A15)*1E+99,,),ROW($1:$7))),(SUMIFS('FR-4---Recoveries from Merchant'!$K:$K,'FR-4---Recoveries from Merchant'!$E:$E,$A15)))

That's final solution I was able to provide
Reply
0
0
Ola
Ola     Ola
This comment was minimized by the moderator on the site
My question is, if the summation exceeds the maximum, I would like to return value less than the maximum, say for instance, the total is #5,000,000 and I could only pay #4,634,642.50 as the available amount. then I would like to return value like Sum(₦1,001,000.00, ₦59,197.50,₦641,000.00,₦1,751,000.00,₦101,000.00,₦1,021,000.00), which is lower than the available amount. Also, for easy reconciliation, we could refer to the specific invoice numbers.
Reply
0
0
Sarah Fraser
Sarah Fraser
This comment was minimized by the moderator on the site
Hi! This instruction is awesome. Thank you! I have a further question: if the summation exceeds the maximum, I would like to return the value 0 instead of the maximum. Is there a way to do that?
Thank you!
Reply
0
0
Sunny
Sunny     Sarah Fraser
This comment was minimized by the moderator on the site
Hi, regarding this issue, please refer to this post Go to now
Reply
Report
0
0
Sarah Fraser
Sarah Fraser     Sunny
This comment was minimized by the moderator on the site
Thank you!
Reply
0
0
Marcos
Marcos
This comment was minimized by the moderator on the site
Q stresse.....ESSAS FÓRMULAS, não funionam aqui.
Reply
0
0
Sunny
Sunny     Marcos
This comment was minimized by the moderator on the site
Hi, the formula provided above is work in English Excel version, if you are in Portugues version, try formula:
=MÍNIMO(100;(SOMA(A5:A10)))
Reply
Report
0
0
V Rogers
V Rogers
This comment was minimized by the moderator on the site
I am trying to limit the amount in a cell to a max of 24. I am calculating the number of hours worked divided by 30 with this formula, for example
=F5/K1

F5 is the specific employees hours worked,
and K1 is a hidden cell with a value of 30
(because for every 30 hours they work, they earn 1 hour of sick time) but the max limit is 24 hours in a year and i don't know how to limit the total to a max of 24 hours earnable.
Any help?
Reply
0
0
Sunny
Sunny     V Rogers
This comment was minimized by the moderator on the site
Hi, V Rogers, try this formula: =MIN(1,SUM(E:E)/K1)
in the formula, E:E is the column that contains employees' work hours, you can change it as you need., the result cell (F5) needed to be formatted as 37:30:55 in the Format Cells dialog. See screenshot:
https://www.extendoffice.com/images/stories/comments/sun-comment/doc-max-hours-1.png
Reply
Report
0
0
Sanchez
Sanchez
This comment was minimized by the moderator on the site
Alguém me ajuda
Qual fórmula uso na celula onde meu resultado limite é de 1000 após esse resultado ele voltar a 0 e continuar somando e sempre q atingir 1000 ele voltar a 0
Reply
0
0
Sunny
Sunny     Sanchez
This comment was minimized by the moderator on the site
Hi, try this formula: =IF(SUM(D1:D2)>1000,0,SUM(D1:D2))
D1 and D2 is the first two data that used to add.
https://www.extendoffice.com/images/stories/comments/sun-comment/doc-formula-01.png
Reply
Report
0
0
Nils
Nils
This comment was minimized by the moderator on the site
Good day,

does anyone have an idea how this works on multiplication formulas?

A1*A20= 100, but min value shall be 120

Is there a formula that always shows at least 120?
Reply
0
0
Sunny
Sunny     Nils
This comment was minimized by the moderator on the site
Hi, use formula like this: =MAX(120,(A1*A20))
Reply
Report
0
0
Andrew
Andrew
This comment was minimized by the moderator on the site
For anyone looking to have a min AND a max, use the following formula:

=MIN(+20,MAX(-20,SUM(A5:A10)))

This will limit the calculated sum to between -20 and +20, or any number of your choosing.
Reply
0
0
Marcos Dantas
Marcos Dantas     Andrew
This comment was minimized by the moderator on the site
Fera, como vc conseguiu isso? Aqui não funciona de jeito nenhum, no excel BR.
Reply
0
0
Oscar
Oscar
This comment was minimized by the moderator on the site
Hola, estoy intentando aplicar la fórmula pero no me funciona. En mi caso el valor min es en horas, no se si es por eso que me da error.

Lo que quiero hacer es una celda que repita el valor de la celda adyacente limitando a un máximo de 8 horas. A ver si me pueden ayudar, gracias.
Reply
0
0
Marco B
Marco B
This comment was minimized by the moderator on the site
Thank you, worked perfectly :)
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations