generating a set of random numbers to total a set value

Y

yaldan

New Member
Joined
Jan 15, 2007
Messages
1
Is it possible to generate a set of random numbers between say 50 - 150 whereby the sum of those random numbers totals a value in the spreadsheet which is determined by two other values in the spreadsheet?

Eg: value 1 = 10, value 2 = 300. The difference = 290. Now generate 6 or so random numbers between 50 - 150 that come to a total of 290.

If so could you helpme with formula or script.

Regards

Yaldan
 
Sort by date Sort by votes
I came across this post and wonder why a macro is not deployed. A very simple macro to produce 10 random numbers between 15 and 60 that sum to 400 produced 10 such sets in milliseconds. I allowed duplicate numbers in the set, but easy to specify 10 different numbers
 
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
I'd be curious to see your code. I thought about writing a macro, but the underlying algorithm would be largely the same as in the formulas. I think it would suffer the same problem as the formulas. Meiko87 PM'ed me with this example: (Target: 276124 Min:400 Max:2000 Nº Random:362). Large enough sets (362) would have the problem of later numbers in the sequence being forced to exact values in order to achieve the desired sum.

But I'm sure there's a better algorithm. If you have one, I'd like to see it. Thanks!
 
Upvote 0
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 24/02/2018 by bob
'


'
Dim mynum(20)
myrow = 19
10 mysum = 0
For j = 1 To 10
20 temp = Int(Rnd * 100)
If temp < 15 Then GoTo 20
If temp > 60 Then GoTo 20
mynum(j) = temp
mysum = mysum + temp
Next j
If mysum <> 400 Then mysum = 0: GoTo 10
myrow = myrow + 1
For k = 1 To 10
Cells(myrow, k) = mynum(k)
Next k
If myrow > 30 Then GoTo 100 Else GoTo 10
100 End Sub
 
Upvote 0
oldbrewer: That code seems correct, and probably works quickly enough for a range of 10 numbers, but I suspect for a target of 276124 and 362 numbers, it would take far too long.

shg: That seems reasonable, but how would you account for the minimum and maximum sizes of each piece? Say you want six numbers that sum to 20, each number being 1-5 in size. If the first 4 cuts are at the 2,3,4,5 positions, that leaves a length of 15 with one cut left, which would mean one piece would be larger than 5.
 
Upvote 0
how would you account for the minimum and maximum sizes of each piece?
Click to expand...
Min size is easy; subtract k * min from the total, and add the min to each resulting piece:

Code: 
Function RandLen(dTot As Double, _
                 Optional dMin As Double = 0#, _
                 Optional ByVal iSig As Long = 0, _
                 Optional bVolatile As Boolean = False) As Double()
  ' shg 2011, 2013

  ' UDF wrapper for adRandLen

  Dim adTmp()       As Double
  Dim adOut()       As Double
  Dim iRow          As Long
  Dim nRow          As Long
  Dim iCol          As Long
  Dim nCol          As Long

  If bVolatile Then Application.Volatile

  nRow = Application.Caller.Rows.Count
  nCol = Application.Caller.Columns.Count

  adTmp = adRandLen(dTot, nRow * nCol, dMin, iSig)
  ReDim adOut(1 To nRow, 1 To nCol)

  For iRow = 1 To nRow
    For iCol = 1 To nCol
      adOut(iRow, iCol) = adTmp((iRow - 1) * nCol + iCol)
    Next iCol
  Next iRow

  RandLen = adOut
End Function

Function adRandLen(ByVal dTot As Double, _
                   nOut As Long, _
                   Optional ByVal dMin As Double = 0#, _
                   Optional ByVal iSig As Long = 307) As Double()
  ' shg 2011

  ' Applies string-cutting to return an array of nOut
  ' numbers totalling dTot, with each in the range
  '    dMin <= number <= Round(dTot, iSig) - nOut * round(dMin, iSig)

  ' Each number is rounded to iSig decimals

  Dim iOut          As Long     ' index to iOut
  Dim jOut          As Long     ' sort insertion point
  Dim dRnd          As Double   ' random number
  Dim dSig          As Double   ' decimal significance (e.g., 1, 0.01, ...)
  Dim adOut()       As Double   ' output array
  
  dSig = 10# ^ -iSig
  
  dTot = Round(dTot * dSig) / dSig
  dMin = Round(dMin * dSig) / dSig

  ReDim adOut(1 To nOut)
  If nOut < 1 Or dTot < nOut * dMin Then Exit Function

  With New Collection
    .Add Item:=0#
    .Add Item:=dTot - nOut * dMin

    ' create the cuts
    For iOut = 1 To nOut - 1
      dRnd = Int(Rnd() * ((dTot - nOut * dMin) / dSig)) * dSig

      ' insertion-sort the cut
      For jOut = .Count To 1 Step -1
        If .Item(jOut) <= dRnd Then
          .Add Item:=dRnd, After:=jOut
          Exit For
        End If
      Next jOut
    Next iOut

    ' measure the lengths
    For iOut = 1 To nOut
      adOut(iOut) = .Item(iOut + 1) - .Item(iOut) + dMin
    Next iOut
  End With

  adRandLen = adOut
