Need formula help!

[kozykristi]

Here's the situation: I'm doing a project for a sports organization and the player's annual dues is $75. They're doing a fund raiser and the organizations earns 50% of everything they sell. They split that 50% with the player (50% of the 50%) until they player reaches their $75 dues. Once the reaches this "payout" level, the organization gives them 25% of the 50% to put in their "account" for uniforms, trips, etc.

How do I show this? For example, if the player sells $100, the organization gets $50. That player gets $25 towards their $75, so they'll have to pay $50 for their dues. Let's say the player sells $500. The organization gets $250. The player gets $75 towards their buyout, then earns 25% of the remaining $50, or $12.50.

Does this make sense? I've been using this formula: =SUM('1-50'!AN9*0.5-75)/2. But it's showing a -$37.50 in the field when no information has been inputted. needs to show the amount the player has earned.

Let me know if I need to explain better. HELP!

 

[stnkynts]

Maybe i am missing something but it doesn't appear as though your numbers are addding up.

Lets look at both examples:

Example 1: Player sells $100. Organization gets $50. $50 remaining. Player gets $25 towards dues. 50 - 25 = 25. What happens to the other $25.

Example 2: Player sells $500. Organization gets $250. $250 remaining. Player get $75 towards dues. $175 remaining. Player then earns 25% of remaining $50....wait what?!? What happened to the other $125?

In addition there needs to be some cell references. For example is the amount players sold in column Z?

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Player</td><td style="font-weight: bold;;">Sales</td><td style="font-weight: bold;;">Dues</td><td style="font-weight: bold;;">Account</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">John</td><td style="text-align: right;;"> $100.00 </td><td style="text-align: right;;"> $ 25.00 </td><td style="text-align: right;;"> $ - </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Joe</td><td style="text-align: right;;"> $300.00 </td><td style="text-align: right;;"> $ 75.00 </td><td style="text-align: right;;"> $ - </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Jack</td><td style="text-align: right;;"> $500.00 </td><td style="text-align: right;;"> $ 75.00 </td><td style="text-align: right;;"> $ 25.00 </td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=MIN(<font color="Blue">B2*25%,75</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=MAX(<font color="Blue">B2-300,0</font>)*12.5%</td></tr></tbody></table></td></tr></table><br />

 

[Sahak]

Hi kozykristi,

Spouse A1 = Employee sales, B1 = formula

 = IF(A1/4>75,(A1/4-75)/2,A1/4-75)

Example 1: A1 = 100 then B1 = -50 (Player have to pay)
Example 2: A1 = 500 then B1 = 25 (Player has balance)

 

[kozykristi]

AlphaFrog, I used your formula and VOILA! It works. Thank you SO MUCH! I've decided that I'm going to learn a LOT hanging out on this website. I know just enough about Excel to be dangerous. But yall on this forum? Yall are GENIUSES!!! Thanks for yall's help. (BTW, stnkynts, the other amounts you're missing is the cost the fundraising company keeps. They keep 50%, and give 50% to the organization, who then profit shares with the players.)

Thanks again yall ~ will be back again when I get stumped.

 

[AlphaFrog]

Hey AlphaFrog. The formula you gave me: =MAX(B2-300,0)*12.5%...

doesn't seem to be working. I thought it was, but when I did the calculation manually to check, the formula is giving them 50% of what's left from sales, past their $75 payout, for their account instead of 25%.

IE:
Sales for player X are $424. The organization gets 50% of sales for their fundraiser, or $212. The player gets 50% of the $212, up to $75. After the $75 dues is reached, they only get 25%. The above formula is giving me $15.50. But manual calculation is:
424/2 = 212
212/2 = 106
106-75 = 31
31*.25 = 7.75 (not 15.50)

Can you help me again?

Based on the how I understand your criteria, I think the red part of your manual calculation is wrong.

31 is already 50% of 50%. If you multiply it again by 25%, you are only giving them 6.25% of sales. (50%*50%*25% = 6.25%)

We know the 1st $300 of sales goes to dues. At $300 they have earned their full $75 dues. After $300 in sales, they should earn 25% of 50% (or 12.5% of sales past $300).

Do a calculation of $301. The 1st $300 is all for dues. What should they get for the next $1 after $300 ?

Correct:
$1 /2 = $0.50
$0.50 *25% = $0.125

Incorrect:
301 /2 = 150.50
150.50 /2 = 75.25
75.25 - 75 = 0.25
0.25 * 25% = .0625