Multiples....

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

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

Thanks, Cam

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" wrote:

>
> Hi, I am trying to set up a cell to look up the least no. of given
> values to produce a given amount
>
> So say in column A I have the values 60, 50 & 5 and the value I am
> looking for is 100. How would I get excel to return a number 2 (ie 50
> x 2) in column B next to the value of 50? At the moment I am getting
> 60x1 & 5x8 to produce the result of 100.
>
> Thanks, Cam
>
>
> --
> Cam
> ------------------------------------------------------------------------
> Cam's Profile: http://www.excelforum.com/member.php...o&userid=27836
> View this thread: http://www.excelforum.com/showthread...hreadid=538118
>
>

Thanks CLR,

The list of numbers would be variable (depending on user input) but 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 100 the result = 50 (2)but for 99 the result = 47 (2) & 5 (1). The numbers available need to be read from the column.

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

Cheers, Cam

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

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Cam" <Cam.2782va_1146644401.1858@excelforum-nospam.com> wrote in message
news:Cam.2782va_1146644401.1858@excelforum-nospam.com...
>
> Thanks CLR,
>
> The list of numbers would be variable (depending on user input) but
> 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 100
> the result = 50 (2)but for 99 the result = 47 (2) & 5 (1). The numbers
> available need to be read from the column.
>
> Hope that helps, please let me know if you need any more info.
>
> Cheers, Cam
>
>
> --
> Cam
> ------------------------------------------------------------------------
> Cam's Profile:
> http://www.excelforum.com/member.php...o&userid=27836
> View this thread: http://www.excelforum.com/showthread...hreadid=538118
>

Hi Dana,

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

Cheers.

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

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" <Cam.27cbvm_1146842700.6306@excelforum-nospam.com> wrote in message
news:Cam.27cbvm_1146842700.6306@excelforum-nospam.com...
>
> ....any takers?......
>
>
> --
> Cam
> ------------------------------------------------------------------------
> Cam's Profile:
http://www.excelforum.com/member.php...o&userid=27836
> View this thread: http://www.excelforum.com/showthread...hreadid=538118
>

Hi Clr,

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

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

Hope that helps, thanks again

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

Vaya con Dios,
Chuck, CABGx3


"Cam" <Cam.27nh9m_1147362901.2256@excelforum-nospam.com> wrote in message
news:Cam.27nh9m_1147362901.2256@excelforum-nospam.com...
>
> Hi Clr,
>
> Sorry for the delay, for some reason I am not getting reply
> notification emails?
>
> The list is variable and the mins & maxs are 1 & 100, all whole
> numbers. The result needed is also a variable whole number.
>
> Hope that helps, thanks again
>
>
> --
> Cam
> ------------------------------------------------------------------------
> Cam's Profile:
http://www.excelforum.com/member.php...o&userid=27836
> View this thread: http://www.excelforum.com/showthread...hreadid=538118
>

Hi, 20 would be the maximum.

Cheers. Cam

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" <Cam.27nymm_1147385401.7764@excelforum-nospam.com> wrote in message
news:Cam.27nymm_1147385401.7764@excelforum-nospam.com...
>
> Hi, 20 would be the maximum.
>
> Cheers. Cam
>
>
> --
> Cam
> ------------------------------------------------------------------------
> Cam's Profile:
http://www.excelforum.com/member.php...o&userid=27836
> View this thread: http://www.excelforum.com/showthread...hreadid=538118
>

Spot on......

Thanks for helping.

Cam

Any news clr?

Thanks.

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" wrote:

>
> Any news clr?
>
> Thanks.
>
>
> --
> Cam
> ------------------------------------------------------------------------
> Cam's Profile: http://www.excelforum.com/member.php...o&userid=27836
> View this thread: http://www.excelforum.com/showthread...hreadid=538118
>
>

Hi CLR,

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

Have a good weekend.

Cheers,

Cam.