Conditional Summing: Find Those Combinations Of Variable Values Which Generate Highest Total Gain

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


ADVERTISEMENT

How To Generate All Combinations Of Ten Values List / Picking In 4 Each Combination

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

Summing Variable Values Across Variable Sheets In Multiple Columns

Jun 27, 2014

I need a macro that will create a sheet at the end of the workbook.

Sum data from a variable amount of sheets and display that data on the created sheet.

Here is a step by step:

Starting on sheet 5.

Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.

Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.

(Note: The data would also need started on row 4. Everything above row 4 is headers)

Here is a small example:

D E T U V
13019090W Part A1
68705500 Part B1
64202900 Part C-11
59634600 Part D1
26005300W Part E1

I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).

After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.

So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).

View 2 Replies View Related

Generate All Possible Combinations

Dec 28, 2006

Is there an Excel Addin that will let me figure out the total number of combinations for any size number pool I specify (39,49, 53) and with any combination quantity I specify (3, 4, 5, 6)? And most important is that all these combinations be displayed in a column separated by commas.

Is a VBA script designed to handle something this complex.

View 9 Replies View Related

Equation To Find Top 2 Highest Values?

Mar 8, 2014

I have the following 2 formulas that determine the top 2 scores and return the respective headers, but they aren't perfect.

=INDEX(T1:V2,1,MATCH(MAX(T2:V2),T2:V2,0))

The above returns the max, however doesn't take into account scores that are a tie or 0 values

=INDEX(T1:V2,1,MATCH(LARGE(T2:V2,2),T2:V2,0))

The above returns the 2nd highest value, but some instances the output is correct (if there is a tie or a 0).

How can I fix both of these equations and also determine the 3rd highest score?

View 5 Replies View Related

Find Highest Value Out Of Multiple Values

May 12, 2009

I have 2 columns, A (has the names of employees) and B (has the month in which the employee has a project scheduled). What I’m looking to do is find the latest month an employee has a project scheduled. Note: Employees can have a multiple number of projects so they may be listed multiple times with corresponding months.

I have each employee listed in column F and am looking to find their latest project month in column G. (I have the number that the formula should return in column H).

View 3 Replies View Related

Generate All Combinations From 2 Columns NOT Permutations

Feb 14, 2013

I have two columns of data in a worksheet, and I need a third column that shows all of the possible combinations of the data in those two columns. For example:

A
A
AA

B
B
AB

[Code] .....

I don't need permutations so, for example, the third column should only give the combination "AB" and exclude "BA".

View 4 Replies View Related

Generate Some Combinations For Specific Sum From Given Dataset

Mar 4, 2014

I have a code that generate some combinations for a specific sum from a given data set. This code has two errors.

1. code does not look into data. it generates
2. Code generate those combinations for which sum is greater than target value.

E.g. first combination code generates is 3+7+10+12+12

This wrong by two means sum is

1) 44 while we need 40
2) 12 is not in data.

View 9 Replies View Related

Find The Loss/gain Difference Between An Initial Value

Aug 27, 2007

I am looking for a simple loss/gain formula for my students. They are trying to find the loss/gain difference between an initial value, and the current value. They are using a monetary value, not date/time.

View 5 Replies View Related

Generate List Of Combinations - 20 Questions With 3 Answers

Feb 26, 2011

How to generate a list of numbers of this function in Excel: COMBIN(20,3), but i want the combinations of numbers. I want to create a list of 20 questions and every question has 3 answer. I think that are 1140 combinations.

View 9 Replies View Related

Generate All Combinations 3 Digit Number Produces?

Aug 3, 2013

How can I generate all the combinations a 3 digit number produces? Example 123 can be written as the following:

123
321
213
312
132
231

I THINK this is all the combinations it will produce.

View 3 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

Combinations That Sum To A Given Total?

Feb 26, 2014

I have up to 30 cells each with an integral value from 1 to 5. The task is to list all the ways that selections of from 1 to 5 cells can be made from the list such that the value of the sum of the selection is 5. It will usually be the case that there will be a final selection that will not sum to 5 and this needs listing also.

I am aware of coding that can list out combinations, but not in a form that can be adapted to deal with the summing limitation. I hope that I will be able to code so that I can deal with different numbers of cells and different sum values.

View 1 Replies View Related

Find Combination Of Cell Values Equaling A Specified Total

Sep 30, 2006

I guess this would work in a similar manner as Solver, but where Solver tweaks cell values to equal a given total, I've got a set of cells and I need to find the combination that equals an amount in another cell. Is there a way to do this?

View 7 Replies View Related

Find Lowest 5 Numbers In Column A With Highest Values In Column B

Oct 5, 2013

I am looking for a formula that will find the lowest 5 number group in column A with a total value from column B under $100 in the below table.

