Multiple If Statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I was wondering if you could help me? How would I express this in an Excel
statement?:

Rates are as follows:
Up to 55,000 x .5% of total property value
from 55,000 to 250,000 x1% of total property value
from 250,000 to 400,000 x1.5% of total property value
from 400,000 and up x 2% of total property value
 
One option is to use nested IF statements. It gets cluncky with lots of
values.
This is off the top of my head, might be some formatting mistakes.

Given
Cell A1 = Property Value

=if( A1 < 55,000, A1 * 0.005,
if( A1 < 250,000, A1 * 0.01,
if( A1 < 400,000, A1 * 0.015,
A1 * 0.02 ) ) )

Ive formatted that out to make it easy to read, it would be all on one
line.

In essence, IF < 55,000, THEN take .5% ELSE IF < 250,000 THEN take 1%
ELSE IF ... etc.

To clean things up you could put the constants (55,000, 0.005, etc) in
cells and references those instead of hardcoding them like I did.
 
=IF(A1<=55000,A1*.005,IF(AND(A1>55000,A1<=250000),A1*.01,IF(AND(A1>250000,A1<400000),A1*.015,A1*.02)))

Dave
 
You need the AND statement in order to test for a range. The way you have it
here, A1=10 is TRUE for both less than 55000 and less than 25000.

See my response to the question.

Dave
 
Another thought is to use a Lookup Table. Creat a table that looks
like this.

Colunm A Column B
0 .005
55000 .01
250000 .015
400000 .02

Give it a range name of data. You can then use this formula

=a1*vlookup(a1,data,2)
 
You need the AND statement in order to test for a range. The way you have it
here, A1=10 is TRUE for both less than 55000 and less than 25000.
Click to expand...

Makes sense - I guess im used to other languages, where its evalulated
left to right and that would be valid :)

That still seems a strange implementation of an IF statement.

I would think excel would parse left to right, and break out of the IF
once the first expresion evaluates to TRUE.

Oh well
 
Right. That also has the benefit of being more flexible, if the percentages,
or ranges, change.

Dave
 
Actually, now that I play around with it it looks like your method may work.
And it's more efficient.
 
Well, I didnt test it, but logically I would expect it to work.
Once the first condition passes, excel should stop evaluating the rest
of the expression.

True, it is more efficenit as well, largely irrelevant for a simple
formula like that, but defintely not to be ignored - not point wasting
unecc cycles :)
 
Back
Top