Calculation with variables - part 2

Hello to all,

A month ago I had a cuestion regarding a bonus system which I had to implement in my company. Thanks to simon.hurby I was able to prepare it. Since that time my boss developed a little bit the bonus system and now I have another question and I will be very glad if someone is able to help me with it. There it goes:

Lets say that in my office I have six employees. Every year my boss will allocate a certain amount of money to distribute to the colleagues. In order to be able to distribute the ammount to my people, based on their contribution to the office results, I will have to evaluate each one of them based on two parameters: sales focus and client focus. For every parameter the scoring will be from 1 to 5 (five being the maximum score). Thanks to my Simon I was able to prepare the table. Now it goes more difficult. The moment I evaluate my colleagues, based on those two parameters, my boss will also prepare a personal evaluation to every colleague, again scoring from 1 to 5. The desire of my boss is that when a colleague is being scored from him with 1 or 2 this colleague wont get a bonus at all. If the score is 3, 4, 5 - the colleague will take the bonus. My problem with Excel is that my boss wants that if he evaluates a colleague with 1 or 2, the amount of the bonus this colleague was supposed to get, should be redistributed to the other colleagues. This is one of the problems.

The second problem is that we have a rule in our company that the year bonus cant exceed more that half of the yearly sallary. So if the bonus exceeds this amount - it has to be round up to 6 salaries.

And here is where I'm stuck. I will be very gratefull if someone is able to help me on this. I attach a template in excel.

Thank you very much in advance

Hi. I calculated requirements seperately but if you want it can be summerized into one cell

Hi,
Thank you very much. I think that it will work also like that, but could you show me how it would look like in one cell. I have also one more question. Because my boss really wants to redistribute all the bonus fund (without any money left) would it be possible a case, when we see that someone will recieve more than 6 sallaries and we round it up to 6 sallaries, to redistribute the money that is left to the other employees?

Thank you very much in advance

Hi
I summerized all in one cell here is formula
=IF(ROUND(IF(H3>2,I3,0)/SUMPRODUCT(($H$3:$H$8>2)*$I$3:$I$8)*SUMPRODUCT(($H$3:$H$8<=2)*$I$3:$I$8)+IF(H3>2,I3,0),0)>D3*6,D3*6,ROUND(IF(H3>2,I3,0)/SUMPRODUCT(($H$3:$H$8>2)*$I$3:$I$8)*SUMPRODUCT(($H$3:$H$8<=2)*$I$3:$I$8)+IF(H3>2,I3,0),0))

And if its greater than 6 months salary it haighlights to red;
=ROUND(IF(H3>2,I3,0)/SUMPRODUCT(($H$3:$H$8>2)*$I$3:$I$8)*SUMPRODUCT(($H$3:$H$8<=2)*$I$3:$I$8)+IF(H3>2,I3,0),0)>$D3*6

Do nopt forget to star if its done

Hi,

Thank you very much for the help. One last thing I would like to ask. Is it possible, if in the calculation we have someone whose sallary has to be reduced to 6 months salaries, the money that is left from him to be redistributed to the others (so that all 40 000 are distributed). Thanks a lot one more time

P.S I would gladly star you, but because I am not so used with this forum - can you tell me from where