Hi,

It would be a weird question, but i like to ask it with an example ...

21 22 23 24
23 24 25 26
27 28 29 30
21 21 21 21
25 26 27 28
20 20 20 20
22 22 22 22
26 27 28 29
25 25 25 25
26 27 28 29

in a template like this, from 10 rows and 4 columns, how can i see all
probabilities for the exact sum that is asked for ...

for example ...

21 -- -- --
-- 24 -- --
-- -- -- 30
-- -- -- 21
-- -- 28 --
20 -- -- --
-- -- 22 --
26 -- -- --
-- -- -- 25
-- 29 -- --
=x

-- -- -- 23
-- 24 -- --
-- -- 30 --
21 -- -- --
-- -- -- 28
22 -- -- --
-- -- 26 --
-- -- -- 25
-- 29 -- --
-- -- 30 --
=x

-- -- 22 --
-- 24 -- --
-- -- 30 --
-- -- 30 --
-- -- -- 28
22 -- -- --
30 -- -- --
-- -- -- 25
-- -- -- 23
-- -- 30 --
=x

as goes like this .......... i want to see all the results in this way
... as calculated in 10 colums and 1 row ...

i do not have any clue, how can i do this transaction ...

i need it for visual program's scripts ... for creative reasons ...

i would be so happy, if you can help me ...

yours turly ...


--
lynch44
------------------------------------------------------------------------
lynch44's Profile: http://www.excelforum.com/member.php...o&userid=33867
View this thread: http://www.excelforum.com/showthread...hreadid=536431

I did not understand your exemples...
Where do I get rhe exact sum that is asked for ?

Cheers,
--
AP


"lynch44" a écrit
dans le message de
...

Hi,

It would be a weird question, but i like to ask it with an example ...

21 22 23 24
23 24 25 26
27 28 29 30
21 21 21 21
25 26 27 28
20 20 20 20
22 22 22 22
26 27 28 29
25 25 25 25
26 27 28 29

in a template like this, from 10 rows and 4 columns, how can i see all
probabilities for the exact sum that is asked for ...

for example ...

21 -- -- --
-- 24 -- --
-- -- -- 30
-- -- -- 21
-- -- 28 --
20 -- -- --
-- -- 22 --
26 -- -- --
-- -- -- 25
-- 29 -- --
=x

-- -- -- 23
-- 24 -- --
-- -- 30 --
21 -- -- --
-- -- -- 28
22 -- -- --
-- -- 26 --
-- -- -- 25
-- 29 -- --
-- -- 30 --
=x

-- -- 22 --
-- 24 -- --
-- -- 30 --
-- -- 30 --
-- -- -- 28
22 -- -- --
30 -- -- --
-- -- -- 25
-- -- -- 23
-- -- 30 --
=x

as goes like this .......... i want to see all the results in this way
.. as calculated in 10 colums and 1 row ...

i do not have any clue, how can i do this transaction ...

i need it for visual program's scripts ... for creative reasons ...

i would be so happy, if you can help me ...

yours turly ...


--
lynch44
------------------------------------------------------------------------
lynch44's Profile:
http://www.excelforum.com/member.php...o&userid=33867
View this thread: http://www.excelforum.com/showthread...hreadid=536431

Exact sum is given by the user, me ...

Up above numbers are just examples ... aim is to find all probabilities
from x colums and y rows, like ...

21 22 23 24
23 24 25 26
27 28 29 30
21 21 21 21
25 26 27 28
20 20 20 20
22 22 22 22
26 27 28 29
25 25 25 25
26 27 28 29

as

21 -- -- --
-- 24 -- --
-- -- -- 30
-- -- -- 21
-- -- 28 --
20 -- -- --
-- -- 22 --
26 -- -- --
-- -- -- 25
-- 29 -- --
=x

-- -- -- 23
-- 24 -- --
-- -- 30 --
21 -- -- --
-- -- -- 28
22 -- -- --
-- -- 26 --
-- -- -- 25
-- 29 -- --
-- -- 30 --
=x

