Results 1 to 2 of 2
Thread: complex formula !

032904, 04:47 #1Registered User
 Join Date
 Feb 2004
 Posts
 7
Unanswered: complex formula !
i want to create a formula to do the following thing:
from a total, find in a number list which the sum can approximatively match with this total.
example:
the total is 50
the number list is 24  12  5  16  48  75, each number is in a raw, with another cell containing text data
the sum which match as good as possible with the total is 24+12+16=52
>it's the best combination in the number list which reaches approximatively 50 (i.e 48+5=53 is a good one too but not the best)
so i want the formula to copy/paste the raws containing 24, 12, 16 and the text cells in another place, when i type 50 in a particular cell in the table.
i think if someone find the solution, he may be proud !!!
many thanks in advance

040204, 15:37 #2Registered User
 Join Date
 Feb 2004
 Posts
 84
Re: complex formula !
Originally posted by webcargo
i want to create a formula to do the following thing:
from a total, find in a number list which the sum can approximatively match with this total.
example:
the total is 50
the number list is 24  12  5  16  48  75, each number is in a raw, with another cell containing text data
the sum which match as good as possible with the total is 24+12+16=52
>it's the best combination in the number list which reaches approximatively 50 (i.e 48+5=53 is a good one too but not the best)
so i want the formula to copy/paste the raws containing 24, 12, 16 and the text cells in another place, when i type 50 in a particular cell in the table.
i think if someone find the solution, he may be proud !!!
many thanks in advance
Okay, this is a weird one. I don't understand the purpose of this exercise, but I can't stand the challedge. They way I would approach the problem is with a loop and related values.
First I would collect the target number in a field. Then I would create a loop that creates a related record with a 'number field'. I would use the Random function to do a setfield between 0 and 'target number * Close Factor'Sum(related records).
If SUM of related record less than (Target number * Close Factor) then exit loop
Otherwise, repeat loop.
This would create a set of numbers that equal with X%, with X being the Close factor.
With a little work, I can see this application turning into a blackjack game. If you had a relationship with all your cards, you could use the same loop and a random function, except the random function would be a 'card number' from the 'card database'.
Instead of the loop quiting after the value reached a certain point you would need to use the show message command to let the user choose whther he wnats another card. the dealer hand would be completely controled by the loop with no interaction required.
Hope this helps....