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?

View 10 Replies


ADVERTISEMENT

How To Calculate Combinations ?

Nov 12, 2005

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..

View 9 Replies View Related

Calculate All Possible Combinations

Sep 11, 2007

I have check list of 14games. With this I'd like to make combinations.

Each row has one default value for unchecked rows of 3 columns in the list.

View 6 Replies View Related

Calculate The Combinations Of Entries

Apr 9, 2007

(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.

View 9 Replies View Related

Calculate Number Of Hours Between Two Date/time Combinations

Jan 21, 2010

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.

View 2 Replies View Related

Number Combinations :: 25 Random Numbers (5 Digit Combinations)

Dec 15, 2008

I have 25 random numbers and I would like to get a possible 5 digit combinations of these numbers. Can anybody help me with the possible formula?

View 9 Replies View Related

All Combinations Of A Set Of Numbers Using Excel?

Aug 22, 2012

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.

View 3 Replies View Related

Data Sorting Query - Get Excel To Calculate?

Feb 10, 2013

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.

Jacob2c
Mary3b
John2a
Sally2b
Sarah2b
Joe3a
Bill3a

If I manually sort the above data into descending order of national curriculum level it will look like this:

Joe3a
Bill3a
Mary3b
John2a
Sally2b
Sarah2b
Jacob2c

but if I try to get excel to do it, obviously excel just treat the levels as alpha numeric and sorts them like this:

Mary3b
Joe3a
Bill3a
Jacob2c
Sally2b
Sarah2b
John2a

I know there must be an easy way involving creating a custom list, but I don't know how to do it.

Problem 2

A related query involves finding a way to get excel to complete the data in the Sub levels progress required column below:

NameLevelTargetSub levels progress required
Mary3b4a
Joe3a4a
Bill3a4a
Jacob2c2a
Sally2b3c
Sarah2b3c
John2a3a

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.

View 6 Replies View Related

Paste And Calculate Odds Data Into Excel

Aug 19, 2013

Basically all I want to do is paste some odds data in to excel and then get it to just calculate the odds for me example below

9/4 Exotic Isle, 5/2 Greenery, 5/2 Port Alfred, 6/1 Reqaaba, 12/1 Somoud

I know I can use text to columns using the deliminator to split them in cells however I just want to take the odds

So Exotic Isle would be 2.25 (9/4) Greenery would be 2.5 (5/2) and so on

I've tried using the left function basically all sorts.

View 3 Replies View Related

Number Combinations :: Between 7 To 15 (6 Digit Combinations)

Jun 30, 2008

What I am looking for is to select between 7 and 15 numbers in total, I want all the possible 6 digit combinations for this.

EG: if I choose 2,9,11,13,15,17&26, it would look something like this

2,9,11,13,15,17
2,9,11,13,15,26
9,11,13,15,17,26
And so on.

If I chose more numbers (10) 1,2,3,4,3,6,7,8,9,10 it would start something like this

1,2,3,4,5,6
1,2,3,4,5,7
1,2,3,4,5,8
1,2,3,4,5,9
1,2,3,4,5,10
And so on.

Please remenber I would like to be able to secelt between 7 and 15 number and be given all the possible combinations.

I would like it to be in one sheet but if that can not be done on as many as it takes.

It would be good if I could just type the required number into A1,B1,C1 and so on and they just gave the combinations required.

View 9 Replies View Related

Unable To Calculate Copy / Paste Data In Excel

Apr 21, 2014

I just copied a table from the web which contains numbers in the last column.

I pasted the data into Excel, but when I try to run any type of calculation i receive a #VALUE! error.

The annoying 'workaround' is to re-type the same numerical text into it's own cell, which allows for calculation.

I've tried changing the cell formatting to 'general', and 'text', but I still receive the #VALUE! error, when trying to calculate.

View 1 Replies View Related

Find All Possible Combinations From Data Sets

Jan 29, 2008

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.

Option Explicit ...

View 8 Replies View Related

Counting Combinations Where Same Field Data Can Be In Different Columns

Dec 19, 2012

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.

View 6 Replies View Related

Complicated -different Combinations Of 2 And Summing Data For Each Combination Of 3

Jun 3, 2006

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?

View 4 Replies View Related

Excel 2010 :: How To Calculate Age

May 1, 2014

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.

View 2 Replies View Related

How To Calculate Deciles In Excel

Jun 30, 2010

I have a set of data and I need to calculate the values per decile.

View 9 Replies View Related

Excel 2010 :: DAX Calculate With Two Filters

Dec 24, 2013

Using Excel 2010

How can I use 2 filters in DAX Calculate?

I tried

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.

View 7 Replies View Related

How To Calculate Total Commission In Excel

Apr 1, 2014

How do you calculate the total commission of a particular person on a consecutive months ?

Attached is the excel file for reference : Total Commsn.xlsā€ˇ

View 1 Replies View Related

VBA To Autofill Formula Over Column Data Calculate And Replace Original Data

Nov 14, 2008

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")

r.FormulaR1C1 = _
"=IF(RC[0]"""",(SUBSTITUTE(RC[0],""."",""/"")+0) ,"""")"
Limit = ActiveSheet.UsedRange.Rows.Count
r.AutoFill Destination:=range(r, Cells(Limit, r.Column))

End Sub

View 9 Replies View Related

Excel 2003 :: Calculate Formula For Third Value Of Week?

Nov 9, 2012

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:

(value*Tvalue)+(value*Tvalue)+(value*Tvalue)+(value*Tvalue)/(Tvalue)

(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.

View 9 Replies View Related

Excel 2010 :: Calculate The Difference Between 2 Dates?

May 14, 2014

I have a pivot table and I created a formula that says:
=jobCompletedDate-jobCreatedDate

and so for example, I have
3/31/2014 7:21AM - 3/31/2014 6:33AM

and that difference is giving me: .03367

I'm not sure what this value represents......the difference in dates, converted to ??

View 2 Replies View Related

Get Excel To Calculate Elapsed Time Between Two Dates?

Apr 3, 2014

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.

View 11 Replies View Related

How To Calculate Cumulative Time Over Different Days In Excel

Jul 28, 2014

calculate the running total of time spent in the attached spreadsheet.

View 8 Replies View Related

Excel 2010 :: Calculate Monthly Nights?

Nov 27, 2013

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.

View 1 Replies View Related

Excel 2007 :: Calculate Hours From Time?

Jul 20, 2012

I want to calulate time by decimal the problem is anything after 1 am wont work

example start at 12:25 finsh at 1:45 time should show up as 1.33

using excel 2007

View 3 Replies View Related

Using Excel To Calculate Weighted Standard Deviation?

Dec 4, 2012

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:

Year Shootings Weight*
2003 4 1
2004 17 2
2005 14 3
2006 19 4
2007 18 5

*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.

View 6 Replies View Related

Excel Formula To Calculate Fiscal Month

Dec 19, 2012

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)

View 4 Replies View Related

Excel Workbook - Calculate Number Of Different Results

Dec 20, 2013

I am building an excel workbook to calculate a number of different results and I am having trouble with one particular formula.

I need to enter in a formula that will return a value for the following:

ex. C21 = 10
C22 = 10
C23 = 10

C24 = IF CUMULATIVE SUM OF C21:C23 IS GREATER THAN 15 THEN START COUNTING AT 15 BUT INCLUDE THE FIRST 15

SO USING THE EXAMPLE ....IF C21:C23 = 30 THEN C24 SHOULD EQUAL 30. BUT IF C21:C23 WERE TO EQUAL 14 THEN C24 SHOULD EQUAL 0

View 3 Replies View Related

Excel 2013 :: Automatically Calculate Area Between Two Charts?

Dec 22, 2013

I have Excel 2013.

Find attached a sample fileDummy for area between.

charts.xlsx

View 3 Replies View Related

Excel 2010 :: Determine Row To Calculate Based On Statement

Jul 5, 2014

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.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved