Formula Which Will Work Out 48 Permutations
Jan 17, 2007
I need to work out a formula which will work out 48 permutations.
There are three headings: Impact, Probability, Risk Category:
ImpactProbabilityRisk Category
VHM = 1
VHH = 1
VHVH = 1
HH = 1
HVH = 1
VHL = 2
HM = 2
MH = 2
MVH = 2
HL = 3
LVH = 3
MM = 4
LH = 4
ML = 5
LL = 5
LM = 5
The formula I need is: if Impact is "L" and probability is "M", then return answer = "5".
The thing is - there are 48 possible answers - and im not sure what formula you could type into 1 cell - which would answer this.
View 9 Replies
ADVERTISEMENT
Nov 15, 2006
This formula will only do 7 permutations, I need it to do 8. Is there some way to do that? Thanks in advance.
=IF(AND(A10,B10),B10,(IF(AND(A10,B11),B11,(IF(AND(A10,B12),B12,(IF(AND(A10,B13),B13,(IF(AND(A10,B14),B14,(IF(AND(A10,B15 ),B15,(IF(AND(A10,B16),B16)))))))))))))
View 9 Replies
View Related
Jan 11, 2009
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View 4 Replies
View Related
Jan 2, 2009
I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.
2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")
and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations
View 2 Replies
View Related
Feb 24, 2009
I'm sure I should be able to do this but my brain is out of gear today!
Can someone tell me how I can create a list of every 5 letter permutation using only letters (A-D).
I also need to do the same for 4 letter and 3 letter, again using A-D.
Would prefer to do it without VB but if this is the only way......
View 6 Replies
View Related
Apr 26, 2006
You will find a VBA Code for a PRIVATE example - displaying all 6 numbers combinations out of 10 (1-10).
My code displays them (in column "A") BUT What my question to you is - does someone have a General/Universal code to handle other kinds of combinations.
To my opinion, the usage of Nested (For-Next) Loops will not be the ideal solution - so maybe by using VBA Recursion ?
View 3 Replies
View Related
Jun 2, 2007
I must first thank the forums for the amazing help in making my life just a little easier, especially member "shg" for helping set up the VBA code to help me.
So I have a sheet to develop all possible combinations of units, the letters with their associated values above, as well as the maximum number of units possible. The user inputs a top value, this is followed by some subtraction leading to a working value with a tolerance level in which the sum of the combinations needs to remain within.
What my predicament is that while the VBA code in the attached book works to develop most combinations, when I input a small number such as 405, the combinations that arise are not complete. The results are shown in the attached workbook: show combinations of H and PP, but any human can read that there should also be 2 HN in the results, as the math add's up to within the tolerance level.
View 9 Replies
View Related
Jul 23, 2007
I am creating a class of permutations. The problem is that there are many ways to describe what a permutation does. for example.
Dim aPerm As New clsPermutation
Dim bPerm As New clsPermutation
aPerm.DoesTranspose(2,3) : Rem aPerm.Action({a,b,c}) = {a,c,b}
MsgBox (aPerm Is aPerm.Inverse)
I want the message box to return True. But since aPerm and aPerm.Inverse are different instances of clsPermutaion, it returns False. (.Inverse is a read-only property that returns a clsPermutation.). What I would like to do is have a collection, myPermutations. When I specify the action that aPerm does, I would like
1) if nothing in myPermutations does the same thing as aPerm, then add aPerm to myPermutations.
2) if something (xPerm) does the same thing as aPerm, then
Set aPerm=xPerm
so that "xPerm Is aPerm" returns True.
The problem I have is that in the class module,.....................
View 8 Replies
View Related
Apr 17, 2007
This IF formula does not work on the last part of this formula
IF(E10>50,"$50.00",0)))) and I think there is a conflict with the
IF(E9>199,(E9*0.01) but I cannot figure out what the problem is.
=IF(E8>0,"$0.00",IF(E9199,(E9*0.01),IF(E10>50,"$50.00",0))))
View 9 Replies
View Related
Aug 11, 2014
I have some problem with my excel formula here. It works in some rows but doesn't in others.
I have attached the excel file herewith : Book1.xlsx
View 4 Replies
View Related
Aug 7, 2014
See the attached workbook.
Please edit the formula in column B so that it only gives a BINGO if the adjacent cell in column G is less than 51.
So cell M16 should not be a BINGO because cell G16 is not less than 51.
Attached Files : Book1.xlsx
View 3 Replies
View Related
Jan 8, 2014
Combing these two formulas. I have a work schedule spreadsheet. If the employee is Off I want the value to = 0 (zero hours). But if the cell has a start time I need it to calculate those hours. I know both of the formulas work individually. But I need them to work together.
Formula for when cell says OFF:
=IF(C11="Off",0)
And the second
Formula to calculate hours (based on start time/finish time is the same for all employees)
=($X$4-C11+($X$4<C11))*24
View 2 Replies
View Related
Oct 18, 2008
=SUMPRODUCT(--($C$1:$C$17="S"),--($C$2:$C$18"S"))
how does this formula work?
It counts the number of instances in a list but not sure how
does the ranges have to be differant for this to work? ie C1:C17 C2:C18
View 9 Replies
View Related
Jul 23, 2012
I need VB codes for an activity in excel. Im trying to create an example file for the suppliers to enter keywords (Search keyword) where it should generate different formats of the search (WORDS) and its case sensitive. Suppliers would be typing the keywords in A2 and it should generate the Permutations horizontally against the keyword (B2 C2 D2 E2 etc). Let me know if you need more clarifications.
Example given below and also attached the file for your reference.
New Keyword – Singular Proper Case
Singular-PR
Singular-LC
Singular-UC
[Code]....
View 4 Replies
View Related
Apr 22, 2014
Simple permutation problem that will most likely requires nested loops. I need a list of every possible permutation of words in a row broken out by column. Order does matter and not every row has the same number of words in it. I have attached a sample worksheet with a sample table and then the needed output format.
View 5 Replies
View Related
Aug 31, 2007
Is there any way to determine all possible unique combinations (or sets) of any 3 strings in a table? I need to make a master set list from a list of strings which could be over 100 elements long.
Once I have a result set, I need to get rid of any groups which have a value contained in another group.
Example:
cells A1 through B100 contain:
Item1 45
Item2 50
Item3 98
...
Item100 75
Result set would contain groups of any 3 item combinations where the corresponding "B" value is within a certain range (between 40 and 80), while also trying to create sets with the lowest possible sum (placed in the 4th column) of the 3 numbers.
One possible combination would be:
Item1 Item2 Item100 170
etc...
I need to list all possible combinations, without re-using an element from a previous (lower sum) combination that matched the criteria.
View 13 Replies
View Related
Jun 27, 2009
For Ex:- If there are three Items.. A B C and forming a group of 2
then the the total no of combinations would be permut(3,1) = 6...
I need this to be shown as follows depending on the no of itmes and no of group no=2 in this case...
Permuations & Combinations are as follows:
AB
AC
BC
CA
CB
BA
View 10 Replies
View Related
Mar 1, 2013
I own a custom print shop and I am trying to generate skus based on variables..Column A will only have one item (the product code), column B will have the sizes, Column C may contain more or less colors depending on what is offered for the shirt style, Column D hopefully can contain all the combinations. All this data will be in contained within one worksheet..
Column A
Column B
Column C
Column D
G200
S
Red
[Code] .......
View 3 Replies
View Related
Nov 26, 2013
I have trying to assign cards to respondents to view. I have 16 cards total which are grouped in sets of 4. Each set (of 4) is given a color, (red, green, blue, and yellow). Each color has a 4 different shades (S1, S2, S3, S4) so I have the following 16 cards:
1: Red_S1
2: Red_S2
3: Red_S3
4: Red_S4
5: Green_S1
6: Green_S2
7: Green_S3
8: Green_S4
9: Blue_S1
10: Blue_S2
11: Blue_S3
12: Blue_S4
13: Yellow_S1
14: Yellow_S2
15: Yellow_S3
16: Yellow_S4
I will have a total of 10 respondents giving their opinions who will each see 5 of the cards, randomly. Each respondents must see each color at least once (given that 5th card will be a repeated color). Each card must be tested 3 times (given that the 2 additional cards can be any of the cards).
If there a function or cell code that I can write so that I can allocate the card #s (1-16) across my 10 respondents?
View 1 Replies
View Related
Mar 21, 2007
A1 has some characters
this code will generate all possible words, that can be made using all characters
system
permutate and checkspelling: if OK then write to column B
example
A1: iftrs
results: first frits rifts
Option Explicit
Dim CurrentRow
Const col = 2
Sub correctly_spelled_permutations()
Dim InString As String
Dim CalcSet As Integer
InString = Range("A1")
If Len(InString) < 2 Then Exit Sub
With Application
.ScreenUpdating = False
CalcSet = .Calculation
.Calculation = xlCalculationManual
.EnableCancelKey = xlErrorHandler
.StatusBar = "searching valid combination"....................
View 9 Replies
View Related
Mar 11, 2009
I want to output every combination of a set of numbers. These permutations must include combinations that use only a few of the numbers as well as all eight...ie.
1,
1,2
1,2,3
as well as 1,2,3,4,5,6,7,8
The macro below is from
http://www.j-walk.com/ss/excel/tips/tip46.htm
This only produces combinations using every number. I'm not sure how this macro works but hopefully someone with better know how could run with it or break it down for me!
I only need to achieve this once but am pretty sure doing it manually will cause error and or madness
Dim CurrentRowSub GetString() Dim InString As String InString = InputBox("Enter text to permute:") If Len(InString) < 2 Then Exit Sub If Len(InString) >= 8 Then MsgBox "Too many permutations!" Exit Sub Else ActiveSheet.Columns(1).Clear CurrentRow = 1 Call GetPermutation("", InString) End IfEnd SubSub GetPermutation(x As String, y As String)' The source of this algorithm is unknown Dim i As Integer, j As Integer j = Len(y) If j < 2 Then Cells(CurrentRow, 1) = x & y CurrentRow = CurrentRow + 1 Else For i = 1 To j Call GetPermutation(x + Mid(y, i, 1), _ Left(y, i - 1) + Right(y, j - i)) Next End IfEnd Sub
View 9 Replies
View Related
Oct 5, 2006
I have a number 23753.2570, to be precise.
in another column, i have 400 rows filled with different numbers, from 1.4 to 23000,7840.
I need to find out all the possible combinations of which numbers from that 400 can make up 23753.2570.
so if my number was 40,000, and i had 4 cells with 10,000 in them, the outcome will be:
"There are 4 possible combination to your value"
then paste it into a new worksheet with the results
[cell1] = 10000 + [cell2] = 10000 + [cell3] = 10000 + [cell4] = 10000
Total = 40000
i found this code, but i tested it on the example above and it said "All Combinations exhausted" which they wasn't?
Sub findsums()
'This *REQUIRES* VBAProject references to
'Microsoft Scripting Runtime
'Microsoft VBScript Regular Expressions 1.0 or higher
Const TOL As Double = 0.000001 'modify as needed
Dim c As Variant
View 9 Replies
View Related
Oct 24, 2006
I've got what I think is a pretty interesting problem, not sure if it can be solved with Excel, and if not that's fine I'll think of something better but thought I would put it up here.
I have two lists, one of adjectives, one of nouns. I want somehow to generate every possible combination of the two. Eg:
List 1:
Adj1
Adj2
Adj3
List2:
Noun1
Noun2
Noun3
gives us:
'Adj1&Noun1', 'Adj1&Noun2', 'Adj1&Noun3', 'Adj2&Noun1' ... etc etc
Is there a way to solve this problem using a macro or a pivot table?
NB ideally i would like all possible combinations to include reversals. i.e. 'Noun1&Adj1' and 'Adj1&Noun1' etc.
View 9 Replies
View Related
Mar 15, 2007
i've got 2 numeric numbers one in each cell. These numbers indicate Maximum occupancies for a hotel room...
Maximum Adults Maxmium Occ
3 5
The Occupancy table looks like this
Adults Children Infants... so taking the above numbers the table should be built up like this
Adults Children Infants
1 0 0
2 0 0
3 0 0
1 1 0
1 2 0
1 3 0
1 4 0
1 1 1
1 1 2
1 1 3
3 2 0
3 0 2
3 1 1
etc...
so basically to room can have a maxium of 3 adults and 2 children equaling the maxium room occupancy.
i'm trying to create a function that will automatically do this and workout subsquent numbers below the maxmium but i can't get anywhere near the result i am looking for...
View 8 Replies
View Related
Dec 13, 2007
I have a set of numbers 1-24, what I would like to have is to list every 4 set combination of 24 numbers. I believe this is 10626 combinations. I would like the combinations to display in seperate cells.
For example: Rows 1, column 1, 2, 3, and 4 would look like 1 2 3 4. No two numbers can be the same within that row. Row 2 would have 1 2 3 5 and so on untill all possible combinations are exhausted.
How does one do something like this within excel?
Also, I would like to be able to change the value of 24, so instead of entering 24 I could say I have a set of 32 and want a list of 4 or 3 permutable combinations. I want to be to make this as custom as possible.
View 6 Replies
View Related
Jun 23, 2014
I'm trying to count the rows of a worksheet based on the location selection. Heres what I'm using:
[Code]....
Before I add the Vlookup, I get a number of 50. I should get at least 40+, but after adding the
[Code] .....
I get "0."
I'm wondering if I need to use VLookup differently in this case ????
View 14 Replies
View Related
Mar 12, 2008
What am I doing wrong here. Some times if I do a =if( ) formula it doesn't work. For example, look at the attached picture. Cell K63 should say "End of Run". But it doesn't, What gives?
Also I have had before where i do =IF(X62>$O$2,"End of Run", 0) (and $O$2 =81) and the cell when X62 is 81, not greater than. I know I can to >=, but thats not what I am doing.
View 11 Replies
View Related
Nov 19, 2007
On the attached form, for row 3 information is manually inputed into C3 and E3 to J3; K3 then looks at the max value inputed in E3 to J3 and alters L3 accordingly.
Row 4 is my attempt to automate the process. I am trying to alter the sheet so that the information added into C4 updates E4 to J4 automatically and then K4 looks at the max value and alters L4 accordingly.
Since I added the formula to H4 to look for info in C4, the max formula in K4 no longer works, as such L4 will not update.
View 14 Replies
View Related
May 31, 2012
I currently have a sheet of data with names and scores (1-10) on e.g.
NameScoreJohn10Smith5Steve4John9
and what i am wanting is a formula that will read these columns and tell me the amount of 1's, 2's, 3's, 4's, 5's, 6's, 7's, 8's, 9's and 10's the corosponding name has gotten.
for example:
JohnSmithSteve123415167891101
I have been playing around with COUNTIF formulas but cannot seem to work this out.
View 5 Replies
View Related
Jul 11, 2013
I'd like a calculation that gives me a total of 7.50 hours instead of 8:00 hours (I'm hoping to add up the time worked into a meaningful hour:minute format)
View 2 Replies
View Related
