Multiple If Statement
 

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


--
Thanks for your help!

Multiple If Statement
 

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.

TamIam wrote:
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


--
Thanks for your help!

Multiple If Statement
 

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

Dave
--
Brevity is the soul of wit.


"TamIam" wrote:

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


--
Thanks for your help!

Multiple If Statement
 

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
--
Brevity is the soul of wit.


" wrote:

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.

TamIam wrote:
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


--
Thanks for your help!

Multiple If Statement
 

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)


Dave F wrote:
=IF(A1<=55000,A1*.005,IF(AND(A155000,A1<=250000), A1*.01,IF(AND(A1250000,A1<400000),A1*.015,A1*.02) ))

Dave
--
Brevity is the soul of wit.


"TamIam" wrote:

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


--
Thanks for your help!

Multiple If Statement
 

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

Multiple If Statement
 

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

Dave
--
Brevity is the soul of wit.


"willwonka" wrote:

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)


Dave F wrote:
=IF(A1<=55000,A1*.005,IF(AND(A155000,A1<=250000), A1*.01,IF(AND(A1250000,A1<400000),A1*.015,A1*.02) ))

Dave
--
Brevity is the soul of wit.


"TamIam" wrote:

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


--
Thanks for your help!

Multiple If Statement
 

Actually, now that I play around with it it looks like your method may work.
And it's more efficient.
--
Brevity is the soul of wit.


" wrote:

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

Multiple If Statement
 

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