The Sum of A1 + A2 + A3 + A4 = 52

[hiker95]

Team,

As I enter integers into range A1:A4, say 5 into cell A1 the remaining cells A2:A4 would populate with intergers where the sum of the range would total 52.

If A1 = 5, and I enter 15 into A4, cells A2 and A3 would populate with integers, where the sum of the range would total 52.

I do not have a clue where to begin.

Thanks for reading this post.

I hope someone has a solution.

Thanks in advance.

 

[hiker95]

Thanks everone for your great help (your formulae, and VBA code).


I was helping someone at Excel Forum, and their requirements have changed (still waiting for thier reply to finalize everything):
' For anedelis at
' http://www.excelforum.com/excel-gen...sum-of-4-cells-to-always-the-same-amount.html

I have 4 cells, say A1, A2, A3, A4, feeding into a cell, say A5 that needs to always equal 52.

If I change the value in any of the 4 cells (A1:A4), I want the other 3 cells to adjust accordingly so that A5 still equals 52.


With shawnhet's original formulae, I was able to come up with a Worksheet_Change Event that works correctly most of the time, and can handle integers, doubles, and negative numbers.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 08/06/2011
' http://www.mrexcel.com/forum/showthread.php?t=569930
' For anedelis at 
' http://www.excelforum.com/excel-general/787180-need-help-locking-the-sum-of-4-cells-to-always-the-same-amount.html
If Intersect(Target, Range("A1:A4")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Select Case Target
    Case Range("A1")
      Range("A2").Formula = "=INT(RAND()*(52-A1))"
      Range("A3").Formula = "=INT(RAND()*(52-A1-A2))"
      Range("A4").Formula = "=52-A3-A2-A1"
    Case Range("A2")
      Range("A1").Formula = "=INT(RAND()*(52-A2))"
      Range("A3").Formula = "=INT(RAND()*(52-A1-A2))"
      Range("A4").Formula = "=52-A3-A2-A1"
    Case Range("A3")
      Range("A1").Formula = "=INT(RAND()*(52-A3))"
      Range("A2").Formula = "=INT(RAND()*(52-A1-A3))"
      Range("A4").Formula = "=52-A3-A2-A1"
    Case Me.Range("A4")
      Range("A1").Formula = "=INT(RAND()*(52-A4))"
      Range("A2").Formula = "=INT(RAND()*(52-A1-A4))"
      Range("A3").Formula = "=52-A3-A2-A1"
  End Select
  With Range("A2:A4")
    .Value = .Value
  End With
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

 

[shg]

That works (obviously), but it gives a very different distribution of the numbers totalling the residual, with a knee shape that favors large numbers.

 

[hiker95]

shg,

Yes, the Worksheet_Change Event does work, but it has its downfalls.

Your code works great, and I can see a lot of uses for it.

It does handle negative numbers. This was not an original requirement.

It also only accepts a number less than 50, but this is also good thing. This was not an original requirement.

But, it does not work with a double ( 1.1 ). This was not an original requirement.

 

[shg]

It was delierately constrained to integers.

Change

        adCal = aiRandLen(dTot:=iTot - rInt.Value2, _
                          nNum:=Range(sRng).Cells.Count - 1, _
                          dMin:=iMin, _
                          iSig:=[COLOR=red]0[/COLOR])

to

        adCal = aiRandLen(dTot:=iTot - rInt.Value2, _
                          nNum:=Range(sRng).Cells.Count - 1, _
                          dMin:=iMin, _
                          iSig:=[COLOR=red]2[/COLOR])

for example, to return hundredths, or omit the last argument for full-precision numbers.

iTot and iMin can be likewise changed to Doubles.

 

[hiker95]

shg,

Wow!!!!!

You are soooooo good at what you do.

Thanks again, and again,...