How can I ...

  • Thread starter Thread starter Sebastien
  • Start date Start date
S

Sebastien

Hello,
I have a value in a cell and depending on this amount in this cell I would
like to do something like this :
< 100 then I apply a rate of 12%
between 101 and 200 I apply a rate of 8%
Over 2001 I apply a rate of 5%

If anyone can help me I would really apreciate,
Thanks,

Seb
 
Hi Sebastien
if your value is in A1 try
=IF(A1<100,A1*0.12,IF(A1<200,A1*0.08,IF(A1>=200,A1*0.05,"not
defined")))
 
Frank Kabel said:
if your value is in A1 try
=IF(A1<100,A1*0.12,IF(A1<200,A1*0.08,IF(A1>=200,A1*0.05,"not
defined")))
Click to expand...
....

Too clever. A1<100, A1<200 and A1>=200 is numerically all-inclusive. There
is no real number value that doesn't satisfy at least one of these (x for
which 100 <= x < 200 satisfies the first two). Blank cells will be treated
as satisfying the first one. Text representations of numbers will be treated
as if they were numbers, but nonnumeric text, e.g., "fubar", will throw
errors rather than return "not defined" because all nonnumeric text strings
will satisfy the last of these, as will boolean TRUE/FALSE, but they'll be
converted to numbers, so 0.05 for TRUE and 0 for FALSE. Finally, if A1 were
any error value, your formula would return that error value.

I believe you'll find there's no value you can put in cell A1 that would
ever result in your formula returning "not defined", so you might as well
simplify it to

=A1*IF(A1<100,0.12,IF(A1<200,0.08,0.05))

or add a useful initial condition such as

=IF(ISNUMBER(A1),A1*IF(A1<100,0.12,IF(A1<200,0.08,0.05)),"not defined")
 
Harlan Grove said:
. . . Text representations of numbers will be treated as if they were
numbers, . . .
Click to expand...
....

I'm wrong. Any text will satisfy the A1>=200 condition.
 
Back
Top