Multiple If Statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TamIam - 18 Oct 2006 19:34 GMT

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

Signature

Thanks for your help!

Reply to this Message

tinyguppie@hotmail.com - 18 Oct 2006 19:46 GMT

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.

> I was wondering if you could help me? How would I express this in an Excel
> statement?:
[quoted text clipped - 4 lines]
> from 250,000 to 400,000 x1.5% of total property value
> from 400,000 and up x 2% of total property value

Reply to this Message

Dave F - 18 Oct 2006 19:53 GMT

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

Signature

Brevity is the soul of wit.

> One option is to use nested IF statements. It gets cluncky with lots of
> values.
[quoted text clipped - 25 lines]
> > from 250,000 to 400,000 x1.5% of total property value
> > from 400,000 and up x 2% of total property value

Reply to this Message

tinyguppie@hotmail.com - 18 Oct 2006 20:06 GMT

> 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.

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

Reply to this Message

Dave F - 18 Oct 2006 20:17 GMT

Actually, now that I play around with it it looks like your method may work.
And it's more efficient.

Signature

Brevity is the soul of wit.

> > 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.
[quoted text clipped - 8 lines]
>
> Oh well

Reply to this Message

tinyguppie@hotmail.com - 18 Oct 2006 20:55 GMT

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 :)

Reply to this Message

Dave F - 18 Oct 2006 19:49 GMT

=IF(A1<=55000,A1*.005,IF(AND(A1>55000,A1<=250000),A1*.01,IF(AND(A1>250000,A1<400000),A1*.015,A1*.02)))

Dave

Signature

Brevity is the soul of wit.

Reply to this Message

willwonka - 18 Oct 2006 19:57 GMT

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)

> =IF(A1<=55000,A1*.005,IF(AND(A1>55000,A1<=250000),A1*.01,IF(AND(A1>250000,A1<400000),A1*.015,A1*.02)))
>
[quoted text clipped - 10 lines]
> > from 250,000 to 400,000 x1.5% of total property value
> > from 400,000 and up x 2% of total property value

Reply to this Message

Dave F - 18 Oct 2006 20:10 GMT

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

Dave

Signature

Brevity is the soul of wit.

> Another thought is to use a Lookup Table. Creat a table that looks
> like this.
[quoted text clipped - 23 lines]
> > > from 250,000 to 400,000 x1.5% of total property value
> > > from 400,000 and up x 2% of total property value

Reply to this Message