Excel formula question

January 16, 2007 2:06 PM

Excel question: How to define a range so that it always adds up to a constant value.

I have range of cells that are the same right now, and they add up to 100. I would like to have it so that if I change one value, the others adjusted equally so that the sum continues to be 100:

7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857

100

Any ideas? I hope this makes sense.

posted by corpse to computers & internet (20 comments total)

I'm not sure it's possible to do using only those cells. But here's a way to do it if you have the flexibility to use a second column. Let's say A1-A14 are cells where you indicate any values you'd like to have fixed (leaving the cell blank if you want the value of that particular entry to be one of the variable ones). Now, in B1, do

=IF(ISBLANK(A1),(100-SUM($A$1:$A$14))/COUNTIF($A$1:$A$14,""),A1)

and copy that to B2 through B14. B1 through B14 will have the values you want.
posted by DevilsAdvocate at 2:24 PM on January 16, 2007

This doesn't make sense. You just want to divide 100 by the number of cels. Why do you need to populate each cel with the same value?
posted by mzurer at 2:29 PM on January 16, 2007

I think the idea is that he changes the bottom one to 8, and the rest go to 6 something. I have no idea how to do that, but hopefully that is an accurate clarification.
posted by cschneid at 2:35 PM on January 16, 2007

Each cell doesn't have to be the same value, they just have to adjust if I change one value, example:

20
20
20
20
20

= 100

then if I change one of the 20's the other values should change as well, so that it still adds up to 100:

22
19.5
19.5
19.5
19.5

=100
posted by corpse at 2:38 PM on January 16, 2007

Or rather, what exactly do you want to see happen in the other cells? Can you show us an example using, say, four cells? If you "adjust" one, do you want the rest to all have equal values?
posted by mzurer at 2:40 PM on January 16, 2007

Not sure if you can do this as if you change a cell you are deleting the formula in it, so if you then change another cell the cell you changed first won't do anything. Also, setting up a formula that checks other cells to see what value it should output won't work if the other cells contain formulae that reference the cell that the first formula is in (i.e. it creates a circular reference).
posted by EndsOfInvention at 2:42 PM on January 16, 2007

And you need to be able to arbitrarily change any cell from a formula to a value? Is the range fixed or arbitrary?
posted by mzurer at 2:43 PM on January 16, 2007

Are you trying to make a sudoku solver?
posted by mzurer at 2:44 PM on January 16, 2007

Per this earlier thread, it looks like it's not possible to use a single cell in Excel for both input and output without using Visual Basic.
posted by DevilsAdvocate at 2:50 PM on January 16, 2007

I thought about it a bit more and Devils Advocate's formula will kind of do what you want. Any cells you change manually will never change, but all the remaining blank cells will automatically filled with 100/(the number of blank cells remaining).
posted by EndsOfInvention at 2:51 PM on January 16, 2007

This would seem to require turning on Circular References and Iterations.

Say you start like this:

 | A | B |
1| 5
2| 5
3| 5
4| 5
5|20 <- cell a5 is the sum of A1 to A4

Then you create a formula in cell B1 which is 20 minus 5:

 

 | A  | B  |
1| 5  | 15 |<- cell B1 is A5 - A4
2| 5
3| 5
4| 5
5|20 

And then you change cells A1, A2, and A3 all to be B1/3 ... Excel refuses to do it. Until you turn on Calculation and a certain number of iterations.

So now if you change cell A4 to something else:

 

 | A  
1| 5  
2| 5
3| 5
4|11 <-- this number has changed
5|20 

Excel re-calculates possible values of the other cells so that the total still adds up:

 

 | A  
1| 3  
2| 3
3| 3
4|11 
5|20 

posted by AmbroseChapel at 2:59 PM on January 16, 2007

Having read the clarification, my solution may not fit because it only allows you to change one of the cells, not any of them.
posted by AmbroseChapel at 3:03 PM on January 16, 2007

You can't do this with only Excel formulas. To avoid an endless loop, the process for determining the value of each cell requires the value of at least one cell to remain independent of the formula. You want that one cell to be the cell most recently edited, but Excel formulas have no means of determining which cell was most recently edited. So you either need to establish in advance which cell(s) you'll be editing and which cell(s) should have values determined by formula, or use a programming language that can figure out which cell was most recently edited.
posted by scottreynen at 4:06 PM on January 16, 2007

This is a little less elegant, but if you're only changing the one value in the column, you could simply have each entry in the column be

