# Generating Average From Group Of Numbers In List That Match Two Criteria?

Jan 29, 2013
So we had a month long, company paid (woohoo!) "weight watchers" challenge. On 1 worksheet ("Stats"), I have the Name of every Employee (A Column), then their Start Weight (B Column), End Weight (C Column), Department (D Column) and finally Location (E Column).

I know how sensative some people can be about their weight, so I locked the page and created another worksheet named "UI", which will display the "Average End Weight" for each department. So Human Resources would be the department in cell A2 on the UI sheet, with City 1 being the Column Header in cell B1. There are 8 cities (offices) and 23 departments.

So, for cell B2, I want to scan through the "Stats" worksheet and locate all of the cells in the department and location columns that read "Human Resources" and "City 1" respectively. Then grab the "End Weight" for every row that meets these 2 criteria, add them up, and divide by the number of "End Weights" that were grabbed.

My solution would be to make a separate column for each department (and then each city, so essentially 8 columns to represent all the cities for each department), use a nested (maybe 4) IF statements to then list only the end weight if that particular row meets the criteria, then at the bottom of each separate column, add all the shown numbers up and divide, then draw the information from THAT number into the "Average" cell on the UI worksheet. But it isn't very dynamic and if i want to use this sheet next year, there will be more employee info to be added and it would be a mess..further more, it would be...what...23 Departments x 8 Cities x 155 employees = ~28,500 added cells.

View 7 Replies
ADVERTISEMENT
Feb 11, 2014

Here is my formula that does not return accurate results:

=IFERROR(SUM(SUMIFS(Tenure!$H:$H,Tenure!$E:$E,{"=1100","=1090"},Tenure!$C:$C,{"=12","=14","=13"},Tenure!$G:$G,"=1/1/2013",""})/SUM(COUNTIFS(Tenure!$E:$E,{"=1100","=1090"},Tenure!$C:$C,{"=12","=14","=13"},Tenure!$G:$G,"=1/1/2013",""}))),0)

I need to average column H from the sheet Tenure IF column E is 1100 or 1090 (formatted as text) and IF column C is equal to 12, 13, or 14 and IF column G is less than or equal to a date and column I if greater than or equal to a date OR if this column is blank.

I tried AVERAGEIFS, the above route and many others. For whatever reason, I have been unsuccessful, but close many times.

View 3 Replies
View Related
Aug 2, 2013

I have a sheet below, where I want to sum the value on a set of data on another sheet. The formula will be where the "???????" are. Each row on the other set of data will have a cell that's either 100, 200, or 300.... the row will also have a number that's either 1010, 1020, 1030, 1040, 1050, 1060... And there are many rows of data for each combination, thus why I want it summed up in the "???????" cells.

Type

Code

Item

100

200

300

A

1010

Cash

??????

??????

??????

[code]....

View 5 Replies
View Related
Feb 9, 2008

I have 7 cells containing strings but not numbers on a row.

Now I want to list out all the combinations of drawing out 3 cells out of these 7 cells while the remaining cells that haven't been drawn out could also be listed out one column next to the drawn cells.

For example, I got 7 cells like this.

A B C D E F G (each letter in ONE cell)

And I want to list out all the combinations like this:

ABC DEFG

ABD CEFG

ABE CDFG

ABF CDEG

etc.

For more details, please refer to the attached sample (an .xls file being zipped).

View 14 Replies
View Related
Jan 3, 2008

The attached workbook has two tabs:

1. Burn Rate - this is where I need my formula to calculate

2. prorder - this is where the table will be

What I need:

1) from 'Burn Rate', get the 'PO ID' we will look up in the table.

2) go to 'prorder' - when the 'PO ID' there matches that same 'PO ID' from the other worksheet - take the value in column F - this is the number that will be averaged.

In other words - in 'Burn Rate', for a given 'PO ID' (column A), I want to average all of the values that are found in column F in 'prorder', and return that average to 'Burn Rate' (column B).

View 4 Replies
View Related
Nov 10, 2011

I need to figure out a way to get an average for the numbers in column A, but ONLY those numbers in column A that have the number "1" in column B. example:

Column A Column B

3 5

6 1

4 1

The average would be "5", because the numbers 6 and 4 have "1" next to them in column B.

View 1 Replies
View Related
Apr 1, 2014

Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?

E.g i have this code