-- -- 22 --
-- 24 -- --
-- -- 30 --
-- -- 30 --
-- -- -- 28
22 -- -- --
30 -- -- --
-- -- -- 25
-- -- -- 23
-- -- 30 --
=x

so forth ...

is it clear?


--
lynch44
------------------------------------------------------------------------
lynch44's Profile: http://www.excelforum.com/member.php...o&userid=33867
View this thread: http://www.excelforum.com/showthread...hreadid=536431

<is it clear?

No.
What would x be in your example? In your 3rd table, why is 23 in the first row in the 4th posistion and not in the 3rd as in the
first table?

--
Kind regards,

Niek Otten

"lynch44" wrote in message
...
|
| Exact sum is given by the user, me ...
|
| Up above numbers are just examples ... aim is to find all probabilities
| from x colums and y rows, like ...
|
| 21 22 23 24
| 23 24 25 26
| 27 28 29 30
| 21 21 21 21
| 25 26 27 28
| 20 20 20 20
| 22 22 22 22
| 26 27 28 29
| 25 25 25 25
| 26 27 28 29
|
| as
|
| 21 -- -- --
| -- 24 -- --
| -- -- -- 30
| -- -- -- 21
| -- -- 28 --
| 20 -- -- --
| -- -- 22 --
| 26 -- -- --
| -- -- -- 25
| -- 29 -- --
| =x
|
| -- -- -- 23
| -- 24 -- --
| -- -- 30 --
| 21 -- -- --
| -- -- -- 28
| 22 -- -- --
| -- -- 26 --
| -- -- -- 25
| -- 29 -- --
| -- -- 30 --
| =x
|
| -- -- 22 --
| -- 24 -- --
| -- -- 30 --
| -- -- 30 --
| -- -- -- 28
| 22 -- -- --
| 30 -- -- --
| -- -- -- 25
| -- -- -- 23
| -- -- 30 --
| =x
|
| so forth ...
|
| is it clear?
|
|
| --
| lynch44
| ------------------------------------------------------------------------
| lynch44's Profile: http://www.excelforum.com/member.php...o&userid=33867
| View this thread: http://www.excelforum.com/showthread...hreadid=536431
|

i see that practice is the way ...

here is other simple examples ...

1 2 3
1 2 3
2 2 2
1 1 1
1 1 1
1 1 1
should be=5

so there is no answers in this example ... because all exceeds 5 ...

1 2 3
0 0 0
2 2 2
0 2 3
0 2 3
1 1 1
should be=5

- 2 -
0 0 0
2 2 2
0 - -
0 - -
1 1 1
=5

OR

1 2 3
0 0 0
2 2 2
0 2 3
0 2 3
1 1 1
should be=8

- - 3
0 0 0
2 2 2
- 2 -
0 - -
1 1 1
=8

1 - -
0 0 0
2 2 2
- 2 -
- 2 -
1 1 1
=8

- 2 -
0 0 0
2 2 2
- - 3
0 - -
1 1 1
=8

- 2 -
0 0 0
2 2 2
0 - -
- - 3
1 1 1
=8

so forth ...

i would like to see all probable vertical sums in this way ...

simply i need the formula ...

regards ...


--
lynch44
------------------------------------------------------------------------
lynch44's Profile: http://www.excelforum.com/member.php...o&userid=33867
View this thread: http://www.excelforum.com/showthread...hreadid=536431

1 2 3
0 0 0
2 2 2
0 2 3
0 2 3
1 1 1
should be=5

- 2 -
0 0 0
2 2 2
0 - -
0 - -
1 1 1
=5

What sums op to 5? I don't see it! Yes, the middle column, but that doesn't match with your other examples. I'm afraid you'll have
to explain in words what you're trying to solve!

--
Kind regards,

Niek Otten