End Function

Distinct values (which only makes sense for whole numbers) is also straightforward:

Code: 
Function RandLenDist(iTot As Long, _
                     Optional iMin As Long = 0#, _
                     Optional bVolatile As Boolean = False) As Long()
  ' shg 2018

  ' UDF wrapper for aiRandLen

  Dim aiTmp()       As Long
  Dim aiOut()       As Long
  Dim iRow          As Long
  Dim nRow          As Long
  Dim iCol          As Long
  Dim nCol          As Long

  If bVolatile Then Application.Volatile

  nRow = Application.Caller.Rows.Count
  nCol = Application.Caller.Columns.Count

  aiTmp = aiRandLen(iTot, nRow * nCol, iMin)
  ReDim aiOut(1 To nRow, 1 To nCol)

  For iRow = 1 To nRow
    For iCol = 1 To nCol
      aiOut(iRow, iCol) = aiTmp((iRow - 1) * nCol + iCol)
    Next iCol
  Next iRow

  RandLenDist = aiOut
End Function

Function aiRandLen(ByVal iTot As Long, _
                   k As Long, _
                   Optional ByVal iMin As Long = 0&) As Long()
  ' shg 2018

  ' Applies string-cutting to return an array of k distinct Longs >= iMin,
  ' totalling iTot
  
  ' Partitioning n into k distinct positive integers is equivalent to
  ' partitioning n-k(k-1)/2 into k positive integers, some possibly equal,
  ' and then adding {k-1, k-2, ..., 0} respectively to each element.

  Dim aiOut()       As Long         ' output array
  Dim iOut          As Long         ' index to aiOut
  Dim jOut          As Long         ' sort insertion point
  Dim iCut          As Long         ' random cut point
  
  If k < 1& Then Exit Function
  If iMin < 0& Then iMin = 0&
  iTot = iTot - k * iMin            ' to accommodate min value
  iTot = iTot - k * (k - 1&) \ 2&   ' to accommodate distinct values
  If iTot < 0& Then Exit Function

  ReDim aiOut(1 To k)

  With New Collection
    .Add Item:=0&
    .Add Item:=iTot
    
    ' create the cuts
    For iOut = 1& To k - 1&
      iCut = Int(Rnd() * (iTot + 1&))

      ' insertion-sort the cut
      For jOut = .Count To 1& Step -1&
        If .Item(jOut) <= iCut Then
          .Add Item:=iCut, After:=jOut
          Exit For
        End If
      Next jOut
    Next iOut

    ' measure the distances between cuts and add the min to each
    For iOut = 1& To k
      aiOut(iOut) = .Item(iOut + 1&) - .Item(iOut) + iMin
    Next iOut
  End With
  
  InsertionSort aiOut     ' sort by length (required for the next step)
  For iOut = 1& To k      ' add the stepped lengths to make the values distinct
    aiOut(iOut) = aiOut(iOut) + iOut - 1&
  Next iOut
  FYShuffle1D aiOut         ' re-randomize order

  aiRandLen = aiOut
End Function

Function InsertionSort(ByRef av As Variant) As Boolean
  ' Sorts 1D or 2D array av in situ; if 2D, one dimension must be 1

  Dim iLB           As Long
  Dim iUB           As Long
  Dim LB            As Long
  Dim i             As Long
  Dim j             As Long
  Dim v             As Variant

  Select Case NumDim(av)
    Case 0, Is > 2
      ' not an array, or > 2 dimensions

    Case 1
      iLB = LBound(av)
      iUB = UBound(av)

      For i = iLB + 1 To iUB
        v = av(i)

        For j = i - 1 To iLB Step -1
          If av(j) <= v Then Exit For
          av(j + 1) = av(j)
        Next j

        av(j + 1) = v
      Next i
      InsertionSort = True

    Case 2
      If UBound(av, 1) - LBound(av, 1) > 0 Then
        If UBound(av, 2) - LBound(av, 2) > 0 Then Exit Function

        iLB = LBound(av, 1)
        iUB = UBound(av, 1)
        LB = LBound(av, 2)

        For i = iLB + 1 To iUB
          v = av(i, LB)

          For j = i - 1 To iLB Step -1
            If av(j, LB) <= v Then Exit For
            av(j + 1, LB) = av(j, LB)
          Next j

          av(j + 1, LB) = v
        Next i
        InsertionSort = True

      Else
        iLB = LBound(av, 2)
        iUB = UBound(av, 2)
        LB = LBound(av, 1)

        For i = iLB + 1 To iUB
          v = av(LB, i)

          For j = i - 1 To iLB Step -1
            If av(LB, j) <= v Then Exit For
            av(LB, j + 1) = av(LB, j)
          Next j

          av(LB, j + 1) = v
        Next i
        InsertionSort = True
      End If
  End Select