ActiveSheet.Range("$A$7:$N$31997").AutoFilter Field:=1, Criteria1:=Array( _

"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues

However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?

View 1 Replies
View Related
Nov 1, 2008

Hello, I have a column with 100 numbers in it. I want to be able to choose a number N and then calculate the average, minimum and maximum of the first N numbers in the list.

I have played around with countif and sumproduct but can't figure it. I don't know how index works, and wonder if that is the key.

View 13 Replies
View Related
Jun 25, 2008

I'm currently stuck with the following problem. I have a fairly large database with 10,000+ companies (in the rows) over 10 years (in the columns). I want to aggregate industry averages of a financial item in so-called SIC codes (there are approx. 1,000 different 4-digit SIC codes).

I've been using the daverage function, which works for one criterium (e.g. SIC = 0100) but not for the entire range of criteria. Does this mean I'll have to manually adjust every daverage function for every single SIC code? Or can this process be automated in some way?

Attached is a simplified version of my database file.

View 9 Replies
View Related
Oct 24, 2013

I need to generate 8 (random) numbers which should range between 0 to 80 and the sum of those generated 8 numbers should be 80.? How can I achieve this.

View 14 Replies
View Related
Feb 16, 2010

I am running Excel 2003. I have two columns of numbers (column A and column B)

column A has 1s and 0s. column B has latencies (ie. 100-500ms). When column A has a 0, i would like to know the latency of the following row in column B. from this i would like to average all of these latencies that are found. for example.

column A has 1,1,0,1,1.

column B has 100, 200, 300, 400, 500.

so since A3 is 0, then i want the latency of the following row in column B which is B4 or 400. and then average these findings.

View 3 Replies
View Related
Mar 13, 2007

I want to create a long list of 10,000 random numbers between -100 and +150, but do it so that the average of all the random numbers equals 20.

View 9 Replies
View Related
Aug 9, 2006

My store uses a retail price "code" wherein a price like 99 in expressed as a

code like "PP"--exchanging the number digits 1-0 as letters.

Is there a function to do this automatically?

View 9 Replies
View Related
Nov 23, 2006

I have been working on a project with 30 products and 20 different companies.

I am working on a business case, where there are launch dates for various companies with expected sales, contribution margin, NPV etc.

The idea is that I can create specific scenarios (What is Company1 doesn't launch the products, etc), and there is an accompanying each company. Because it can be difficult to see what products are included in the current calculations, I would like to use some kind of function that produces a list that matches my criteria. (In this case, all cells for a company that are larger than 0).

I would like to find a function that finds all products that have a launch date and create a list of them -- without spaces between non-matched results. And that it updates whenever data is changed (new launch year or new product in company's profile)

I have included a generic excel file to show what I am going for.

View 4 Replies
View Related
Nov 21, 2006

im given an area and im asked to generate 10000 random numbers. how can i?

View 2 Replies
View Related
Apr 9, 2014

I have 2 spreadsheets with many rows (in the tens of thousands) and columns (about 20 or so) of data.

I am trying to take the ID# from one spreadsheet and append it to the other, based on a name match. Typically I would use a vlookup, but, in this instance, most companies appear more than once and they often have many different ID#s associated with them. I am looking to return each unique ID# that is associated with each company, and display them horizontally next to that company's name.

Since this is including a lot of private data, I have included a small, generic example of what I am looking to search off of and output. Sample Spreadsheet.xlsx

I have seen examples of how to return multiple values using index functions, but, they all seem to only be able to handle one name at a time, and it displays the values vertically from that. Since I am trying to do this for thousands of rows of data, this won't work for me. I need to be able to have a function/formula of some sort that I can apply to each and every of the thousands of rows of data simultaneously. And, on top of that, I'd like to be able to display the 2nd, 3rd, etc ID#s for each company in additional columns of data.

View 3 Replies
View Related
Jul 26, 2013

How to generate semi-random numbers? I have a small sample (26 points) that I would like to extrapolate out and plot on a skewed bell curve. The numbers represent a ratio which can never be less than one. The average of my sample is 1.0149 and the standard deviation is 0.01234. When I use the random number generator tool in the data analysis pack, it generates mostly good numbers, but it also produces numbers like 0.98 and such that are never possible in my data. I've made a plot with the random numbers, including the sub-one ones, and then just adjusted the x-axis so that they wouldn't show on the graph, but I know that that's not the correct way to do it. My graph isn't skewed as much as it should be.

View 1 Replies
View Related
Nov 10, 2008

I wanted to generate 11 random numbers between 1 and 10, which added up to 100 every time I pressed F9...

View 9 Replies
View Related
May 19, 2014

Here is a code for me to print the names in ascending order even if values are of same means it is showing one name only instead of the other name

means if values are A=1 B=2 C=3 D=4 E=2

it has to show like this A B E C D

But the result is coming like this A B B C D

Formula which i used

=OFFSET(B$6,MATCH(SMALL(C$6:C$18,ROW()-ROW(C$6)+1),C$6:C$18,0)-1,0)test.xls

Here i am attaching the sample file for your reference

View 10 Replies
View Related
Mar 8, 2007

1. I need to randomly generate either a 1, 6 or 12 every time I hit calculate (i.e. F9).

2. I want to specify the probablility of each result. For example, I want 12 to be the result 80% of the time, and 6 and 1 to be the result 10% of the time.

View 9 Replies
View Related
Jan 6, 2009

use a vlookup with an IF function to display those unmatched record found in sheet1.

In sheet2, there are some new and old firms. Thus I need to separate them to process.

View 9 Replies
View Related
Feb 5, 2007

I have ten numbers (i.e. 1,2,3,4,5,6,7,8,9,10) and I'm trying to figure out how to make Excel generate a list of all the combinations possible for these 10 numbers. I'm trying to avoid using a VBA script. Is it possible to accomplish this task only using a function formula? This way I will have the ability to tweak the formula and test different qauntities of numbers (i.e. 1,2,3,4,5,6,7 or 44,56,77,33). Is getting a list of all possible combinations to output on a single column with commas between numbers possible with a formula?

View 5 Replies
View Related
Feb 19, 2014

I need generating 40 rows (Columns of 17 (Columns A - Q) Random numbers (1 to 5) that will sum to specific values (60, 55..) in R Column, and to regenerate it on pressing any key.

The same has been done for one value in [URL] ..... but I need it for a set.

View 5 Replies
View Related
Jun 25, 2012

I'm looking to generate correlated random unifrom numbers between 0 and 1 using the excel random number generator, rand(), and a user defined correlation matrix - without the use of a plugin. I realise there are plenty of risk plugins I could use, but was looking for something excel/vba reliant so I could distribute it without the user having to install anything.

I've tried using a cholesky matrix, but most of time it resulted in one of the numbers being 1, and higher that all the others.

View 2 Replies
View Related
Oct 20, 2009

I have a spreadsheet of 1000+ companies and I want to take a random sampling of 100 of them. I would assume this could be accomplished by auto-filling a numerical value to each line of data (which I did), then scrambling only the column with the numbers (which I can't figure out), and then selecting the entire text and resorting it to put the numbers back in order and then just work with the first 100 rows (which I can do, if I knew how to make the numbers random).

View 10 Replies
View Related
Jul 8, 2013

Let me explain, I am trying to auto generate a list. So I have one cell A1 with value "ABC"In another sheet,

I have a list of products in cells A:1 to A:100 and "ABC" is one of them, but is there 4 times. I have value in B:1 to B: 100

What I need is to create a list of all the values in Colum B that are related to "ABC"

Colum A Colum B

ABC 10

ABC 20

ABC Test

ABC TBD

Based on "ABC", I need a list to generate a list:

10

20

Test

TBD

I try Arrays and VLookup and Match and index but they are all single data return.

View 4 Replies
View Related
Mar 29, 2014

I would like to ask if there's a way to generate a random 4 characters in one cell and the specific random 4 characters that has been generated will be copied until the last cell of my preferred choice?

The format that I want is that:

yymmdd / random 4 characters that has been generated / 4 numbers that will increment sequentially

For example:

in A1: 140330QWER0001

in A2: 140330QWER0002

in A3: 140330QWER0003

.

.

.

.

.

But here's the catch, the file that I am using when closed then opened again will generate another random 4 characters with the same format. If i opened this file tomorrow:

for example the date for tomorrow is March 31, 2014, the file will do:

in A1: 140331TYUI0001

in A2: 140331TYUI0002

in A3: 140331TYUI0003

.

.

.

.

.

I tried to record it, the Rand() function when copied will generate another 4 characters.

View 2 Replies
View Related
May 15, 2013

I'm trying to use DGET to pull coordinate pairs from a list and match them up to a label based on certain criteria. I can't seem to get it to work. I'm also thinking that the way I have it set up, the coordinate pairs might not stay together and that has to be the case. My criteria is multiple cell based too and I don't think DGET can handle that.

For example, In order for a coordinate pair to be labeled "B" the x coordinate must satisfy a condition and the y coordinate must satisfy a different condition. Can I use DGET with the AND function?

View 6 Replies
View Related
Apr 27, 2007

I have a group of 8 numbers and I have to pick the 6 highest of them and make an average out of this 6.

How can I do that, using formulas?

View 6 Replies
View Related
May 2, 2006

I have a workbook where Sheet 1 Col A is a list of dates. Sheet 2 Col A is a relatively short list of constantly changing names. I need to put this list of names in a repeating fashion in Sheet 1 Col B. Ideally I would like for Sheet 1 to regenerate Col B each time the roster list is changed.

View 5 Replies
View Related