"lynch44" wrote in message
...
|
| i see that practice is the way ...
|
| here is other simple examples ...
|
| 1 2 3
| 1 2 3
| 2 2 2
| 1 1 1
| 1 1 1
| 1 1 1
| should be=5
|
| so there is no answers in this example ... because all exceeds 5 ...
|
| 1 2 3
| 0 0 0
| 2 2 2
| 0 2 3
| 0 2 3
| 1 1 1
| should be=5
|
| - 2 -
| 0 0 0
| 2 2 2
| 0 - -
| 0 - -
| 1 1 1
| =5
|
| OR
|
| 1 2 3
| 0 0 0
| 2 2 2
| 0 2 3
| 0 2 3
| 1 1 1
| should be=8
|
| - - 3
| 0 0 0
| 2 2 2
| - 2 -
| 0 - -
| 1 1 1
| =8
|
| 1 - -
| 0 0 0
| 2 2 2
| - 2 -
| - 2 -
| 1 1 1
| =8
|
| - 2 -
| 0 0 0
| 2 2 2
| - - 3
| 0 - -
| 1 1 1
| =8
|
| - 2 -
| 0 0 0
| 2 2 2
| 0 - -
| - - 3
| 1 1 1
| =8
|
| so forth ...
|
| i would like to see all probable vertical sums in this way ...
|
| simply i need the formula ...
|
| regards ...
|
|
| --
| lynch44
| ------------------------------------------------------------------------
| lynch44's Profile: http://www.excelforum.com/member.php...o&userid=33867
| View this thread: http://www.excelforum.com/showthread...hreadid=536431
|

1 2 3
0 0 0
2 2 2
0 2 3
0 2 3
1 1 1
should be=5

- 2 -
0 0 0
2 2 2
0 - -
0 - -
1 1 1
=5

from all rows, one number must be picked and all columns should be used
...

rows like 0 0 0, 2 2 2, are written like this because they all have the
same number ...

from other rows like 0 2 3, just one number is being used ...

when vertically calculated, sum should be 5 ... and probabilities with
this way ...

regards and thanks ... it becomes really tiresome ...


--
lynch44
------------------------------------------------------------------------
lynch44's Profile: http://www.excelforum.com/member.php...o&userid=33867
View this thread: http://www.excelforum.com/showthread...hreadid=536431

Sorry. Incomprehensible.
Sum of 1st column is 3. 2nd column: 5. 3rd column: 3.
Why is what a good answer?

I suggest you try to describe the problem you're trying to solve, not the algorithms.

--
Kind regards,

Niek Otten

"lynch44" wrote in message
...
|
| 1 2 3
| 0 0 0
| 2 2 2
| 0 2 3
| 0 2 3
| 1 1 1
| should be=5
|
| - 2 -
| 0 0 0
| 2 2 2
| 0 - -
| 0 - -
| 1 1 1
| =5
|
| from all rows, one number must be picked and all columns should be used
| ..
|
| rows like 0 0 0, 2 2 2, are written like this because they all have the
| same number ...
|
| from other rows like 0 2 3, just one number is being used ...
|
| when vertically calculated, sum should be 5 ... and probabilities with
| this way ...
|
| regards and thanks ... it becomes really tiresome ...
|
|
| --
| lynch44
| ------------------------------------------------------------------------
| lynch44's Profile: http://www.excelforum.com/member.php...o&userid=33867
| View this thread: http://www.excelforum.com/showthread...hreadid=536431
|

"Sorry. Incomprehensible.
Sum of 1st column is 3. 2nd column: 5. 3rd column: 3.
Why is what a good answer?"

i see ... confusion comes from myself of course ...

okay ... let's write like this ...

| - 2 -
| 0 - - *
| 2 - - *
| 0 - -
| 0 - -
| 1 - - *
| =5

again the original
| 1 2 3
| 0 0 0
| 2 2 2
| 0 2 3
| 0 2 3
| 1 1 1
| should be=5

* - these rows have all the same numbers in themselves (you do not have
to count all these) ...

variety of numbers goes 0-99 ... so i need a formula that includes the
equation and appropriate calculation ...

again from the original one, all probable counts like above should be
calculated and displayed ...

regards and thanks for the attention ...


