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

[stanleydgromjr]

Team,

As I enter integers into range A1:A4, say 5 into cell A1 the remaining cells A2:A4 would populate with intergers that 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.

Have a great day, and weekend,
Stan

[Admin]

Hi Stan,

Not sure, but are you looking this (http://www.tushar-mehta.com/excel/templates/match_values/) one ?

[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

[Admin]

Hi Stan,

Thanks. :cheers: