Multiples....

[Cam]

Hi, I am trying to set up a cell to look up the least no. of give
values to produce a given amount

So say in column A I have the values 60, 50 & 5 and the value I a
looking for is 100. How would I get excel to return a number 2 (ie 5
x 2) in column B next to the value of 50? At the moment I am gettin
60x1 & 5x8 to produce the result of 100.

Thanks, Ca

 

[Guest]

We need a few more "rules".........are 65,50, and 5 your only numbers to work
with? What result do you want if there is no combination for an exact match?

Vaya con Dios,
Chuck, CABGx3

 

[Cam]

Thanks CLR,

The list of numbers would be variable (depending on user input) bu
would always contain the number 1 so the list might be 60, 50, 47, 32
21, 8, 5, 2, 1. This way the result can always be achieved so for 10
the result = 50 (2)but for 99 the result = 47 (2) & 5 (1). The number
available need to be read from the column.

Hope that helps, please let me know if you need any more info.

Cheers, Ca

 

[Dana DeLouis]

...for 99 the result = 47 (2) & 5 (1).

Hi. Just to mention: For 99, there's another option:
{2,47,50},{5,47,47}

A few numbers like 63 have 4 options:
{1,2,60},{5,8,50},{8,8,47},{21,21,21}

 

[Cam]

Hi Dana,

I guess I am looking for the answer with the most repetition....so i
your example the desired answer for 63 would be 21 x 3 and for 99 i
would be 47 x 2 + 5 x 1...... Any ideas how to do it ?

Cheers

 

[Cam]

......any takers?......

 

[CLR]

I know you said the list was varible, but do you know the minimum and
maximum of the range? And, are any decimals imvolved, or just whole
numbers? Will you always be looking for the end result of 100, or is that
varible also?

Vaya con Dios,
Chuck, CABGx3

 

[Cam]

Hi Clr,

Sorry for the delay, for some reason I am not getting repl
notification emails?

The list is variable and the mins & maxs are 1 & 100, all whol
numbers. The result needed is also a variable whole number.

Hope that helps, thanks again :

 

[CLR]

ONe more thing.......what is the maximum quantity of numbers in the list,
100?, or some smaller quantity?

Vaya con Dios,
Chuck, CABGx3

 

[Cam]

Hi, 20 would be the maximum.

Cheers. Ca

 

[CLR]

All-righty then......
To reiterate:
We are looking for a means to determine the smallest quantity of numbers,
(including multiple numbers) from a list of up to 20 numbers that will equal
a variable target value. All numbers and target numbers are whole numbers,
no decimals or fractions are involved. The numbers themselves range from 1
to 100 and the list will always contain a 1, so in fact "every" target value
may be attained by a worse-case (actually BEST case) multiple of "1 x
itself"..........walla!, the answer could always be "1 x the target number",
unless of course the actual target number itself is also within the
list.........did I miss something?

Vaya con Dios,
Chuck, CABGx3

 

[Cam]

Spot on......

Thanks for helping.

Ca

 

[Cam]

Any news clr?

Thanks.:

 

[Guest]

It's a Bear Cam......gives me a headache. I've got a workbook that offers
four solutions, none of which is bug-free, but you're welcome to it if you
email me your request.

croberts at tampabay dot rr dot com

Vaya con Dios,
Chuck, CABGx3

 

[Cam]

Hi CLR,

Thanks for looking at this - sorry about the headache..... If it make
your head hurt it would make mine drop off !!!!

Have a good weekend.

Cheers,

Cam