Excel向导:如何创建一个公式“如果x≥然后乘以y”等等?

Have only used excel for the basics, wrestling with this problem here:

只使用excel做基础，在这里解决这个问题:

I have these ranges:

我有这些范围:

0-499 * 0

0 - 499 * 0

500-999 * 1

500 - 999 * 1

1000-1499 * 4

1000 - 1499 * 4

I would like to write a formula where what is in my cell will be multiplied by 0, 1, etc. depending on what range it falls into. Was able to figure out =IF(C21>=10000,C21*1) for if a value in a cell is greater or equal to 10,000, however I can't seem to figure out how to do ranges.

我想写一个公式，在单元格里的东西会乘以0 1，等等，这取决于它的范围。我们可以算出=IF(C21>=10000,C21*1)如果一个单元格中的值大于或等于10,000，但是我似乎不知道如何做值域。

Not that tech-savvy when it comes to excel, any help at all would be greatly appreciated!

对于excel来说并不是那么精通技术，任何帮助都是非常感谢的!

Thanks for looking

谢谢你看

4 个解决方案

#1

You can use another IF in the ELSE part of the expression, evaluation will stop as soon as a TRUE condition is met;

可以在表达式的ELSE部分中使用另一个IF，只要满足一个真实条件，计算就会停止;

=A1 * IF(A1 < 500, 0, IF(A1 < 1000, 1, IF(A1 < 1500, 4, 0)))

(The last 0 is the case when the value is > 1499)

(最后一个0是> 1499)

#2

You can use nested IF statements for doing ranges:

您可以使用嵌套IF语句来执行范围:

=IF(C21>=500,IF(C21>=1000,IF(C21<1500,C21*4,'dontknowwhatyouwanthere'),C21*1),0)

#3

How about nested Ifs?

嵌套的Ifs怎么样?

=IF(A1<1000;IF(A1<500;+A1*0;+A1*1);+A1*4)

Then you've got:

然后你有:

If it's less than 1000 another if:

如果小于1000，如果:

If it's less than 500 You do the " * 0 "
如果小于500，用" * 0 "
If it's not (you are at 500-999 range, from the first if) You do the " * 1 "
如果不是(从第一个If开始，您的范围是500-999)，则执行“* 1”

Else it's not less than 1000:

否则不小于1000:

You have your " * 4 "
你有你的" * 4 "

#4

There are also free templates that you can adapt that use the "lookup" function; I found them at the following link: Free Templates that work with the following program Excel Statements. Hope this is helpful as a shortcut.

还可以使用“查找”功能调整免费模板;我在以下链接找到了它们:使用以下程序Excel语句的免费模板。希望这是一条捷径。

#1