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