Redistribution of remaining percentages in excel

Question

I'm having a hard time getting some excel formulas correct. The goal is to redistribute any "lost" Change in Stake (negative values) as positive additions to other rows (the calculation is based mainly on Inactive Hours and each stackholder's original Stake

In this example, the "period" is 5 days and Row 1 controls 28.15% of the stake. so

STAKE PER DAY = 28.15% / 5

I calculate what percentage is lost during 25 hours of inactivity:

(25/24 hours) x 5.63% = ~5.86%

In this case Row 1 loses 5.86% percent for their inactivity, while all other members with lesser inactive hours than 25, get that 5.86% distributed amongst them based on their original stake. So Row 2 gets 21.93% of 5.86%, and so forth.

Stak

This formula was making my head spin last night:

  =IF(B25<>"",IF(D25="YES",IF([INACTIVE HOURS] > 0, -[@[INACTIVE HOURS]]/24*[@[STAKE PER DAY]], [@[ STAKE]]/($K$40+ SUMPRODUCT([[ STAKE]],[@[INACTIVE HOURS]]/24*[STAKE PER DAY], 0))),0),"")

Stayed up real late trying to crack it but the closest I got to getting the totals in Change In Stake to sum to 0.00% (I get 0.16%) and Final Adjusted Stake to sum to 100% (I get 99.84%).

There's also more to this problem. Consider there can also be multiple rows with different values for inactive hours.

For example, the row with 28.15% had 25 inactive hours, Row with 15.71% had 15 inactive hours and Row with 12.44% had 10 inactive hours.

In that case, only 10 of those hours will be fully distributed away from all 3 of these parties, and redistributed to the parties who had 0 inactive hours. However, the stakeholder with 12.44% was only inactive for 10 hours, as opposed to the stakeholder 28.15% who was inactive for 25 hours.

Therefore, stakeholder 12.44% will lose and full redistribute 10 hours worth but will also gain some back from the stakeholders with 15 and 25 hours of inactivity, during which stakeholder 12.44% was active. Likewise, the 15 hours stackholder is entitled to a proportionate share from the stackholder who was inactive for 10 hours more than they were.

Can excel formulas handle this type of calculation? What would it look like?

Answer 1

I think your partial solution is complicating the underlying problem so I would propose a different method. Reading between the lines the issue is to reduce/increase each stakeholders share by the inactive/active hours.

Calculate an equivalent number of hours from the total hours (share * 5 days * 24 hrs), subtract off the inactive hours, re-base the share on the new lower total. See the image below.