changing one percentage, but total must be 100
 

I am trying to create a macro/function that will allow me to change a
percentage in a row, adjust the remaining percentages to still keep my total
100%. I am lost and need help.

Thanks!

changing one percentage, but total must be 100
 

If you add 5 to one of them, subtract 5 from one of the others...
Maybe you were looking for something a bit more complicated though.

Tim



"Laurie" wrote in message
...
I am trying to create a macro/function that will allow me to change a
percentage in a row, adjust the remaining percentages to still keep my
total
100%. I am lost and need help.

Thanks!

changing one percentage, but total must be 100
 

Where are the other numbers which need to be modified, and how should they be
modified.
--
HTH...

Jim Thomlinson


"Laurie" wrote:

I am trying to create a macro/function that will allow me to change a
percentage in a row, adjust the remaining percentages to still keep my total
100%. I am lost and need help.

Thanks!

changing one percentage, but total must be 100
 

Yes, its more complicated than that. I am setting up an application it has
to be user friendly and must allow for all possibilities. If the user wants
to see how changing one percentage may affect other items in the application,
how can I keep my total 100% if the user choses to change any cell to a
different percentage?

"Tim Williams" wrote:

If you add 5 to one of them, subtract 5 from one of the others...
Maybe you were looking for something a bit more complicated though.

Tim



"Laurie" wrote in message
...
I am trying to create a macro/function that will allow me to change a
percentage in a row, adjust the remaining percentages to still keep my
total
100%. I am lost and need help.

Thanks!

changing one percentage, but total must be 100
 

If there is some dependency between the different items which make up the
100%, then the nature of the dependency would dictate how other items change
when one is adjusted.

In other words, to get a definitive answer you'll need to explain exactly
how you would expect changing one item to "affect other items"...

Tim.


"Laurie" wrote in message
...
Yes, its more complicated than that. I am setting up an application it
has
to be user friendly and must allow for all possibilities. If the user
wants
to see how changing one percentage may affect other items in the
application,
how can I keep my total 100% if the user choses to change any cell to a
different percentage?

"Tim Williams" wrote:

If you add 5 to one of them, subtract 5 from one of the others...
Maybe you were looking for something a bit more complicated though.

Tim



"Laurie" wrote in message
...
I am trying to create a macro/function that will allow me to change a
percentage in a row, adjust the remaining percentages to still keep my
total
100%. I am lost and need help.

Thanks!

changing one percentage, but total must be 100
 

Let us assume that you have four variables in cells A1,A2,A3,A4. As
understand your problem, you want to find out how rest three variable
would be affected if any one of them is changed. In my opinion thi
means that we need to maintain the respective ratio of rest thre
variables same as it was earlier. eg if A1=25, A2=24,A3=25 and A4=2
and we change A1 to say 62.5 A2, A3 and A4 must still maintain thei
earlier proportion (24:25:26) and the new values would be thus A2=12
A3=12.5 and A4=13.

I can suggest procedure

Sub Percent()
TestRow = ActiveCell.Row
TestCol = ActiveCell.Column
NewValue = Cells(TestRow, TestCol).Value
If TestRow = 1 And TestCol = 1 Then
Denominator = [B1] + [C1] + [D1]
[B1] = (100 - NewValue) * [B1] / Denominator
[C1] = (100 - NewValue) * [C1] / Denominator
[D1] = (100 - NewValue) * [D1] / Denominator
End If

If TestRow = 1 And TestCol = 2 Then
Denominator = [A1] + [C1] + [D1]
[A1] = (100 - NewValue) * [A1] / Denominator
[C1] = (100 - NewValue) * [C1] / Denominator
[D1] = (100 - NewValue) * [D1] / Denominator
End If

If TestRow = 1 And TestCol = 3 Then
Denominator = [A1] + [B1] + [D1]
[A1] = (100 - NewValue) * [A1] / Denominator
[B1] = (100 - NewValue) * [B1] / Denominator
[D1] = (100 - NewValue) * [D1] / Denominator
End If

If TestRow = 1 And TestCol = 4 Then
Denominator = [A1] + [B1] + [C1]
[A1] = (100 - NewValue) * [A1] / Denominator
[B1] = (100 - NewValue) * [B1] / Denominator
[C1] = (100 - NewValue) * [C1] / Denominator
End If

End Sub

You will need initialising OnEntry Event procedure eg
Worksheets(1).OnEntry = "Percent" to execute our procedure "Percent
every time you change values in either A1,A2,A3 or A4. It wil
immediately update rest three cells. This could be nuisance. This wil
not allow you to fill up initial values as you want, as other cells ge
affected as soon as you enter any one cell. And since the cells ar
blank to start with , it might give divide by zero error when you ente
the first cell. May be you would want to execute the proc only after yo
click a button or something instead of other cells recalculating as soo
as you enter any one cell.

A V Veerkar

A V Veerka

--
avveerka
-----------------------------------------------------------------------
avveerkar's Profile: http://www.excelforum.com/member.php...fo&userid=3033
View this thread: http://www.excelforum.com/showthread.php?threadid=50857