End Function

Sub FYShuffle1D(av As Variant)
  ' shg 2015

  ' In-situ Fisher-Yates shuffle of 1D array av
  ' VBA only

  Dim iLB           As Long
  Dim iTop          As Long
  Dim vTmp          As Variant
  Dim iRnd          As Long

  iLB = LBound(av)
  iTop = UBound(av) - iLB + 1

  Do While iTop
    iRnd = Int(Rnd * iTop)
    iTop = iTop - 1
    vTmp = av(iTop + iLB)
    av(iTop + iLB) = av(iRnd + iLB)
    av(iRnd + iLB) = vTmp
  Loop
End Sub

Max size -- dunno, other than trial and error.
 
Last edited:
Upvote 0
shg said:
Max size -- dunno, other than trial and error.
Click to expand...
Clarification: I can think of a few ways to do that other than trial and error, but none that don't totally hose the distribution of large and small numbers from their natural distribution, which is the point of using string cutting.
 
Upvote 0
easy to build into my macro 3if the first N numbers total less than "X" abort and re-run

will try it with bigger numbers later....
 
Upvote 0
Eric W said:
OK, I created some formulas that will always generate a valid set of numbers, with no repeated F9. But as I warned you, the formulas are more complicated.

Excel 2010
ABCD
TargetMinMaxNumber of random numbers

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A4[/TH]
[TD="align: left"]=RANDBETWEEN(MAX($B$2,$A$2-(($D$2-ROWS($A$4:$A4))*$C$2)),MIN($C$2,$A$2-(($D$2-ROWS($A$4:$A4))*$B$2)))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A5[/TH]
[TD="align: left"]=IF(ROW()=$D$2+3,$A$2-SUM($A$4:$A4),IF(ROW()>$D$2+3,"",RANDBETWEEN(MAX($B$2,$A$2-(SUM($A$4:$A4)+($D$2-ROWS($A$4:$A5))*$C$2)),MIN($C$2,$A$2-(SUM($A$4:$A4)+($D$2-ROWS($A$4:$A5))*$B$2)))))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=IF(OR($C$2*D$2<$A$2,$B$2*$D$2>$A$2),"Can't be done!","")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B10[/TH]
[TD="align: left"]=SUM(A4:A20)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Here's your 2-number example:
Excel 2010
ABCD
TargetMinMaxNumber of random numbers

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]25[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



From your example in post #13 :
Excel 2010
ABCD
TargetMinMaxNumber of random numbers

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



Since your numbers 3 and 4 are fixed, subtract their total (50) from the overall total (85) to get the value (35) that numbers 1 and 2 must sum to.
Click to expand...

Hi MrExcel i got some problem when i use your formula. My office version is Office 2013

My error is

0E6yzL.png


Please help me.
 
Upvote 0
Eric W said:
OK, I created some formulas that will always generate a valid set of numbers, with no repeated F9. But as I warned you, the formulas are more complicated.

Excel 2010
ABCD
TargetMinMaxNumber of random numbers

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A4[/TH]
[TD="align: left"]=RANDBETWEEN(MAX($B$2,$A$2-(($D$2-ROWS($A$4:$A4))*$C$2)),MIN($C$2,$A$2-(($D$2-ROWS($A$4:$A4))*$B$2)))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A5[/TH]
[TD="align: left"]=IF(ROW()=$D$2+3,$A$2-SUM($A$4:$A4),IF(ROW()>$D$2+3,"",RANDBETWEEN(MAX($B$2,$A$2-(SUM($A$4:$A4)+($D$2-ROWS($A$4:$A5))*$C$2)),MIN($C$2,$A$2-(SUM($A$4:$A4)+($D$2-ROWS($A$4:$A5))*$B$2)))))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=IF(OR($C$2*D$2<$A$2,$B$2*$D$2>$A$2),"Can't be done!","")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B10[/TH]
[TD="align: left"]=SUM(A4:A20)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Here's your 2-number example:
Excel 2010
ABCD
TargetMinMaxNumber of random numbers

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]25[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



From your example in post #13 :
Excel 2010
ABCD
TargetMinMaxNumber of random numbers

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



Since your numbers 3 and 4 are fixed, subtract their total (50) from the overall total (85) to get the value (35) that numbers 1 and 2 must sum to.
Click to expand...

Can you upload file ? I can not work formula
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Random Number Generator That Can Exclude Numbers
Replies
2
Views
506
FormulasOnly
F
N
  • Solved
Can I generate a random order but with two inputted numbers?
Replies
5
Views
738
Peter_SSs
Peter_SSs
T
  • Solved
Random number generator button
Replies
3
Views
1K
Georgiboy
Georgiboy
J
  • Solved
Calculate sums based on values
Replies
3
Views
305
NateSC
NateSC
D
  • Question Question
Determine Multiple Ranges from Column Values
Replies
3
Views
202
awoohaw
awoohaw

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top