Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add column for percentage o total | Excel Discussion (Misc queries) | |||
Percentage total may not exceed 100 | Excel Discussion (Misc queries) | |||
Total Revenue Percentage | Excel Discussion (Misc queries) | |||
Percentage Discount Total | Excel Discussion (Misc queries) | |||
a number as a percentage out of a total | Excel Worksheet Functions |