If and Countif Formula not Working

[danbl]

Several weeks ago, with the help of several, I got a working formula for the distribution of funds.
As a reminder the goal is funds to be distributed based on full and half partners. The total for distribution obviously needs to equal the total distributed. When there is a half partner, the remaining half partner share needs to be distributed to all in a manner which results in those funds being distribute in a ratio or percentage that ends totaling the half share. (D15 = D22 in the file attached)

As you can see with the existing number of partners the formula works. but if you add a partner, full or half (in yellow area), total for distribution and total distributed do not equal each other. I have checked the ranges for the formulas and do not see a reason for this. What have I missed???

Current formula is:
IF($C25="",0,IF(COUNTIF($C$25:$C$42,"h")=0,D$14,IF($C25="",0,IF($C25="h",D$15+D$20/COUNTIF($C$25:$C$42,"h"),IF(C$25="f",D$14+D$19/COUNTIF($C$25:$C$42,"f"),0)))))
Thanks

[Pepe Le Mokko]

Title adapted as per forum rules

[danbl]

note change

[danbl]

Does revision comply with rules?

[Melvosh]

Hopefully your title change is sufficient.

From what I can see, adding any number of Full partners works (for column D; the other columns need the ranges adjusted). Once you start adding Half partners, the issue arises. I believe it has to do with the way you're calculating shares value. You're dividing the amount for distribution by the total number of people, regardless of share. This gives you a full share value (in theory), which you then cut in half for the half share value. This ends up leaving money on the table, because the rest of the calculations are based on the assumption that there will always be a 1 half share left over.

I think you'd be better off dividing the amount for distribution by the total number of half shares. This would give you a half share value, which you could then double to get the full share value. This will always distribute all of the money, and will not require a calculation to distribute a remaining half share, because there won't be one.

Here's an example:
You have $100 to distribute between 3 full shares and 2 half shares. Your method would calculate the full share at $20 ($100/5), and the half share at $10 ($20/2). If you add those up, you get $80 (3*20 + 2*10). That leaves $20 left over, which is a full share, not a half share.

What I'm suggesting would work this way: Calculate the half share at $12.50 ($100/((3*2)+2)). Calculate the full share at $25 ($12.50*2). If you add those up, you get $100. There's no leftover to worry about.

[shg]

Perhaps you'll be more successful in convincing him than I was: https://www.excelforum.com/excel-for...ml#post5092941

[Melvosh]

Hah, I thought this looked familiar. I remember typing up something similar last time, and then realizing you had already posted your recommendation.

Always calculate based on the value of the smallest share, and then multiply up. This I learned from Nathan Lowell's fantastic Trader's Tale series. (Available as a free podcast for anyone interested. Hope this doesn't count as advertising or anything!)

[danbl]

Melvosh ….

thanks for the reply and suggestions. It makes sense, I just need to spend some time working on changing the other calculations.

[danbl]

Melvosh

Attached is the data with your suggested formula. This works with the current but again when you add a Full or Half partner the totals do not equal. What did I miss??

[danbl]

My apologies ……………….. the problem appears to be, I did not expand the total ranges properly!!

Thank you to all.