Results 1 to 14 of 14

20080824, 21:20 #1
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Change values to achiever at the Target % spend (Excel 2003)
Hi Excel Experts,
I am working on a excel sheet that was provided to me with the Target Budget % which i need to spread amoungst the Population. I have given the leaverage % that was used as the multiplier to arrive at the target spend across the population. However i need to change some of the values in this and yet arrive at the Target %.
Can someone help me with the same and also explain the logic so that going forward if we need to change any further i should be able to do it.
I have attached the file for your reference and also highlighted the changes that i require.
Column A has the Leverage (Not sure how to arrive at this %). Cell F1 has the Target %. Population spread is on Column C.
I need to make changes to cells highlighted in Col B in Green and yet arrive at the Target % on D7.
Thanks
Baiju

20080824, 21:31 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Change values to achiever at the Target % spend (Excel 2003)
If you can only change the leverage for one population group (the 10% group), you cannot keep the total (the target %) the same. If you can change the leverage for all population groups, there is an infinite number of solutions.
(In your sample workbook, the leverages are in column B instead of in column A, and the target spread is in column D instead of in column C.)

20080824, 21:52 #3
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Change values to achiever at the Target % spend (Excel 2003)
Hi Hans,
Thanks for your response. Can you let me know the solutions by changing the leverages so that the 10% group is at the desired % that i am looking at.
Regards
Baiju

20080824, 21:57 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Change values to achiever at the Target % spend (Excel 2003)
There isn't a single "recipe". You can change around the other leverages in many ways to achieve your goal. If you want a specific type of solution, YOU will have to tell us what you want.

20080824, 22:04 #5
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Change values to achiever at the Target % spend (Excel 2003)
Hi Hans,
I received the leverages earlier and the % Inc were arrived based on the same. Now i would like to change the leverages so that i still get the Target % same but Change % Inc is such a way that the 10% Leverage get the new % that have been mentioned in Col F.
Hope this explanation helps.
Regards
Baiju

20080824, 22:06 #6
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Change values to achiever at the Target % spend (Excel 2003)
Hi Hans,
This could also mean that the % Inc for the others also will change.
Regards
Baiju

20080824, 22:10 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Change values to achiever at the Target % spend (Excel 2003)
As I mentioned before, there isn't a single solution or set of solutions. You can change one of the other leverages, or some of them, or all of them. Without restrictions, I can't simply say "do this".

20080824, 22:13 #8
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Change values to achiever at the Target % spend (Excel 2003)
HI Hans,
Can you let me know how i could do that by changing the leverages of 65% & 10% Population.
Thanks
Baiju

20080824, 22:29 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Change values to achiever at the Target % spend (Excel 2003)
In cell B5, enter the formula
=F5/F1
This makes the value in C5 equal to the desired value.
In B4, enter the formula
=(F1D2D3D5D6)/(A4*F1)
This will make the value in D7 equal to the desired target.

20080824, 23:01 #10
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Change values to achiever at the Target % spend (Excel 2003)
Thanks Hans,
I was able to get the desired results.
Can you let me know what would be the formula if i had to change all the leverages keep the desired % in B5. Asking so that incase I need to rework on all the leverages i have the formula readily available.
Regards
Baiju

20080824, 23:15 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Change values to achiever at the Target % spend (Excel 2003)
Without some kind of extra restriction it is not possible to answer that question  you can create an infinite number of combinations that will yield the desired target.

20080824, 23:24 #12
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Change values to achiever at the Target % spend (Excel 2003)
Hi Hans,
I am referring back to one of the earlier questions you had asked. " You can change one of the other leverages, or some of them, or all of them. Without restrictions, I can't simply say "do this".
What would be the formulas for each of these restrictions i.e. by changing some of them or all of them? I am only trying to find the logic to do this.
Hope this helps.
Thanks
Baiju

20080824, 23:37 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Change values to achiever at the Target % spend (Excel 2003)
If you allow two or more leverages to change, there is an infinite number of solutions.
Therefore it is not possible to give you "the" formulas  there is an infinite number of possibilities.
It is as if you ask "I want to find two numbers that add up to 12.345. Can you give me the formulas for the two numbers?"
The numbers could be 0 and 12.345, or 1.1 and 11.245, or 14 and 1.655, or 212.345 and 200, etc. etc.
It is not possible to give "the" solution.
However, if you specify an extra condition "The first number must be twice as large as the second number", it becomes possible to give a single solution.
So if you specify extra conditions that the leverages must satisfy, it might be possible to come up with a single solution. I cannot provide these conditions since I don't know what is required.

20080824, 23:59 #14
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Change values to achiever at the Target % spend (Excel 2003)
Hi Hans,
Now i understand what you meant when you said specify the conditions. Im not sure of the logic or reasoning behind the existing leverages. I will try and figure out the same and then come back to you with specific conditions.
Thanks for the formula that you provided as a fix for the existing query. It helped me fix the immediate problem.
Regars
Baiju