--
lynch44
------------------------------------------------------------------------
lynch44's Profile: http://www.excelforum.com/member.php...o&userid=33867
View this thread: http://www.excelforum.com/showthread...hreadid=536431

I don't have a complete solution for you (yet).
But maybe the stuff below gets you going.

--
Kind regards,

Niek Otten


Find numbers that add up to a specified sum.

Niek Otten

April 5, 2006



This type of application tends to be very resource-consuming. It is wise to test a solution first with a limited

set of data

One option is using Solver; I include an example given by MVP Peo Sjoblom. The other is a rather famous VBA Sub by Harlan Grove.
There seems to be one flaw: if the table is sorted ascending and the first n numbers sum up to the required value exactly, it will
miss that combination. I don't know if this has been corrected later.

Note the requirements for your settings documented in the code itself



Peo's solution:

==================================================

One way but you need the solver add-in installed (it comes with
excel/office,check under toolsadd-ins)
put the data set in let's say A2:A8, in B2:B8 put a set of ones {1,1,1 etc}
in the adjacent cells
in C2 put 8, in D2 put

=SUMPRODUCT(A2:A7,B2:B7)

select D2 and do toolssolver, set target cell $D$2 (should come up
automatically if selected)

Equal to a Value of 8, by changing cells $B$2:$B$7, click add under Subject
to the constraints of:
in Cell reference put

$B$2:$B$7

from dropdown select Bin, click OK and click Solve, Keep solver solution
and look at the table

2 1
4 0
5 0
6 1
9 0
13 0

there you can see that 4 ones have been replaced by zeros and the adjacent
cells to the 2 ones
total 8

--

Regards,

Peo Sjoblom

==================================================

Harlan's solution:


'Begin VBA Code

' By Harlan Grove

Sub findsums()
'This *REQUIRES* VBAProject references to
'Microsoft Scripting Runtime
'Microsoft VBScript Regular Expressions 1.0 or higher

Const TOL As Double = 0.000001 'modify as needed
Dim c As Variant

Dim j As Long, k As Long, n As Long, p As Boolean
Dim s As String, t As Double, u As Double
Dim v As Variant, x As Variant, y As Variant
Dim dc1 As New Dictionary, dc2 As New Dictionary
Dim dcn As Dictionary, dco As Dictionary
Dim re As New RegExp

re.Global = True
re.IgnoreCase = True

On Error Resume Next

Set x = Application.InputBox( _
Prompt:="Enter range of values:", _
Title:="findsums", _
Default:="", _
Type:=8 _
)

If x Is Nothing Then
Err.Clear
Exit Sub
End If

y = Application.InputBox( _
Prompt:="Enter target value:", _
Title:="findsums", _
Default:="", _
Type:=1 _
)

If VarType(y) = vbBoolean Then
Exit Sub
Else
t = y
End If

On Error GoTo 0

Set dco = dc1
Set dcn = dc2

Call recsoln

For Each y In x.Value2
If VarType(y) = vbDouble Then
If Abs(t - y) < TOL Then
recsoln "+" & Format(y)

ElseIf dco.Exists(y) Then
dco(y) = dco(y) + 1

ElseIf y < t - TOL Then
dco.Add Key:=y, Item:=1

c = CDec(c + 1)
Application.StatusBar = "[1] " & Format(c)

End If

End If
Next y

n = dco.Count

ReDim v(1 To n, 1 To 3)

For k = 1 To n
v(k, 1) = dco.Keys(k - 1)
v(k, 2) = dco.Items(k - 1)
Next k

qsortd v, 1, n

For k = n To 1 Step -1
v(k, 3) = v(k, 1) * v(k, 2) + v(IIf(k = n, n, k + 1), 3)
If v(k, 3) t Then dcn.Add Key:="+" & _
Format(v(k, 1)), Item:=v(k, 1)
Next k

On Error GoTo CleanUp
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

For k = 2 To n
dco.RemoveAll
swapo dco, dcn

For Each y In dco.Keys
p = False

