I am trying to show the all possible combinations of a set of numbers in Excel, in my case I think permutations are more appropriate to use. For example: there are three numbers 1, 2, 3 I want to show results like:
1, 2, 3
1, 3, 2
2, 1, 3
2, 3, 1
3, 2, 1
3, 1, 2
The functions in Excel available only give the total number, but I want to see these combinations!
I have three columns that each have a drop down of text options; column A has three options, column B has three options and column C has two options. I want to have a fourth column that will show a text value dependent on the combination of the three columns.
It ends up being 18 variations so my IF formula just won't compute.
This is basically the format I have and works for a few values then just chucks up an error when I extend it to all 18 variations:
IF(AND(A1="text",B1="text2",C1="text3"),"show this text",
IF(AND(B1="other",B1="other2",C1="other3"),"then show this", ) etc etc
Any way I can get this to work for all 18 possible outcomes??
I have a list of numbers from 1-7 would look like this each number in a seperate cell.
3 5
1 2 3 5 6 7
2 4 5 6 7
6 7
4 6
6
I want to use one number from each row (which there is only 6 rows) and then find every number from 1-7 that will complete the sequence 1-7. So with the numbers above (using one number from each row) the only other numbers that could be used would be 3 or 5.
The combos that would work:
row 1 use 3 ----------- row 1 use 5
row 2 use 1 ----------- row 2 use 1
row 3 use 2 ----------- row 3 use 2
row 4 use 7 ----------- row 4 use 7
row 5 use 4 ----------- row 5 use 4
row 6 use 6 ---------- row 6 use 6
5 would complete ---------- 3 would complete
Remember the numbers and how many numbers in each row can change but will always be 1-7 and I always need to find every number that can complete the sequence 1-7 by using one number from each row.
The numbers are file attributes, as you know these are
Normal = 0
Read Only = 1
Hidden = 2
System = 4
Volume = 8
Directory = 16
Archive = 32
These numbers are cumulative, so if a file has an attribute of 5 it is Read Only and System (1 + 4), it can't be anything else. Or if it has an attribute of 6 it can only be Hidden and System (2 + 4).
What I need is a spreadsheet that calculates every possible combination of these numbers, so I can check my Select Case statement has covered all possible combinations.
If it was just a one off project I could just work it out "by hand", but I have realised that there are several other projects I have that this would be useful in.
e.g. I am doing a skills matrix at work. If I give each skill a number, then give each employee a cumulative total number then I can have a spreadsheet that shows their skills. For each employee number there will only be one possible combination of skill that add up to that number.
My employer often adds new skills, so each time this happens I will have to check every combination is covered.
So I really need a spreadsheet solution, something I can input a group of numbers and it will show me a list of every possible combination of those numbers. The number of numbers in the group will vary, so a solution that only works for a group of (say) 6 numbers won't work. It has to work on a variable group of numbers.
If I have A1 = 1, A2 = 3, A3 = 5, A4 = 7, and A5 = 9 in column A, Would someone know how to get the combination of numbers in column A that equal 13, or 24 for example?
View 9 Replies View RelatedIs there anyway to use Excel to determine which of a set of numbers equals a total? I encounter this issue when dealing with accounts receivable. We receive payments with no details on what is being paid. I have a list of open items and a total being paid. I would like to have Excel determine which combination of invoices can be added together to match the payment.
View 4 Replies View RelatedI have provided an attachment. what I am trying to accomplish. I am trying to have a worksheet that if I input multiple 3 number combinations into the input cell range, after pressing the sort button, it would then sort, rank and count each 3 number combination for me. So as my attached file illustrates, the input cells would be A9:D14. In this sample the ranking consists of cells A19 - A31 as the ranking columns. Cells F19 - F31 show the counted and sorted results and are ranked accordingly. I need a sort button as illustrated in cell F10 to make the worksheet function after the 3number combinations are inputted in cells A9:D14. How do I get started to make this work? I do not know VBA codes or macros so I will need guidance along the way if this is what is needed. I do have some working knowledge of formulas (e.g. countif, rank, etc.)
View 8 Replies View RelatedI have a list of 10 of my favorite/lucky numbers that I want to play in the lottery. The lottery picks 5 numbers total. I need a way to show me all the possible combinations of my 10 numbers picked in a 5 number draw (hope that makes sense). There are no repeat combinations- for example- I DO NOT WANT 1-2-3-4-5 and 5-4-3-2-1 to come up as separate combinations- so each of my favorite #s needs to be used only once in each combination, and each set used once.
I have searched this board for 2 hours now- read tons of other posts, but not finding a real solution. The output will be a list of all the possible combinations (no repeats, and no permutations) using my 10 favorite numbers. Another example-
1-2-3-4-5
1-2-3-4-6
1-2-3-4-7
1-2-3-4-8
1-2-3-4-9
1-2-3-5-6
1-2-3-5-7
and so on.
How do I create this? I realize the resulting table will be quite a large number of combinations- but we're going to have fun with it and pick a few at random.
I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.
I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.
I want to have a cell report the sum of a column and have that sum change depending on which values I have filtered on another column. I have a cell reporting a sum of times in column E, but if I filter Column F to show only jobs for 1/16/09, I want that number to display the total time for just those shown. At this point, it just shows the total regardless of what is filtered.
View 3 Replies View RelatedI have over 2000 cells like the following
S304
S205
S204
S306
S207
and so on
Is there anything I can do to show just the numbers and not the S at the start without me having to delete each S individually?
I need a formula to show the numbers as they are when they are below 30, but when they go above 30 I need it to display 30 every time. I have attched a file.
View 3 Replies View RelatedI have a number format that I need assistance with:
The number format I need is 1234-000 or 1234-001
The normal number format is 1234-000, very rarely does the 1234-001 be used. So I would like the number format to left fill the left section 1234 and right fill the right section 001.
So I have a bar chart with numbers as its data labels...so each bar is divided into 4 colors equaling to 100% ...how can i show percentages next to numbers in the chart...so for example if a bar equals to 200 and each color is lets say 20, 60, 40, 80 ...i would like to show these numbers as well as percentages..i know we can do this manually by inserting a text box but the percentages might change quite often and i dont want to go through the hassle of calculating and changing them each time.
View 1 Replies View RelatedThe current method I'm using to get the results I need in Excel are a bit manual and excessive. is a simple all in one formula for what I'm trying to do,
The data is 3 rows. We can also assume the data is in the first three columns starting from A1.
698
885
499
As you can see in the above set from 0 through 9, the numbers that are not listed are 0,1,2,3,7
And As you can see the numbers that are listed are 4,5,6,8,9.
How do I find and show which numbers from 0 - 9 are not in the set.
How do I show the number that are listed in the set Example:
Note: the special characters aren't needed.
698 4,5,6,8,9 --- 0,1,2,3,7
885
499
Note the the evaluation is for every three rows. So the three rows in the example are from a table with 150 rows. Each row begins a new evaluation for the next three.....Example:
698 and the following two starts the next set
885 and the following two starts the next set
499 and the following two starts the next set
and so on ......
Over in the following thread
[url]
you can see we've hit a problem with the Calendar Control object.
I submitted a workbook which has an Activex calendar control embedded on a User Form. On my PC everything appears as you'd expect, with the calendar showing the individual days in the calendar matrix. However when the user loads the form on their system, although they see the calendar as you'd expect, and they can change the month & year at the top of the calendar, they don't see the individual day numbers, 1,2,3 etc. within the body of the calendar.
if i had a group of numbers , for example, the scores would be 30, 40 , 50 , 60 , 70 and 80 , how would i group that. like it would show me how many scores are between 30 and 50 , it would show up as 3 , then between , 51 and 60 it would show 1
View 2 Replies View RelatedWhat formula can I use to return the value in column A if the value is found.
Something like:
=LOOKUP($M$1,$B$18:$O$31,give the value in Column A of the row of where the result is)
I'm trying to write a macro in Excel that would change any number greater than 10 in a spreadsheet to say "+10"
View 9 Replies View RelatedI have a linked document, only 2 sheets. On the first sheet there is a column for + or - signs, I need a formula for the second sheet to show the + as a 6 and the - as a 5 instead of just the symbols carrying over.
View 3 Replies View RelatedLets say cells A1 to A5 contain these lines of information:
Till 174 (T0215) - till keeps turning itself off.
Till 245 - stuck on windows screen
116 - keyboard is unresponsive
Berkel Scale is constantly beeping
ped not reading cards Till 156
How can I show only the numbers from these cells (i.e. B1 will be 174, B2 will be 245 etc...)? As you can see the number isn't always in the same place, and doesnt always have the same characters either side.
How do I format numbers so it will show the actual first digit, and the rest as x?
so that $1,234.56
will appear as $1,xxx.xx
or alternatively,
$1,234,567.89
will appear as $1,xxx,xxx.xx
I would prefer to do this with custom formatting rather than VBA,
(this is so I can generate sample reports for prospective clients, without showing the real numbers in the tables)
I have a rather large report full of data, 10+ columns contain a RANKING column, and these ranks, 1-10, are assigned by a % bracket:
1-20% = 1
21-40% = 2
41-60% = 3
61-80% = 4
81-100% = 5
101-120% = 6
121-140% = 7
141-160% = 8
161-180% = 9
181-200% = 10
There are several hundred rows in each column that all need individual ranks. My first thought is to program a if then else statement for 1 to 10 with a % limit range, but this isnt feasable as i would have to program a separate if statement for each of the many hundred rows, or do one heck of a fancy loop that i just cant contemplate (as the rows are split and sepperated... no solid uniform place for the data to be plugged.) What is the best way to go about this? I'm at a total loss of ideas with my knowledge of excel.
Is it possible to just show numbers greater than an amount in a pivot table?
View 3 Replies View RelatedThe list columns 5,7 and 9 has number entries.The userform has 3textboxes.is it possible to link total amount of this numbers result into the textboxes.Like column 5"CZ" entries total will showup in textbox10,column 9"DD" = textbox14
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim a, i As Long, ii As Long, b(), n As Long
ListBox6.Clear
With TextBox2
If .Text = "" Then Exit Sub
If WorksheetFunction.CountIf(Range("cv:cv"), .Text) = 0 Then
MsgBox "No Entry !"
TextBox2 = ("")
Exit Sub..........
I want to show numbers on my excel spreadhseet in thousands. However, the challenge is I want to show negtive numbers in red within brackets. For instance, I have a number -104,784,089. I want to display it as (104,784) in red color. Similarly, I want to show 577,090 as 577. Is there a customer formatting I can set up in excel that do that?
View 2 Replies View RelatedHow do I truncate a range of numbers where a number will not show more than 2 past the decimal ....and this be in vba as part of a macro.
View 3 Replies View RelatedI am trying to find the right format that will show a variety of account numbers properly.
I have these types of numbers displaying like this right now:
9.04585E+13 (should be 90458524211818)
785656.001 (showing properly)
NRDE1312089 (showing properly)
SCGA 396.00 (showing properly)
The formatting is set to general, which works for all but the top type of account number. I tried setting to a custom format like "#.#######" ...The only problem with that format is that the "." shows up whether there is a decimal in the number or not. What's the way around that?
I need to have all of my number be "x10^-6", but Excel wants to make them, for instance, 7.66x10^-3, when I need it to read "7660x10^-6". How can I force excel to do this?
View 3 Replies View RelatedI have a worksheet which basically tracks time. the time is reported in Column C. In that row in Column E, there is a validation list with about 6 different categories in it. On the side of this "table" I have a list of all the categories and I want a value to be next to it that reports the sum of time (C) for each category (E).
So for the "Routing" category, I would want the value to be the sum of just data on the timesheet that have "routing" in Column E.