Hi, you replied to my post below a while ago but ive only got round to looking at it. Thanks by the way.
Ive looked at your awnser but couldnt work it out. I was wondering (if your still out there, fingers crossed) if you could e-mail an example on an actual spreadsheet. It would be greatly appreciated.
IS THERE ANYONE ELSE THAT COULD DO IT ??0
http://www.excelforum.com/search.php?searchid=397332
THANKS
Sorry e-mail is [email protected]
As it said . . .Originally Posted by jhill
note, . . . . I bet you're glad you asked that question
...
If you follow the instructions you will get your list, Formula-Fill is described at http://www.mvps.org/dmcritchie/excel/fillhand.htm
If you have problems let me know how far you got and what gave you trouble.
note, wen you copy the formula to A2 you need to remove Web-introduced spaces - there are no spaces in that formula.
Cheers
---
Last edited by Bryan Hessey; 10-12-2006 at 10:36 AM.
I carnt do it. im absolutely usless in excel. PLEASE, PLEASE, PLEASE can you e-mail it to me. ill probably be able to work it out then.
http://www.excelforum.com/showthread...ighlight=1-2-3
please.
Do you wish to list all the posible outcomes, or know how many there are?
for unique values the number of outcomes is =fact(number) so in your instance 1,2,3 3 unique number outcomes =fact(3)=6
Factorial is 1 x 2 x 3 =6
=fact(4)= 1x2 x3 x4=24
Do you want a list of all the combinations or just the total number of combinations
or if you were taking a sample so how many unique samples (order important) out of a larger number
=permut(number of numbers,sample size)
so how many times you can get a sample of size 3 from 10
is
=PERMUT(10,3)=720 the number of permutations
in the above permut(3,3)=6 the same answer
if the order is unimportant so 1,2,3 is the same as 3,2,1 for example
it is combin(10,3)=120 the number of combinations
Regards
Dav
Last edited by Dav; 10-13-2006 at 08:15 AM.
Hi Dav. Thanks for the reply. i would like a list of all the possibile combinations.
if i was to type 123 in a cell, is there a formula that would list all possibile combination ??
It would be possible to create a macro that would list all the combinations for a given number by creating for 4 item 4*4*4*4=256 rows and then filtering out the duplicates. This would only work simply for up to 6 items and would have to be custom written for each number
as the rows initially are are
1 1
2 4
3 27
4 256
5 3125
6 46656
it may be easier to download a calculator off the internet, possibly the one on
http://www.saliu.com/permutations.html there is a free exe file you can download that appears promissing
but i can not test it as i am at work, I may try when i get home
Regards
Dav
No, but I will make it easy to do.
If you do noy know how to 'formula fill' then see http://www.mvps.org/dmcritchie/excel/fillhand.htm
For your list, select a new worksheet, and make Cell A1 'text' format (rightmouse on A1, 'Format Vell' and select 'Text' from the number format.
Put 111 in A1
In A2 put =IF(MID(A1,3,1)+0<3,LEFT(A1,2)&TEXT(MID(A1,3,1)+1,"0"),IF(MID(A1,2,1)+0<3,LEFT(A1,1)&TEXT(MID(A1,2,1)+1,"0")&"1",IF(LEFT(A1,1)+0<3,TEXT(LEFT(A1,1)+1,"0")&"11","end")))
and formula-fill that to cell A28
in B1 put
=LEFT(A1,1)
in C1 put
=MID(A1,2,1)
in D1 put
=RIGHT(A1,1)
in E1 put
=SUM(COUNTIF(B1:D1,B1),COUNTIF(B1:D1,C1),COUNTIF(B1:D1,D1))
in F1 put
=IF(E1>3,"",A1)
and formula-fill B1 to F1 down to row 27
Column F now contains your list.
hth
---
Byran. I have tried to follow your incsructions by cutting & pasting your seggested formulas into the cells however it does not work. Could you please check it by cutting & pasting yourself. THANKS.
thats where the instructions came from
What didn't work?
did you take the space out of
~~~C1),COUNTIF(B 1:D1,D1))
~~~ LEFT(A1,1)+1," 0")&"11","end")))
~~~C1),COUNTIF(B 1:D1,D1))
these are Web induced and you need to remove them.
You may also need to remove and replace the = in a formula to make it acceptable after you copy from the web
Where did it go wrong, can you post your book if it doesn't work?
---
Si fractum non sit, noli id reficere.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks