[The Wolf]

K, I want to write an equation. I would love to make a little app/apple script for this, but I'm not sure how. Anyways, Excel would be fine for now. Here's what I need...

I need an equation that will calculate the following for me:

30 percent of X up to the first $250,000; plus
25 percent of X up to the next $250,000; plus
20 percent of X up to the next $500,000; plus
15 percent of X up to the next $250,000; plus
10 percent of anything else (i.e., above 1,250,000).


so for example, if X=1,500,000, then it would generate an answer of:
$ 300,000.00 (I think)

[turboSPE]

Originally posted by The Wolf:
K, I want to write an equation. I would love to make a little app/apple script for this, but I'm not sure how. Anyways, Excel would be fine for now. Here's what I need...

I need an equation that will calculate the following for me:

30 percent of X up to the first $250,000; plus
25 percent of X up to the next $250,000; plus
20 percent of X up to the next $500,000; plus
15 percent of X up to the next $250,000; plus
10 percent of anything else (i.e., above 1,250,000).


so for example, if X=1,500,000, then it would generate an answer of:
$ 300,000.00 (I think)

Why not make an "if" statement, a macro, or an array?

turboSPE

[Ω]

Try:

=IF(B1>1250000, (B1-1250000)*0.1+275000, IF(B1>1000000, (B1-1000000)*0.15+237500, IF(B1>500000, (B1-500000)*0.2+137500, IF(B1>250000, (B1-250000)*0.25+75000, B1*0.3))))

B1 is the value of "x"

I would personally go through and assign the "breaks" (1,250,000 etc) as cells, as well as the percentages. This will give it more flexibility when things change.

You will also notice that there is a plus 275000 in the first if statement. This value of 275000 equates to a running sum of the costs. I did not see the point in having massive if calculations when we can determine the cost at 1250000 and then add this to any value over that amount. This is the same for all price breaks.

I think it is correct......

[E's Lil Theorem]

All right, at first I was going to provide some guidance, but as I thought through it I just started writing the darn Excel equation. As you can see, it's not very pretty, but I think it's doing what you wanted it to. If not, you can use it as a starting point and modify it as you like. Anyway, here it is:

Commission Calculations

EDIT: Omega beat me to it (that's what happens when you start writing a post, walk away for 10 minutes and come back to hit reply), though my code is a bit more flexible.

[Ω]

Do I actually show up as an omega symbol? On all my machines I am a stupid ?.

I also had a fully working spreadsheet, but with no way of hosting it explaining it would have taken so long, that I "dumbed" it down instead.

[E's Lil Theorem]

Originally posted by _?_:
Do I actually show up as an omega symbol? On all my machines I am a stupid ?..

Heh, I saw your thread in the Feedback Forum asking to have your name changed. So, that's how I know you're "Omega."

[Ω]

Good memory, that was months ago!

[The Wolf]

Originally posted by E's Lil Theorem:
All right, at first I was going to provide some guidance, but as I thought through it I just started writing the darn Excel equation *snip*

That is awesome. Pretty enough!!!