Help with if statement

**rabs0721** · 06-11-2013, 06:47 PM

Hello, I need help to develop a file to track profit on jobs. There is royalty charged on the amount of the job, and there are breaks for certain revenue levels.
The breakdown is like this:
Revenue: $0-$80000,$80001-$125000,$125001-$225000,$225000+
Royalty: 17%, 11%, 10%, 5%
The royalty break only lowers for the revenue after the break value.
What I'm looking to do is have the cell add up each jobs cost and categorize to the proper royalty level and spit out value.

**Tony Valko** · 06-11-2013, 07:27 PM

Maybe this...

Create this 2 column table:

Please Login or Register  to view this content.

Assume that table is in the range D2:E5.

A2 = some number

This formula entered in B2:

=LOOKUP(A2,D2:E5)

Format as Percentage

**djapigo** · 06-11-2013, 07:35 PM

If your number is in A1... put this formula in B1

=MIN(A1,80000)*0.17+MAX((MIN(A1,125000)-80000),0)*0.11+MAX((MIN(A1,225000)-125000),0)*0.1+MAX((MIN(A1,10000000)-225000),0)*0.05