Conditional formulas?

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Is there such a thing as conditional formula?

I want a cell that will perform a formula like this,

if value is between 0 and 100 then multiply by 5 and
subtract 12,
if value is between 101 and 200 then multiply by 6 and
subtract 13.

Is this possible? any and all help is greatly appreciated.
 
Michael,

A simple multiple If does it
=IF(AND(A1>=0,A1<=100),A1*5,IF(AND(A1>=101,A1<=200),A1*6,0))

If you are likely to need more tests, it may be better to use VLOOKUP and
multiply the result, like so
=A1*VLOOKUP(A1,{0,5;101,6;201,7},2)
just add to the conditions. The table values {0,5;101,6;201,7} could be put
in a table and referenced in the VLOOKUP.
 
One way:

=IF((A1>=0)*(A1<=100),A1*5-12,IF((A1>=101)*(A1<=200),A1*6-
13,"Error"))

HTH
Jason
Atlanta, GA
 
...
...
if value is between 0 and 100 then multiply by 5 and
subtract 12,
if value is between 101 and 200 then multiply by 6 and
subtract 13.
Click to expand...
...

Would this value always be a whole number? If not, do you want to exclude values
between 100 and 101 like 100.25?
 
Assuming that the logic goes on upwards, or that the question is very general:
There is a numerical logic to this. For each hundred reached, add one here and add another
one there. So divide the number by 100 and remove the decimals (INT) to get the ones -or
more general; spot the pattern.
=A1*(5+INT(A1/100))-(12+INT(A1/100))

You are also introduced to boolean math and IFs in the other good replies. So yes, there
are very many different conditional formulas.
 
Back
Top