Formula help needed...again.

[kozykristi]

I have a client who does fundraisers for their sports assocations. The company gets 50% of all sales. My client gets the other 50%.

My clients' players have an annual $75 dues they must pay. Through the fund raiser, they can earn their dues. The client will give them all of their 50% profit up to their $75. After that, they will split the profit with them to put in their "account" to use for purchases such as uniforms, trip fees, etc.

The current formula I have for their earnings towards dues is this:
=MIN(C9*50%,75)...column C is the sales column.

The formula I have for their "account" is this:
=SUM(C11*.5)-75*.5

This formula isn't working. Guess i don't know what I'm doing.

Example: Chris, the player, sells $200 in product. The client earns $100, $75 of which they credit to Chris for his dues. $25 is leftover, of which the client will give 50%, or $12.50, to Chris for his "Account".

What should my formula be for the "on account" column to make it reflect $12.50?

HELP!

 

[Joe4]

Two things wrong with your formula:

1. Why are you using SUM? You only use SUM when you are adding up a range of cells, not on a calculation on a single cell.

2. The precedence of mathematical computations is multiplication before subtraction. So you need to enclose part of your formula in parentheses if you want to subtract the 75 BEFORE multiplying by 50 percent.

So, I think you may be looking for something like this:
=((C11*0.5)-75)*0.5

 

[kozykristi]

Joe4, Answer to #1: because I don't know enough about Excel to NOT use Sum in this scenario. ;o)

Thanks for the formula. It does work. However can we give it a condition that if they haven't earned anything on their account yet, it doesn't show negative? For example, if a player sells $75, the formula you gave shows a -$18.75. I would want it to show zerio until such time they actually start earning money for their account.

 

[Joe4]

Just like you used MIN in an earlier formula, there is also a MAX formula, i.e.
=MAX(((C11*0.5)-75)*0.5,0)

 

[kozykristi]

Joe4 for President!!! ;o) That did it. Thanks so much, not only for the formula, but for the education!!

Some people's mathematical brains just blow me away! Thanks again ~