For j = 1 To n
If v(j, 3) < t - dco(y) - TOL Then Exit For
x = v(j, 1)
s = "+" & Format(x)
If Right(y, Len(s)) = s Then p = True
If p Then
re.Pattern = "\" & s & "(?=(\+|$))"
If re.Execute(y).Count < v(j, 2) Then
u = dco(y) + x
If Abs(t - u) < TOL Then
recsoln y & s
ElseIf u < t - TOL Then
dcn.Add Key:=y & s, Item:=u
c = CDec(c + 1)
Application.StatusBar = "[" & Format(k) & "] " & _
Format(c)
End If
End If
End If
Next j
Next y

If dcn.Count = 0 Then Exit For
Next k

If (recsoln() = 0) Then _
MsgBox Prompt:="all combinations exhausted", _
Title:="No Solution"

CleanUp:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.StatusBar = False

End Sub

Private Function recsoln(Optional s As String)
Const OUTPUTWSN As String = "findsums solutions" 'modify to taste

Static r As Range
Dim ws As Worksheet

If s = "" And r Is Nothing Then
On Error Resume Next
Set ws = ActiveWorkbook.Worksheets(OUTPUTWSN)
If ws Is Nothing Then
Err.Clear
Application.ScreenUpdating = False
Set ws = ActiveSheet
Set r = Worksheets.Add.Range("A1")
r.Parent.Name = OUTPUTWSN
ws.Activate
Application.ScreenUpdating = False
Else
ws.Cells.Clear
Set r = ws.Range("A1")
End If
recsoln = 0
ElseIf s = "" Then
recsoln = r.Row - 1
Set r = Nothing
Else
r.Value = s
Set r = r.Offset(1, 0)
recsoln = r.Row - 1
End If
End Function

Private Sub qsortd(v As Variant, lft As Long, rgt As Long)
'ad hoc quicksort subroutine
'translated from Aho, Weinberger & Kernighan,
'"The Awk Programming Language", page 161

Dim j As Long, pvt As Long

If (lft = rgt) Then Exit Sub
swap2 v, lft, lft + Int((rgt - lft + 1) * Rnd)
pvt = lft
For j = lft + 1 To rgt
If v(j, 1) v(lft, 1) Then
pvt = pvt + 1
swap2 v, pvt, j
End If
Next j

swap2 v, lft, pvt

qsortd v, lft, pvt - 1
qsortd v, pvt + 1, rgt
End Sub

Private Sub swap2(v As Variant, i As Long, j As Long)
'modified version of the swap procedure from
'translated from Aho, Weinberger & Kernighan,
'"The Awk Programming Language", page 161

Dim t As Variant, k As Long

For k = LBound(v, 2) To UBound(v, 2)
t = v(i, k)
v(i, k) = v(j, k)
v(j, k) = t
Next k
End Sub

Private Sub swapo(a As Object, b As Object)
Dim t As Object

Set t = a
Set a = b
Set b = t
End Sub
'---- end VBA code ----



"lynch44" wrote in message
...
|
| "Sorry. Incomprehensible.
| Sum of 1st column is 3. 2nd column: 5. 3rd column: 3.
| Why is what a good answer?"
|
| i see ... confusion comes from myself of course ...
|
| okay ... let's write like this ...
|
|| - 2 -
|| 0 - - *
|| 2 - - *
|| 0 - -
|| 0 - -
|| 1 - - *
|| =5
|
| again the original
|| 1 2 3
|| 0 0 0
|| 2 2 2
|| 0 2 3
|| 0 2 3
|| 1 1 1
|| should be=5
|
| * - these rows have all the same numbers in themselves (you do not have
| to count all these) ...
|
| variety of numbers goes 0-99 ... so i need a formula that includes the
| equation and appropriate calculation ...
|
| again from the original one, all probable counts like above should be
| calculated and displayed ...
|
| regards and thanks for the attention ...
|
|
| --
| lynch44
| ------------------------------------------------------------------------
| lynch44's Profile: http://www.excelforum.com/member.php...o&userid=33867
| View this thread: http://www.excelforum.com/showthread...hreadid=536431
|