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

[stanleydgromjr]

Admin,

Thank you for the reply. The link information is one for my archives.

I was helping someone, and their requirements have changed (still waiting for thier reply to finalize everything):
I have 4 cells (A1, A2, A3, A4), feeding into a cell (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.


Another very interesting solution was from shawnhet at MrExcel.com (also, one for my archives) that worked for the original set of requirements where cell A1 was the only cell that was used for data entry:
' http://www.mrexcel.com/forum/showthread.php?t=569930
With their 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

Another very interesting solution was from repairman615 at MrExcel.com (also, one for my archives):
' http://www.mrexcel.com/forum/showthread.php?t=569930
' Sub FiftyTwo(), that worked for the original set of requirements where cell A1 was the only cell that was used for data entry.

Another very interesting solution was from shg, MrExcel MVP (also, one for my archives):
' shg, MrExcel MVP, 2011, 08/07/2011
' http://www.mrexcel.com/forum/showthread.php?t=569930
' This uses a string-cutting algorithm that results in the 'correct' distribution of numbers totalling a particular value.


Thanks, and have a great week,
Stan