1 $28.75
2 $28.00
3 $27.75
4 $24.75
5 $25.50
6 $25.25
7 $25.25
8 $16.50
9 $24.75
10 $26.50
11 $24.50
12 $27.00
13 $26.50
14 $23.50
15 $23.00
16 $19.25
17 $20.75
18 $25.00
19 $20.50
20 $20.50
21 $17.75
22 $22.50
23 $20.50
24 $19.75
25 $20.00
26 $10.75
27 $11.25
28 $5.25
29 $13.00
30 $9.00

View 3 Replies View Related

VBA Extract Highest And Second Highest Values

Nov 27, 2013

I'm attempting to put together a script which in simple terms finds the 'Highest and Second' highest activity and associated FTE for a given sub group.

To be more specific:

On the "Slide 1" sheet look at the values in column J starting at row 7 until blank;For each value, then search the "All Monthly Direct Activities" sheet in column C for the same value, excluding the 'Subtotal' rows;Where a match is found, compare all the values in column E for that sub group;And Find the highest and second highest figure;

Once these are found copy this figure and associated value in column B and paste onto the "slide 1" sheet.

I'm not particularly well versed in writing VB, but I've been working with the code to see if I can get this work, which I have been unable to do.

I have attached a file which may perhaps provide a greater detail than my description of the problem containing the 'Source' and 'Destination' sheets.

View 9 Replies View Related

Adjacent Cells - Search For Specific Values And Find The Total Number Of Instances

Jan 30, 2014

Any method to determine the number of instances a value ("4" for example) appears next to a specific value ("x" for example). I don't have a workbook; I'm just looking for any formula that can search for specific values and find the total number of instances that an adjoining cell contains data.

View 14 Replies View Related

Summing The Total $ Between Months

May 5, 2014

In the example spreadsheet I have attached/linked below contains a formula on the Cash Flow sheet in cell D4.

I have this formula working perfectly when instructing it to SUM all the ($) values for a specified month that is located on the Cash Flow sheet in cell B4 (Apr-13) and then it looks for all the matching criteria related ($) values that's located on the Trend Log sheet in column D. It then returns back the total exactly like it's supposed too.

However, when I want this same my formula that's located on the Cash Flow sheet in cell D4 to give me the SUM between the month in B4 (Apr-13) and the month in C4 (Aug-13), it won't work no matter what I try.

So I went back to the formula version I had that works with the one month in B4 (Apr-13) so you choose to you would have at least a good starting point. That is if you don't want to just delete the whole formula in favor of a better formula.

Sum Between Date Range Example.xlsm

View 14 Replies View Related

Summing Total Per Customer For Each Month

Nov 4, 2009

Please see the attached example.

I am working with Office 97 and I know that if I had Office 2007 this question could be easily answered with the "SUMIFS" function. But since Office 97 doesn't have that formula I need to find another way to accomplish what i am trying to do.

In the gray boxes I need a formula that will sum the amounts for each customer for each month. For example, the total amount that shipped to customer A in October is 180.4.

I will be applying this to a much larger amount of data, so please do not provide a formula that only works for the data supplied in this example. I will be continuously adding more info to my list over time and I want the count to automaticaly include the data when I enter it.

View 13 Replies View Related

Summing Total With Push Button

Nov 6, 2009

I have an Excel 2003 spreadsheet with pupil test scores and would like to automate the total. Example attached.

There are N tabs (one for each class; there is one or more class in each year group) of raw data; all the scores are numerical but some entries may be blank. After entering the raw data I would like to press a button on each “Total” tab to collate the data.

Total A presents the data in class order; the column marked Class should just say A1, A2, B, etc. If there are multiple classes in a year group then they should appear in order, that is, A1 followed by A2 followed by B, so on.

Total B presents all the data in ascending surname order.

View 14 Replies View Related

Summing Daily Total Over / Short

Nov 29, 2013

A
B
C
D
E
F

1
DATE
MANIFEST
P'work lbs
Scale lbs
over/short

2
1/1/2001
1
1000
1001
1 (formula)

[Code] ..........

I would like a formula to put in column F that will total the daily over/short.

I would like to place this formula in every row in Column F, but return empty "" if the next row is not empty, if however the next row is empty total the over/short column for the day. Keep in mind that a zero value in column E is not empty.

View 9 Replies View Related

FREQUENCY Formula - Get Total Highest Intervals Of Zeros In A Range?

Jun 21, 2013

I use the following CSA formula to give me the total highest intervals of zeros in a range. I understand how most of it works apart from one thing and that's how the data array and bins array give me the result.

=MAX(FREQUENCY(IF(H2:Y2=0,COLUMN(H2:Y2)),IF(H2:Y2>0,COLUMN(H2:Y2))))

So this is what I understand:

H2:Y2=0 - Returns TRUE/FALSE in the array based on range having 0 or a value other than that - ie{TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE}

COLUMN(H2:Y2) - Returns the column number {8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25}

IF(H2:Y2=0,COLUMN(H2:Y2)) -Returns the column number for every TRUE value

