Getting Excel To Calculate All Combinations Of A Set Of Data
Nov 4, 2005
I've found myself having to use a spreadsheet to calculate some acoustics problems, (problematic frequencies, called modes, in orders ranging from 0,1,2,3....).
Ideally what I want to be able to do is simply to get Excell to calculate all the possible combinations of these modes.
A combination could be:
000
011
101
110
111
012
143
etc.
Obviously this is a nightmare to input by hand, is there any kind of function I can use to help with this process?
Also these frequencies only need to be calculated up to a certain point, so is it possible I could also instruct the cell to only display a value if it is less than a value in another cell?
I want to see how much combinations are possible when i got 6 numbers..
-- got this numbers 1--2--3--4--5--6 -- want to calculate how much combinations of ( 2 ) numbers possible -- want to calculate how much of ( 3 ) numbers possible -- want to calculate how much of ( 4 ) numbers possible -- want to calculate how much of ( 5) numbers possible
-- never 2 same numbers together (2-2) or (2-2-3) or (2-2-3-4) may not be in list
-- How can i make the result visible in kind of list ?
I would be nice if somebody knows a good solution..
(I am unable to use Colo’s HTML Maker to post this—I have noticed several newer members indicating the same thing).
|....| 1 | 2 | 3 |4 | 5 | 6 | 7 | 8 | | A | Y | N | Y | N | Y | Y | N | N | | B | Y | N | N | Y | Y | N | N | N | | C | Y | Y | N | Y | N | Y | Y | N | | D | Y | Y | Y | N | Y | Y | N | Y |
The answer to the table above is: 144 combinations (using "Y" entries only). The answer is not to do just a simple count of the entries in each row and multiply as this produces 360 combinations rather than the desired 144 combinations. In this example, combinations such as 1-1-1-1 or 5-4-4-3 would not need to be calculated, which is the reason the answer is not 360 combinations.
The table always allows for 8 x 4 cell entries. It is possible that every cell can be selected (the answer would be 8!/4! in this instance); At least one entry must occur in each of the first 3 rows. In this example, if row 4 was excluded the answer would be 38 combinations (3 rows);
I am looking for 2 formulae: The first one to calculate the combinations of entries in 4 rows and the second to calculate for 3 rows only.
I am trying to find a way to calculate the number of hours between date/times found in separate rows. The attached data set will help to envision what I am talking about.
For each couple of rows, I need to find a way to calculate the number of hours elapsed from row 1 to row 2. In the first example, to calculate the number of hours between 12/2/2009 8:56:51 and 12/4/2009 6:35:27.
Lets say I have 10 numbers (1,2,4,5,6,8,9,12,19,13). Now, I am trying to get a list of all possible combinations (single digit, two digit, 3 digit, 4 digit... 13 digits).
Assume that the 10 numbers (this is a variable, it can sometimes be 9 or 11 etc) are in column A and I want all the possible combinations in column B.
I am a teacher and have a recurring problem when trying to sort data in excel. The problem involves national curriculum sub levels. For those not familiar with these levels, there is a main level then 3 sub levels. The problem is that they are counter intuative to the way excel sorts data. The sub levels work like this:
4a - highest 4b 4c 3a 3b 3c 2a 2b 2c - lowest
There are lower and higher levels, but you get the idea. If I have a set of pupil data that I want to sort into decending order, I find it difficult in Excel. E.g.
At the moment I manually complete this. E.g. for Mary the Sub levels progress required would be 4, as there are 4 sub levels between 3b and 4a. For Joe it would be 3 as there are 3 sub levels between 3a and 4a, etc. Is there a quick way to get excel to calculate this.
i found this vb code the other day on this forum from a post made way back in 2005 i think it was. I've found it really useful for what i wanted to do, but i wonder if it can be expanded or changed to allow for more sets of imformation.
This is the Code, it finds all possible combinations from 3 sets of 3 results.
What I am trying to achieve using the example below:
ID Subject 1 Subject 2 Subject 3 1 Italian French German 2 Italian Art Physics 3 German French Italian 4 French Italian German
the result:
Italian French German 3 Italian Art Physics 1
As in the example, the combinations of Italian, French and German where counted, irrelevant of whether the subjects are in 2nd, 3rd or 4th column.
I tried to do this task by creating a pivot table but there are so many permutations and subjects that it would take me a long time to add the combinations.
I have one last question. is there a way to make this a little more complicated? for every two possible combinations of names, i have a value. is it possible to create a fourth colum, in which the sum from the three values is calculated?
for example-
12 different letter names (a-l) after running the code derk sent, i now have a-l in cells A1:A12 I also have every combination of 3 using these 12 names in columns C D and E
take for instance the combination of three names (a, b, c). i have values for ab, ac, and bc in columns G, H and R respectively. can these values be summed together and averaged in the fourth column?
keep getting "The formula you typed contains an error".
To calculate a person (or anything else) age you basically subtract his date of birth from today's date. However which date functions to use depends on the amount of precision and the output format you want.
3. If we want his age in years and months, then we can use a formula like this one: =INT((TODAY()-A1)/365.25) & ā€¯ years and ā€¯ & INT(MOD((TODAY()-A1)/365.25;1)*12) & ā€¯ monthsā€¯
The output of this formula will be as follows: 40 years and 2 months.
I change A1 to G3 because that is where I the birth date.
MyCalc:=CALCULATE([MyMeasure], Filter(Tbl,Tbl[Field] && Tbl2,Tbl2[Field2])) It works with 1 Filter : MyCalc:=CALCULATE([MyMeasure], Filter(Tbl,Tbl[Field])) Just falls down when I add the second filter.
Id like to apply a formula, any formula to an entire column if it contains data, and incorporate the original data in the calculation and then replace the original data with the result. I don't want to have to create new columns.
I'm using this to fix up database results; a common problem is dates in dot format e.g. 14.11.2008
All I have so far is an autofill formula that overwrites everything. Can someone help me with the rest? I'm using the SUBSTITUTE function to replace the dots '.' with slashes '/'
Sub Create_formula_result()
Dim Limit As Long Dim r As range Set r = range("A1")
On a excel sheet I've got columns, each column represents a weeknumber. I want to calculate the so-called 4 wk average for each row and for each week and this is the formula I use:
(this is not the actual formula but simplified, that's not really important).
It's the checks that make things a bit more complex. If a value of a weeknr is zero, skip it, but if the next value is also zero, just skip the formula alltogether and make it a zero (or text like "false"). So another thing that has to be accounted for is that if a value is zero, the next weeks value is taken instead.Example (see included file):
I want to calculate the formula (mov 4wk avg) for the third value for week 12, which will make the formula
(0.2*6)+(0.3*6) now there's a zero on week 14 so I skip it, then formula will be: (0.2*6)+(0.3*6)+(0.6*6)+(0.9*6)/(6).
Right now I'm doing this in VBA with a lot of variables and a lot of if statements.Is there an easier more effective
I know the example sheet is a 2007/2010 version but I need to accomplish this for 2003.
I have been trying to get excel to calculate elapsed time between two dates using mostly what I've read from other elapsed time posts. But I am clearly missing a step. I'm trying to streamline my process for taxes.
I want to take a time in the format of " yy/mm/dd hhmm " example: 1
3/04/24 2245 ( note its a 24hr clock) 13/04/24 2245 would represent 2013 April the 24th at 10:45PM.
------- A --------------- B -------------------- C 13/04/24 2245 ------13/05/24 0900 ---------[display hrs elapsed] <-- currently get #name?
I have custom formatted A and B to yy/mm/dd hhmm and formatted c to [h]:mm
When you enter b-a in column c you get #name?
If its easier to have the date and time into separate columns I can give that a shot, I was just trying to make it as streamlined as possible for copy past.
I am trying co Calculate number of nights between to dates for example 10/26/2012 -- 11/25/2013 in a period that can be greater than the month Period. for example. some One checked in on October 24, 2013 and they will checkout on december 17, 2013. in the monthly Period of 10/26/2012 -- 11/25/2013, I am trying to calculate, the monthly nights of that period itself, and the number of days he was checked in on that period.
Formula for computing the weighted standard deviation? I was able to calculate the weighted average (16.4) but have been unsuccessful in computing the weighted SD. I conducted numerous searches and have not been able to find a posted formula that actually works. My data are as follows:
*The reason I set up the weights the way I did is because I want to give more weight to the more recent years of shootings when comparing them to other years such as 2008, 2009, 2010 and so on.
I have a large data sheet with dates in column B - in column A, I'm trying to write a formula that will determine what fiscal month it should be mapped to.
I have the calendar listed on a different tab.
Fiscal Month Start Date End Date
January 1/1/2012 1/31/2012
[Code] .........
The formula I tried using is: =LOOKUP(B2,Calendar!$B$2:$C$25)
I need some different columns within a row to calculate based on a statement found in an earlier column in the row. something similar to below. there will only be two statements total which are noted on the table below.
A B C D E
1 0 0 20 5%
[Code].....
So basically If column A = disposition calculate D and E in column B but if Column A = Receipt calculate in column C
The formula for D and E is not exactly as shown just using that as a quick example. I'm using office 2010.
