Is this possible with Formula - Increase Matrix

[Baiju G Nath]

Hi Excel Experts,

This is my first query in this lounge. Hoping to get a positive response. Any solution will help me immensely

I am working on a increase % matrix for my employee. I have attached an excel that would help.

I have the employees spread in 5 bands RPO1,RPO2,RPO3,RPO4,RPO5
The employee fall with a specific salary range
10.00% - 84.99%
85.00% - 94.99%
95.00% - 104.99%
105.00% - 114.99%
115.00%+

- I have the % spread of employee in this range entered manually i.e. column K
Column I has the desired % spread of employee in the 5 bands mentioned above

Is it possible through a formula in the cells shaded GREY Column E-G to arrive at the Target %'s in Column L. At the moment im doing it manually to somehow get it close to the Target

Hope to have a positive response. This is save a lot of my time/effort to arrive at the Target manually

Regards
Baiju

[HansV]

Welcome to Eileen's Lounge!

You can't do this with formulas since this problem probably has multiple solutions. You can use the Solver add-in for this. In the attached version, I have calculated the sum of the squares of the differences between the numbers in column L and the target numbers in column N. (Taking the square eliminates the distinction between positive and negative differences). The Solver can search for the lowest value of this sum, under the condition ("restraint") that the cells marked in grey are between 0% and 100%.

For Matrix Formula.xls