Variable Percentage Rate

**janschepens** · 07-27-2011, 11:17 PM

Hello, all.

Here's the situation:

$0 - $180,000 investors get 75%
$180,000 - $450,000 investors get 50%
$450,000 - $900,000 investors get 0%
$900,000+ investors get 30%

Based on this link, I was able to come up with this formula, where D8 is gross revenue:

=SUMPRODUCT(--(D8>{0;180000;450000;900000}), (D8-{0;180000;450000;900000}), {0.75;-0.25;-0.75;0.3})

For example, if you have $250,000 gross revenue, investors receive $170,000.

Where this all breaks down is after $450,000. Any ideas?

All help will be greatly appreciated.

Thanks much.

**shg** · 07-28-2011, 12:27 AM

At a glance, it looks like it should be

=SUMPRODUCT(--(D8>{0;18;45;90}*10000), (D8-{0;18;40;90}*10000), {0.75;-0.25;-0.5;0.3})

... but it's a weird schedule.

**janschepens** · 07-29-2011, 02:48 PM

Hey, shg.

Thanks a lot for the help. That seems to be working better now, but, when I put in 500,000 for gross revenue, the investor's net revenue figure shows up incorrectly as 245,000--it should be 270,000.

When I enter exactly 450,000 for gross revenue, it totals the investor's net revenue accurately at 270,000.

Any further thoughts?

Thank ya's.