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

 

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

 

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