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
Is this possible with Formula - Increase Matrix
-
- NewLounger
- Posts: 1
- Joined: 23 Aug 2010, 16:17
Is this possible with Formula - Increase Matrix
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is this possible with Formula - Increase Matrix
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%.
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%.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans