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
ADVERTISEMENT
Jun 4, 2014
Find the attached excel sheet : Example Statistic.xlsx
I am trying to calculate the crew hours from a database I created .
I entered the sumifs formula but i entered many argument .
The block time should be calculated based on the month and to calculate only for specific name for each crew .
The formula should be involve in the colorful cells only ...
View 4 Replies
View Related
Oct 20, 2013
I have a list in excel with ten values, but I need to get all combinations possible, picking 4 of 10 each combination. This must be as a combination and no permutation or variations either, I mean, values can't be repeated in a single combination.
I'm struggling to do this in a Macro but I don't get it.
View 9 Replies
View Related
Jun 17, 2008
The aim is to find those combinations of variable values which generate highest total gain. I attached the spreadsheet which shows the variables (A through K) and a Gain column. I created 5 additional tabs which show all possible 2,3,4 and 5-member combinations of the variables. These tabs are like coordinates of which variable combinations should be examined. As an example I used the first combination from the second tab = A and B. If you look at these two columns on the EXAMPLE CALCULATION tab you will see 7,7 in the Number combination which is the first number pair for these two variables. The headings of the red and the yellow columns calculate the total count for this number pair and the total gain. These were recorded on a separate EXAMPLE RESULTS tab along with some other pairs which appear afterwards (these were recorded only from the first 39 rows of the AB data). I need a macro which will cycle through each variable pair (only using the combinations from the tab 2 for now, annd later from 3,4 and 5 tabs) collecting statistics for each unique number combination it encounters (printing to a separate sheet one after one), such as shown on the EXAMPLE RESULTS.
View 9 Replies
View Related
May 3, 2013
I am building a dashboard for a project, which is going to be on a separate worksheet for easy printing and providing only needed data from my database. I have attached a small example in the format I will actually be using as I think the formula is going to be specific to my dashboard.
Attached file, you should see a column "Rank" highlighted orange, with certain rows being numbered. My objective is to display certain data from that particular row, and display it (scroll to the right to see the dashboard display layout) on the dashboard. I think my main goal is to have the specified data displayed in ranked order 1-10 from the top down, and I don't know where to start with that....the other possiblity is to just have the specified data display in ascending order of the zip codes as long as something other than <blank> is in the "Rank" field.
Example.xlsx‎
View 1 Replies
View Related
Jul 28, 2009
here i have results from a race. the racers have numbers in the format of a20,c51 etc
then the name of the competitor and car make model.
then some times that they run. in the format of 11.80 secs the following times have either thousands or hundredthousths of a second which i only want to comma seperate the 2 decimals before and after so a 12.3456 will be ,12.34,ignoring the 56 then the next result and so on.
so i want to comma seperate the a20,andrewspeck,vauxhalltigra,11.27,13.36,12.87,12.91 etc etc
now the name and vehicle will cause huge sorting head ache so leave those together i can seperate them manually.
.
A7,Andrew Speck,Vauxhall Tigra,11.27,13.36116,12.87113,12.91124,12.14134,11.37129,11.27128
2A4Iain BurtVauxhall Astra11.8012.1011311.8811411.8211511.80120
A2Chris SuttonVauxhall Corsa11.9514.1911413.8811212.4612412.1511511.9511814.08112
A17Andy NichollsRover 200 Turbo12.0812.0811612.8211712.50119
1A4Iain BurtVauxhall Astra12.0812.0811212.5812312.4611612.5311712.2910512.18111
A4Iain BurtVauxhall Astra12.1313.5811512.6111712.1811012.4811512.1311112.29113
1A8Gareth BirchVauxhall Nova12.1412.1411612.769912.18119
A8Gareth BirchVauxhall Nova12.1912.9811412.3811012.4111513.009612.4611212.19114
1A7Andrew SpeckVauxhall Tigra12.2612.26131
A10Errol HuellHonda CRX Turbo12.3613.3211513.2912315.726912.3610822.3652
1A14Westan FrickerFiat Coupe Turbo12.7812.78110
A14Westan FrickerFiat Coupe Turbo12.8414.8411513.7411413.4011513.3011513.0511312.84109
View 9 Replies
View Related
Apr 23, 2008
I got a complicated macro that I need some help on. I want to write a macro that does the following (for clarity, I have attached an excel file).
I want to write a macro that automatically copies the rows in worksheet 'boekhouding' to their corresponding worksheet. The name of the worksheet, to which the data of each row has to be copied, is specified in row I (Beware: if it is not in row I, it is in row H). The names of all destination worksheets are given in the blue cells in worksheet ‘rekeningen’.
For example, row 2 (of worksheet 'boekhouding') should be copied to worksheet 'ABD'. Row 3 (of worksheet 'boekhouding') sheet be copied to sheet 'bestuur'. Row 4 (of worksheet 'boekhouding') should be copied to sheet 'Commissiekosten gala'. Etc for every datapoint (row) in worksheet 'boekhouding'. The number of datapoints will exceed 300, so manually doing this will be too time-consuming and unreliable.
View 14 Replies
View Related
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
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
Apr 15, 2014
I have two columns, left is called OD right is called ID. In each OD there are multiple ID's but this is shown for example as:
OD-----------ID
254000----0
254000----127000
285750----0
285750----127000
285750----158750
304800----0
304800----127000
Therefore, showing duplicates for the OD. The required format is:
254000---0---127000
285750---0---127000---158750
304800---0---127000
This shows each OD in the first column (no duplicates) and the corresponding ID's in the columns on the right. The example is done with small amount of numbers however, the data I am working with consists of thousands of rows therefore is not practical to do by hand. Please see attached spread sheet for example template with both unsorted and sorted data shown.
I attempted to do this by using the following code:
[Code] .....
Attached File : testing.xlsx
View 3 Replies
View Related
Sep 11, 2007
Following my bosses recent charting attempts involving multicoloured backgrounds, graduated bars, textured boxes, mis-matched fonts etc, etc, which frankly showed no information whatever, I was asked to simplify them.
I did so, as in the two attachments, but the response is now along the lines of "well, yes, but they aren't very exciting, are they?"
View 4 Replies
View Related
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
View Related
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
Sep 27, 2006
I've taken over a spreadsheet and been asked to produce various charts. One chart in particular asks for views of the following (hyperthetical) data from different workbooks on the same sheet
July 06 - 24%
June 06 - 22%
May 06 - 29%
April 06 - 21%
overlaid with this data
April 06 - 42
May 06 - 68
June 06 - 47
July 06 - 55
The problem I'm having is that firstly, the dates are presented the opposite way round, and secondly one set of data is in percentages, the other in basic integers. The spreadsheet data is large, and historically has always been done like this so it's not easy to change the way its presented, but is there an easy way to show it in a combination chart?
View 6 Replies
View Related
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
Jul 12, 2012
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.
View 9 Replies
View Related
Jun 6, 2009
I am trying to do is when a user enters in a GL (a 4 digit number) from the list (tab 2) I want it to appear in the summary box below. However the same GL number can be used multiple times so I will have duplicates. The data entered above will always change there is over 200 possiblities the user can enter. So I don't want the data to be specific it will populate based on what is entered by the user.
Can this even be done?????
Say if the user entered:
Column C Column G
4606 $20.00
4606 $20.00
2134 $15.00
2301 $35.00
4606 $100.00
The data in the GL and currency column in the summary box would appear as the following:
Column C Column G
2134 $15.00
2301 $35.00
4606 $140.00
View 3 Replies
View Related
Aug 22, 2014
I'm pretty new to Excel and trying to create a spreadsheet for a friend. For the part that I'm working on right now, I have a cell to track donations received for every day of the month for every month of the year. Donations can be split into three categories (Field trips, Speakers, and Misc), so next to the cell for the manually entered $ amount I have another cell that provides a drop down menu to select from these three categories.
What I would like to do is have three cells at the bottom of the page that will total the $ amounts for each category. I've attached the page that I'm referring to.
Attached File : Example.xlsx‎
View 3 Replies
View Related
Oct 27, 2009
The formula is in C4, and I am trying to sum the data when the value of B4 is matched in the table. see attached.
View 4 Replies
View Related
Jan 21, 2010
I’m trying to get the total number of participants in two different sets, with selection criteria attached to both (I’ve attached an excel file, as the example with those scoring +ve highlighted in yellow). The first set A (b1:b15) I want to select all values above 5, and the second B (c1:c15) set above 10. There other provisor, is that the A and B are further split into two groups (one above and one below 20).
In summary, I need to count all the A>5, B>10 (And both have to be above 20 in column D), but not to double count the ones that have Both A>5 and B>10. I don’t want any duplicates to be counted, so summing the total of A and adding it to B won’t work. In essence it’s an attempt to use the A Union B function that you’d observe when using venn diagrams.
Is there a simple way/formula of doing this?
View 13 Replies
View Related
Oct 5, 2013
How to sum numbers from multiple columns that match certain criteria.
My spreadsheet is a funeral team tracker and it's only the 1st day of the month, Each corresponding day will eventually have it's own tab once I nail down this first one.
Basically the page is laid out into 6 different details a team member can do. A team member gets their hours from the "Total Detail Time," that gets calculated from the show time til the return time. The team who worked the detail gets added to the bottom of the Detail they worked. I am just trying to total the "total detail time" each time a member works it. So, a member could work say Detail 1 and Detail 3, so I would just want to sum the total detail time from those two days where the member is shown.
I was trying to use =SUM(IF((Detail1,N13,B10)+(Detail2,N13,D10)+...
Note: I have range names for each bottom portion of the details to shorten the formula (i.e., Detail1, Detail2, etc.)
[URL] ...........
View 5 Replies
View Related
Jan 15, 2007
How do I edit the VBA so that the 1st data cell is selected, and then the program calculates the sum of the data values, and then stop summing when I reach end of data?
View 5 Replies
View Related
May 29, 2007
I'm currently using the formula in cell I43:
=AVERAGE(IF(I10:I420,I10:I42,""))
If any of these cells are blank I get the #DIV/0! error. If any are filled in then I get an average which is what I want. The problem is that I am trying to average mutiple sheets of these cells (I43) and if any sheet has this has the #div! error it won't calculate.
View 9 Replies
View Related
Nov 20, 2002
I have divided my data into 2 sheets. They are called 1) Sheet1 2)Sheet2
Sheet1 -> Sheet with all my Data
Sheet2 -> Sheet with calculations (formula's)
Know what I need to do is the following
1) As soon as someone starts enters a value in Sheet1 (eg. as soon as data is entered in Column A in Sheet1) VBA captures that line # and then in Sheet2 it copies the formula from the ROW ABOVE itself and then inserts a new ROW (corresponding to same row where value was entered in Sheet1) and copies that formula into that row (FORMULA are from COLUMNS B to J.
I need is Sheet2 to update itself and perform calculations when new line of data is started in Sheet1 and to INSERT a new Row so ALL other calculations BELOW it are updated as well. (Eg. Averages, Additional Calculations)
I have a lot of calculations in Sheet2 that correspond to Sheet1. The new ROW in Sheet2 has to look at where the data in Sheet1 was entered because I have a bunch of Averages at the bottom of Sheet2. I need all that to automatically update. So VBA has to insert a NEW row and then copy the previous row's formula in order for that to work.
View 9 Replies
View Related
Nov 27, 2008
I am having some problems converting a working formula in excel into VBA code. The working formula is as follows;
=SUMPRODUCT(--(ReasonList=$B51),--(AgeList=0),--(CreatedList>=$D$47),--(CreatedList
View 9 Replies
View Related
Jun 1, 2009
I want to sum certain data, which meets two conditions.
My data set contains three columns and a lot of rows. The columns are the following:
PostalCodeDeparture
PostalCodeArrival
PassengersInCar
I want to sum the total number of passengers with departure postal code 5100 and arrival postal code 5110. (and I want to do the same for all other postal code combinations in the data set)
With "SUMIF" I can only include one condition.
View 4 Replies
View Related
Nov 23, 2007
I have a set of data maintained in a number of tables within an Excel Worksheet.
All of the tables are the same layout and occupy the same Columns.
I want to summarise data from certains Rows within these tables at the bottom of the Worksheet.
Is there a better solution than simply doing something like?:
=A1+A11+A21+A31+A41
View 9 Replies
View Related
Jul 18, 2006
I have a lot of paperwork that needs to be entered into Excel. When I finsih entering the data, there will be many workbooks containing many different worksheets. I know how to summarize each workbooks but is there a way I can add specific columns in each of those summary sheets into a new workbook? I guess the question is can I sum data from different workbooks into a new workbook?
View 5 Replies
View Related
May 25, 2007
See attachment. For example, imagine data in cells A2:N25000 (obviously, this example in the attachment has been shortened). At this point, also imagine that rows 7 & 8 did not exist. So, there is a continuous stream of data for vaious counties in A2:N25000. For the purpose of context, the data contain mortgage information for all the lenders in a particular county for an entire US state.
Is it possible to create a macro that would insert two rows after each county in the data range (e.g., rows 7 & 8 in the attachment)? In other words, in the first row a macro would insert a row (row 7) that sums the results of the top 25 lenders in each county. Then, the macro would insert a second row (row 8) that sums the results of ALL the lenders in each county?
View 2 Replies
View Related
Apr 26, 2008
I have a set of data that I would like to break into groups, but I do not know what the groups are. I would like Excel to help me find the groups.
More specifically, my tax data consists of the following columns (I'm simplifying): parcel number, dollar value, tax amount, days late paid.
123435, $12000, $100, 20
234234, $23000, $230, 05
etc.
Of course my Excel "results" would omit the parcel numbers, but it would propose groups (and how many parcels in each group) such as: ...
View 6 Replies
View Related
