Percentage with three variables

[Superstar31]

Help!!

CC Care $ Trends 2006 - Additions.Revised.xls
	A	B	C	D	E	F	G
1		Allocation
2		1	2	3	4	5	6
3	Whole
4	a	50%	50%	50%	20%	20%	20%
5	b	50%	50%	50%	40%	30%	20%
6	c	50%	50%	50%	40%	30%	20%
7	d	10%	5%	0%	0%	0%	0%
8	e	100%	100%	100%	100%	100%	100%
9	f	80%	65%	60%	60%	60%	60%
10	g	100%	100%	100%	100%	100%	100%
11	h	100%	100%	100%	100%	100%	100%
12	i	0%	0%	0%	0%	0%	0%
13	j	100%	100%	100%	100%	100%	100%
14	k	50%	50%	50%	40%	30%	20%
15	l	50%	50%	50%	40%	30%	20%
16	m	100%	100%	100%	100%	100%	100%
17	n	50%	50%	50%	40%	30%	20%
18	o	100%	50%	50%	40%	30%	20%
19	p	100%	50%	50%	40%	30%	20%
20	q	100%	50%	50%	40%	30%	20%
21	r	0%	0%	0%	0%	0%	0%
22
Center Allocations

I'm trying to have all the columns total to 1 row total 100% and if I change one it changes the rest

so A1 = 25%
then I want A2 to equal 25%
and A3 to equal 50%

The more i think about it the more impossible it sounds.. any help??

 

[RalphA]

For your example, one could set A2 to =A1, and A3 to =100-A1-A2. As long as A1 <= 50, this will work.

 

[PaddyD]

your problem is under-specified.

- you give a 3 cell example, but your sheet has 6 columns - what's actually going on

- you give no indication on how the allocations are to be distributed

- fyi you can't have a formula & data entry in the same cell without resourting to vba

 

[Superstar31]

For your example, one could set A2 to =A1, and A3 to =100-A1-A2. As long as A1 <= 50, this will work.

Hmm that might work, but I can't say a1 will always be less than 50

 

[Superstar31]

your problem is under-specified.

- you give a 3 cell example, but your sheet has 6 columns - what's actually going on</quote>
2 sets of 3.. meaning only 3 cells are tied together sometimes it only 2 cells, but I'm only concerned about the 3 cell once

<quote>
- you give no indication on how the allocations are to be distributed
</quote>
There is no set allocation, thats why I need a forumla, because this moonth a certain amount goes towards one thing, and next month it might be changed

<quote>
- fyi you can't have a formula & data entry in the same cell without resourting to vba

Ummmm, sure why not meaning if its hard to do, then we can skip it. but yea this does seem impossible to me the more I write about it

 

[RalphA]

To put it bluntly:

In mathematics and logic, you can only have one dependent variable at a time. If you have more than one, say two, then there is an infinite number of possible solutions. For a simple example:

What values of x and y satisfy the equality, x + 2*y = 5. For positive integers only (two more conditions), these are the solutions:
x...y
0...no solution
1...2
2...no solution
3...1
4...no solution
5...0

If we allow negative integers, we get an infinite number of solutions.

So, your original statement, for three variables, with only one stated condition, has an infinite number of solutions, and, therefore, makes no sense, mathematically.

 

[Superstar31]

To put it bluntly:

In mathematics and logic, you can only have one dependent variable at a time. If you have more than one, say two, then there is an infinite number of possible solutions. For a simple example:

What values of x and y satisfy the equality, x + 2*y = 5. For positive integers only (two more conditions), these are the solutions:
x...y
0...no solution
1...2
2...no solution
3...1
4...no solution
5...0

If we allow negative integers, we get an infinite number of solutions.

So, your original statement, for three variables, with only one stated condition, has an infinite number of solutions, and, therefore, makes no sense, mathematically.

Yea thats what I kinda figured, but I had to ask since you guys are pro's at this stuff