Conditional Formula

[Captain Steve]

Hi All-

I want to write a formula that will look at project value and assign a % fee
charge as follows: Project 0 - $ 100, Fee 10%; Project $101 -$500, Fee 8%,
Project above $501, Fee 5%.

I can write the formula that looks at project cost and finds fee,

BUT I want to set it so that a $600 project will generate a 10% charge for
first $100, 8% charge for next $399 and a 5% charge for the remaining $99.

How do I do it?

Many Thanks!!

[Elkar]

Assuming Project Cost is in cell A1, try this:

=(MIN(A1,100)*0.1)+IF(A1>100,(A1-100)*0.08,0)+IF(A1>500,(A1-500)*0.05,0)

HTH,
Elkar

"Captain Steve" wrote:

> Hi All-
>
> I want to write a formula that will look at project value and assign a % fee
> charge as follows: Project 0 - $ 100, Fee 10%; Project $101 -$500, Fee 8%,
> Project above $501, Fee 5%.
>
> I can write the formula that looks at project cost and finds fee,
>
> BUT I want to set it so that a $600 project will generate a 10% charge for
> first $100, 8% charge for next $399 and a 5% charge for the remaining $99.
>
> How do I do it?
>
> Many Thanks!!

[Elkar]

If I'd taken a bit more time to think about it, I would have suggested this
slightly more efficient formula (which I myself would prefer). But both
should work just fine.

=(MIN(A1,100)*0.1)+(MAX(A1-100,0)*0.08)+(MAX(A1-500,0)*0.05)

I think I need more caffeine...

"Elkar" wrote:

> Assuming Project Cost is in cell A1, try this:
>
> =(MIN(A1,100)*0.1)+IF(A1>100,(A1-100)*0.08,0)+IF(A1>500,(A1-500)*0.05,0)
>
> HTH,
> Elkar
>
> "Captain Steve" wrote:
>
> > Hi All-
> >
> > I want to write a formula that will look at project value and assign a % fee
> > charge as follows: Project 0 - $ 100, Fee 10%; Project $101 -$500, Fee 8%,
> > Project above $501, Fee 5%.
> >
> > I can write the formula that looks at project cost and finds fee,
> >
> > BUT I want to set it so that a $600 project will generate a 10% charge for
> > first $100, 8% charge for next $399 and a 5% charge for the remaining $99.
> >
> > How do I do it?
> >
> > Many Thanks!!

[Sloth]

=MAX(A1,0)*0.1-MAX(A1-100,0)*0.02-MAX(A1-500,0)*0.03

I believe this is what you are looking for. This multiplies the whole
number by 10% and then subtracts 2% for values over 100 (making them 8%), and
subtracts another 3% for values over 500 (making them 5%). Just in case you
were wondering where the 2% and 3% came from.

"Captain Steve" wrote:

> Hi All-
>
> I want to write a formula that will look at project value and assign a % fee
> charge as follows: Project 0 - $ 100, Fee 10%; Project $101 -$500, Fee 8%,
> Project above $501, Fee 5%.
>
> I can write the formula that looks at project cost and finds fee,
>
> BUT I want to set it so that a $600 project will generate a 10% charge for
> first $100, 8% charge for next $399 and a 5% charge for the remaining $99.
>
> How do I do it?
>
> Many Thanks!!