excel query

Reasteh · 9 Jul 2010 at 12:27

Hi

In excel for example i have a column of numbers 1 - 10

i want to be able to type a value into a cell and it would pick from this column the numbers required to give this figure

e.g i need the number 13, it would then pick a random combination i would need to get this such as 10 + 3 or 5 + 8 etc and show which ones its selected

would something like this need to be done in vb?

dazzerd · 9 Jul 2010 at 12:34

interesting but why?

and it does sound like vb to me have no idea how to do it in a formula.

Reasteh · 9 Jul 2010 at 12:46

this would just be as an example, if i could do this i could use it for a manufacturing process where a measurement is given and excel would tell the operator which combination of blocks he could use on a machine to get this measurement

edscdk · 9 Jul 2010 at 12:50

if its only 10 numbers and 2 combinations you could simply add up every possibility and whose that equal the desired result are displayed..

eg
A1+a2=
a1+a3=
a1+a4=
...
...
...

but I assume its going to be more than 10 and possibly more that 2 combinations?

NutterzUK · 9 Jul 2010 at 15:50

You're going to have to look into random numbers, and how to obtain one in an excel spreadsheet.

If you pick a number like 10, and take off a random number less than 10 ( easy in java, but on a spreadsheet I don't know... if you want it in java I can do it for you ), then you will be left with two numbers that add up to 10. The random number + the remainder you get at the end. Unless you're wanting ALL combinations?