Percentage with three variables

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
Help!!
CC Care $ Trends 2006 - Additions.Revised.xls
ABCDEFG
1Allocation
2123456
3Whole
4a50%50%50%20%20%20%
5b50%50%50%40%30%20%
6c50%50%50%40%30%20%
7d10%5%0%0%0%0%
8e100%100%100%100%100%100%
9f80%65%60%60%60%60%
10g100%100%100%100%100%100%
11h100%100%100%100%100%100%
12i0%0%0%0%0%0%
13j100%100%100%100%100%100%
14k50%50%50%40%30%20%
15l50%50%50%40%30%20%
16m100%100%100%100%100%100%
17n50%50%50%40%30%20%
18o100%50%50%40%30%20%
19p100%50%50%40%30%20%
20q100%50%50%40%30%20%
21r0%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??
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
For your example, one could set A2 to =A1, and A3 to =100-A1-A2. As long as A1 <= 50, this will work.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Active Member
Joined
Nov 10, 2005
Messages
496
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

Active Member
Joined
Nov 10, 2005
Messages
496

ADVERTISEMENT

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

Well-known Member
Joined
May 14, 2003
Messages
3,829
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

Active Member
Joined
Nov 10, 2005
Messages
496
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
 

Watch MrExcel Video

Forum statistics

Threads
1,111,596
Messages
5,541,150
Members
410,543
Latest member
ExcelGlenn
Top