=( 100 - ($[Cell Number you are directly inputting] ) / (#of cells - 1)

I don't see this being terribly useful for anything, but it satisfies the requirement.
posted by Andrew Brinton at 4:29 PM on January 16, 2007

It might help if you told us what you're trying to do. My first thought is that this would be much easier in a real programming language, even visual basic inside excel. If you're just trying to solve a particular problem, and excel was the first thing you turned to, then it might make sense to turn to something else. If you're trying to do this in excel for some particular reason, then obviously that's not a proper solution.

So... what's the deal, can you tell us what you're after?
posted by RustyBrooks at 4:57 PM on January 16, 2007

  | A

1 | 20

2 | =($A$6-$A$1)/(ROWS($A$1:$A$5)-1)

3 | =($A$6-$A$1)/(ROWS($A$1:$A$5)-1)

4 | =($A$6-$A$1)/(ROWS($A$1:$A$5)-1)

5 | =($A$6-$A$1)/(ROWS($A$1:$A$5)-1)

6 | 100

This solution only works if you know which cell is the input cell. In this case, it is A1.
posted by sequential at 12:30 AM on January 17, 2007

Seconding scottreynen. If you want to do this using formulas, then you have to have one cell that can't accept input, since there's no way for a formula to "intercept" something that's typed in its cell -- the value will simply overwrite the formula and eventually you won't have any formulas left.

Using VBA (which has the advantage of being self-contained within the workbook itself. Here's how I'd do it, assuming that the values are to be in cells A1:A20:

From within Excel, click Alt-F11 to bring up the VBA editor. Right-click on the sheet in which your target range of values resides, and select "View Code." In the blank window on the right side, enter the following code:

Dim bRunning As Boolean
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
nTyped = Target.Value
If bRunning = False Then bRunning = True Else Exit Sub
' Modify this formula as you see fit.
nOther = (200 - nTyped) / 19
For Each vCell In ActiveSheet.Range("A1:A20").Cells
'MsgBox (vCell.Address)
If vCell.Address <> Target.Address Then
' Next line is necessary to prevent an endless loop
If vCell.Value = nOther Then Exit Sub
vCell.Value = nOther
End If
Next vCell
bRunning = False
End Sub

This assumes that your range to be modified is in cells A1:A20 (line ~7), and that your "target sum" is 200 (line ~6) and that there's 20 cells in your target range (also line ~6). Modify it as you see fit.

Note the bRunning variable declared outside the scope of the subroutine. This is to prevent an endless loop from occurring when you change one cell manually, which changes another cell automatically, which triggers the change event again, which changes the cells again, which triggers the change event again, etc.
posted by Doofus Magoo at 4:04 AM on January 17, 2007

Actually, the previous code didn't account for changes occuring outside the target range, which should be ignored. Replace it with:

Dim bRunning As Boolean
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row < 1 or target.row> 20 Or Target.Column < 1 or target.column> 1 Then Exit Sub
nTyped = Target.Value
If bRunning = False Then bRunning = True Else Exit Sub
' Modify this formula as you see fit.
nOther = (200 - nTyped) / 19
For Each vCell In ActiveSheet.Range("A1:A20").Cells
If vCell.Address <> Target.Address Then
vCell.Value = nOther
End If
Next vCell
bRunning = False
End Sub

There's probably an easier way to do that third line (which basically says "If the cell that change isn't in rows 1-20 and column 1, then don't do anything", but this will work.
posted by Doofus Magoo at 4:15 AM on January 17, 2007

Er... what is supposed to happen if you change more than one cell? e.g., start with:

20
20
20
20
20

Then change to:

28
18
18
18
18

Then change cell 3:

??
??
28
??
??

I can think of an infinite number of possibilities, of which 4 seem sort of sane:

28 | 18 | 25.5 | 24.585
15 | 18 | 15.5 | 15.805
28 | 28 | 28.0 | 28.000
15 | 18 | 15.5 | 15.805
15 | 18 | 15.5 | 15.805

(That's 'keep entered values, fill out the rest', 'reset all other values', 'steal an equal amount from the other cells', and 'steal a proportionate amount from the other cells'.)

Possibility 1 has been covered, 2 is easy to get from 1 (just manually delete the previous entry). I suspect that the last one isn't possible in Excel without resorting to writing an extension of some sort.
posted by reventlov at 10:59 AM on January 17, 2007

You can get reventlov's options 3 and 4 if you're willing to do an extra step after each number. For example, to do option 3 ("steal an equal amount from the other cells"), use four columns, like so: let column A be the initial value; column B is the new value, for the one you want fixed, and the rest of column B is blank; column C will be an intermediate column, and column D will be the final value. I assume for the sake of this example there's 20 rows.

For C1, do:

=IF(ISBLANK(B1),A1,"")

and copy to C2 through C20

For D1, do:

=IF(ISBLANK(B1),A1+(100-SUM($B$1:$C$20))/COUNTIF($B$1:$B$20,""),B1)

and copy to D2 through D20.

Now, after each time you set a new fixed value in column B (or setting 2 or more in column B, if you want all of those to be set to that fixed value and the rest to have the same amount added or subtracted), do a Copy of D1-D20, and paste it into A1-A20, using Paste Special - Values. Then blank column B before entering the next fixed number. You can easily record a macro to do this part so it's done in a single keystroke if you like.

For reventlov's option 4, do the same as above, except the formula for column D, which (for D1) would be:

=IF(ISBLANK(B1),A1+(100-SUM($B$1:$C$20))*C1/SUM($C$1:$C$20),B1)
posted by DevilsAdvocate at 1:20 PM on January 17, 2007