Need some real help

[Fiore100]

Hi there team.

So I have a 1 issue that has got me... Please help

I have code that allows me to add a few values and divide them by the number of times someone works in a full week. but what i want to do now is after i have allocated the number of days each person works i want to deduct a percentage from that person and share the number left over with the rest of the other people.

the code so far
=SUMIFS(C10:I10,C14:I14,"1")

C10:I10 this is the value from the week
C14:I14 this is the number of days that someone has worked...

this is then split between a few people...

Now how to I deduct a % that is allocated to one person

eg

Total moneys made 1000 (this is total of C10:I10)

A B__________C___ D__E____F___G__H________I___________J
1 Name______Days________________Total___Percentage___ New total
2 ___________Mon Tues Wed Thus Fri
3 Jam ________1___1___1___1___1__250
4 Ham________1___1___1___1___1__250
5 Sam ________1___1___1___1___1__250
6 Pam ________1___1___1___1___1__250

So if G is the =sumif(C10:I10,B3:F3,'1') and so on
Then how do i add a percentage that will keep the total of 1000 but when i put in the % it will then divide the total and share the difference between the other people


Eg Jam gets 250 but gets 50% deducted and then the others get the difference.


its probably a lot bigger then i think, or maybe i've over complicating it... but some help would be really appreciated

 

[Dr. Demento]

I have A solution; not sure if it's THE solution.

I added two helper cells - The person being penalized (K3) and the amount (as a negative percentage; L3)

In the Percentage column (col H), I copied this formula to all cells in the column:
=IF($K$3=$A3,$L$3,ABS($L$3/(COUNTA($A$3:$A$6)-1)))

In the New Total column (col I), I use this formula:
=G3*(1+H3)

The New Total still equals the previous Total.

Does that help??