IF(H2:Y2>0,COLUMN(H2:Y2)) - This does the same as the above steps but returns column numbers for every cell that contains a value greater than 0

MAX - Returns the biggest number within the array ie =MAX({3;0;0;0;0;0;9}) would return 9

What I need to know is how the FREQUENCY formula gives {3;0;0;0;0;0;9}

View 3 Replies View Related

How To Use Variation Of COUNTIF Function To Generate A Total Number Of Occurrences

Feb 21, 2014

I'm trying to use a variation of the COUNTIF function to generate a total number of occurrences. The RANGE is a singular column on each of five tabs in a spreadsheet, but the CRITERIA (hopefully) is made up of two separate cells on one tab of the same spreadsheet. Here is the formula as I have it written:

=if(A2="","-",sum(countif('Monday-No Shows'!D:D,or("*" & A2 & "*","8),countif('Tuesday-No Shows'!D:D,"*" & A2 & "*"),countif('Wednesday-No Shows'!D:D,"*" & A2 & "*"),countif('Thursday-No Shows'!D:D,"*" & A2 & "*"),countif('Friday-No Shows'!D:D,"*" & A2 & "*")))

The purpose of this formula is to check to see how many times a student does not show up for an appointment over the course of their time with us. We have one tab with all the pertinent info on each student and I'd like to use this formula for a "No Show" column to more easily know when to cancel a student's services. The problem is that I can only get the function to search for one criteria right now. I've tried using the OR function and adding it to the current function but it always gives me 0 as the result, but if I take out the OR it returns me a higher number. Here's what it looks like with the OR added:

=if(A2="","-",sum(countif('Monday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Tuesday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Wednesday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Thursday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Friday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*"))))

A2 designates the students first name and B2 designates their last name. If I eliminate the OR and resort to multiple COUNTIF's, it will generate twice the number of responses that I want, I fear.

Example.xlsx

View 6 Replies View Related

Find Values Of A Conditional Format?

Aug 21, 2012

I have a long list of users and the hours they have accumulated over the past year of use. I have used a Conditional Format to find the top 10 users. I want to create a chart of just those 10 users so I was hoping there is a way to pull the value and cell of the users name by only pulling the cell values that show up in green.

Is there a way to have excel basically find the formatted cells and list the values? Or are there other ways to list just the top 10? These top 10 users is dynamic and can change throughout the year so the top 10 now, may not be the top 10 tomorrow.

Here is my entire excel spreadsheet... its to hard to split up since there is so much data being pulled from the HoursChart for the year tab.

Top 10 users.xlsx

Click on "Top 10 users" Tab, there you can see I have all the users listed and their hours from the sheet prior. I also have a condtional format in place on hte values.... I need a way to either pull those values into another cell or I need to chart only the cells that have the conditional format... however I need the names and the departments along with them.

View 6 Replies View Related

Variable Selection To Return Highest Value

Dec 12, 2011

If the weight selected is between the ranges e.g 1.5kg for England so that it returns the higher value 10.69. At the moment if I put in the vlookup formula it returns the lower value of 9.60.

Weight KGEnglandScotlandWales0.59.109.109.101.09.609.109.702.010.6910.6910.693.012.2812.2812.28

View 4 Replies View Related

Conditional Formatting To Find Non Unique Values

Dec 14, 2009

I need to find rows in my large worksheet that have 2 vendor names for a single vendor ID. Each vendor should only have 1 vendor ID.

Is there a way to highlight rows that have a single vendor ID for more than 1 vendor name?

View 9 Replies View Related

Find Possible Combinations

Feb 11, 2009

I need to find possible combinations for the following example:

a*x + b*y + c*z = t
p = 10

Where
a = 0
b = 5
c = 10
t = 30

x + y + z has to = p

a, b, c, t, and p would be variables that I would specify. The numbers above are just examples. What I would need to see as output would be:

7a, 0b, 3c
4a, 6b, 0c
5a, 4b, 1c
etc...

View 8 Replies View Related

Find All Possible Combinations Below Certain Value?

Feb 3, 2014

I have a simple list of names with associated numerical values.

I would like to create a formula that will produce a series of all possible series of 8 of those names whose combined numerical values are at or below a certain threshold (or between two thresholds, if that's easy). Bonus points if the total combined value for each group is easily shown, but that'd just be gravy.

View 9 Replies View Related

Find Best Average From Combinations?

Jun 25, 2014

I have the following Data:

Full Drum
247

Full Drum
247

Full Drum
261

Full Drum
261

[code]....

I need to know how to find the best combination of combining these drums to get the highest average from each combination. Each combo will have 3 drums except for one which will have two and a half.

View 2 Replies View Related

How To Generate Brand Name From Part Decsription Variable

Jan 6, 2008

I have following part number , And i like to know how to write code to pick the brand name.

Left col is the data i have and code that generate the brand name....

View 9 Replies View Related







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