Adjust %'s across cells

[Abe]

Hi all,

I have 3 cells and have a total of 100% and want to enter in a % into one
cell say 40%, from there that would leave me with 60%. IM trying to figure
out how to change one cell and then have the 100% decrease so that I dont
ever go over the 100% for my range of cells. This way if on the second cell
I enter 40% then I only can put in 20% on the last cell.

Hope that makes sense.

Thanks!

 

[Fred Smith]

I would set up a helper cell, using the formula:

=1-sum(a1:a3)

Adjust the range to suit. It will decrease as you enter data in your three
cells.

Regards,
Fred.

 

[Pete_UK]

Suppose you want to put values in A1 and A2 - you can apply Data |
Validation to those cells to ensure that values larger than 1 (or
100%) are not entered. Then in A3 you could have this formula:

=100%-A1-A2

Of course, if A1 and A2 were set to 60% each, then A3 would show -20%.

Hope this helps.

Pete