1. Registered User
Join Date
Feb 2004
Posts
7

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 !!!

2. Registered 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 !!!

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....

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•