Geek Answers Handbook

Excel Wizards. How to create a formula for "if x ≥ and then multiply by y" etc.?

Used only excel for the basics, fighting this problem here:

I have these ranges:

0-499 * 0

500-999 * 1

1000-1499 * 4

I would like to write a formula in which what is in my cell will be multiplied by 0, 1, etc. depending on what range it falls into. It was possible to find out = IF (C21> = 10000, C21 * 1) if the value in the cell is greater than or equal to 10000, however I cannot figure out how to make ranges.

Not the technical approach when it comes to excellence; any help at all will be appreciated!

Thanks for watching

+4
source share
4 answers

You can use another IF to ELSE part of the expression, the evaluation will be stopped as soon as the condition TRUE ;

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

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

+6
source

You can use nested IF statements for ranges:

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

What about nested ifs?

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

Then you have:

If it is less than 1000 other, if:

  • If it is less than 500 , you make " * 0 "
  • If it is not (you are in the range of 500-999, starting from the first, if) you do " * 1 "

Otherwise, at least 1000 :

  • You have " * 4 "
0
source

There are also free templates that you can adapt using the “lookup” function; I found them at the following link: Free templates that work with the following Excel Reports program. Hope this is useful as a shortcut.

0
source

More articles:


All Articles