Adjust %'s across cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Abe - 20 May 2008 20:23 GMT

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!

Reply to this Message

Fred Smith - 20 May 2008 22:32 GMT

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.

> Hi all,
>
[quoted text clipped - 9 lines]
>
> Thanks!

Reply to this Message

Pete_UK - 20 May 2008 22:36 GMT

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

> Hi all,
>
[quoted text clipped - 7 lines]
>
> Thanks!

Reply to this Message