Build Average Formula Based On Changing Criteria
Oct 16, 2011
Objective: to create an average variance among exact match RevCode
Example: all RevCodes with "666" should be used to make an average variance statistic
Situation: All RevCode's are not in similar group counts and this creates incorrect averages with a standard formula.
Set of Data
RevCode Observed Expected Variance RevCodeMean
666 200 220 -9.091%
666 205 220 -6.818%
666 207 220 -5.909%
777 500 355 40.845%
767 505 505 0.000%
668 100 105 -4.762%
668 105 105 0.000%
Formula that generates average variance between exact match revenue codes:
=(SUM(B2:B4)-SUM(C2:C4))/SUM(C2:C4)
The solutions can be in VBA or within the spreadsheet either way.
View 1 Replies
ADVERTISEMENT
May 6, 2014
I have a schedule that covers 3 shifts for the entire month. I have another sheet that is used for crew sign in that I want to auto populate with all the people on that shift for that day.
I need to be able to use 2 different cells on the sign in sheet for reference for the formula (one for the day, and the other for the shift. ex. Day 3rd, shift D). I'm wanting to use an array formula to accomplish this. I've uploaded a stripped schedule as this is for the military and can't have any data on it that pertains to what or who it's for.
BTW our current method is to due it manually for every shift for every day that wastes about 12-16 man hours every week. I have excel 2010 at work currently. Access is out of the question and Macro's/vba may or may not work as security is always being increased on our systems so either a single or multiple formula is the route I'm looking to do that will work the the next few years I hope.
View 6 Replies
View Related
Aug 30, 2006
I have a list of 400 cells all in column A. Is there a way to have XL divide them up so that 100 are in column A, 100 in column B, etc., while still treating them like a single column (e.g. Sort will sort them all together)?
View 8 Replies
View Related
Aug 26, 2006
I have two columns. Column A has lots of repeated values, and column B is a list of numbers. For example:
A 1
A 3
A 4
A 2
A 6
B 1
B 1
B 2
B 1
C 3
C 3
C 2
C 4
Is there any way to output the following:
A 3.2
B 1.25
C 3
Where the second column are the averages. Any Excel function or VBA method will be fine.
View 9 Replies
View Related
Oct 18, 2006
i have a excel spreadsheet that contains point of person. Every week a person can or can not compete. That means that there is some days that is blank wouthout points. I want to get the latest 5 points of a person and then out of that 5 i want to get the top 3 point.
View 6 Replies
View Related
Feb 27, 2012
I have a series of numbers in the column AB56 going downwards and in AC56 going downwards their respective counts in a data-set.
What I would like to do is find the average of the min and max, the min and max for the average calculation are both determined by looking at the count column, isolating the max, isolating the min, provided they both have a count greater than zero then averaging both results.
eg.
0.1562818960
0.1558039690
0.1553260431
0.1548481172
0.15437019 2
0.1538922643
0.1534143384
0.1529364124
[code].....
In this calculation the MAX is 0.155326043 because it has a count greater than zero, the MIN would be 0.138120697 as it has a count greater than zero, the average of both would simply be 0.1467. i.e. Average(0.155326043,0.138120697)
View 2 Replies
View Related
Feb 29, 2012
I am trying to work out average for a particular area based on a criteria.
eg:
E F
Area time
A 01:00
B 02:15
A 01:15
C 01:16
B 01:30
C 03:15
In Cell
E100 - 103 I'm trying to work up the average of each area and using the following formula.
=AVERAGE(IF($E$4:$E$61="A",F4:F61,""))
I keep getting an the following error #value!.
i changed the average to median and still get the error.
View 2 Replies
View Related
Jul 29, 2008
I have a list of ages of people who cancelled their account. I'd like to find the average age of people within each month.
So column A = age
and column B = cancel date
Let's so for January 2006 I'd like to find the average age.
Logically this is how I see it...
Average Column A (If column B >= 1/1/2006 and < 2/1/2006)
View 9 Replies
View Related
Jul 29, 2006
I'm trying to find the best Macro or Formula to get this done.
Sheet 1 has a list of salesperson in column "A" and the total numbers they made on Monday (in the same row, column "E"), Tuesday (In the same row, column "F"), Wednesday (In the same row, column "G"), Thurday (In the same row, column "H"), and Friday (In the same row, column "I"). I also have a sheet for each individual salesperson. I need to find a Macro or a Formula that I can use to give me the average of the numbers if "A5" = the salesperson in Sheet 3 then avarage the numbers from "E5" to "I5" and I want the results to show up in cell "D35".
View 9 Replies
View Related
Nov 30, 2007
I am trying to get the average of two columns
column A and Column B
Column A has text (survey type) i.e. Move In, Mid Year, Year End
Column B has the scores given on that survey type i.e. 70
I am trying to get the average of of all the Move In cells from column A
for each survey type so that I get average of all the scores in column B.
View 3 Replies
View Related
Apr 24, 2014
I have 2 columns of data in Excel which I have brought a small section of it below. As can be seen the values in the left column have a large spike (difference of more than 10) at certain points (in this case at 34). I wan to find the average of the numbers in the right column but only till the point where the large spike happens (in this case the average of the first 5 numbers). I've tried AVERAGEIF but it's not what I want. I want this to repeat for the entire column and give me the averages of each of these groups.
10
32.4
11
38.6
12.5
23
[Code] .........
View 2 Replies
View Related
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 18, 2006
I need to monitor the average daily usage of a liquid tank for a customer. We fill this tank every few weeks. The formula I am looking for would ignore the fills and just count the daily usage.
View 9 Replies
View Related
Jan 14, 2013
I have some raw data, and in that data are 2 columns. They are Manager and Role. I am trying to create 2 lists, that correspond to their role. I can create a dynamic named range from the column no problem using this formula:
Formula:
[Code] .....
Now, what I need to be able to do is, add a criteria option to the formula. So build the list off of column B, but only if their role (in column C) is Perm, or Temp.
See the attached spreadsheet : dynamic range criteria.xlsx
View 8 Replies
View Related
Jul 9, 2014
I need an array formula that can take average based on multiple criteria present row wise and column wise.
Check the attached sheet for detail. I need array formulae in yellow space. I have given one result that I need.
Average.xlsx
View 13 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
Jul 8, 2014
The report that I am trying to build relies on DCOUNTA formulas.
This report has 200 lines and 200 columns approximately. Each of these cells would be a DCOUNTA formula. I have a separate sheet, 'Criteria', where I intend to describe all of my criteria for the DCOUNTA formulas.
My problem is that for each DCOUNTA cell in my report, I have to create 2 rows and 5 columns worth of criteria information... so, for 40,000 cells to populate, I need to create a huge sheet of criteria.
So, while I could do the above, and it would take me some time, more efficient and elegant way to spend my time in order to get this report display DCOUNTA formulas?
For more context, I am trying to present information for 100 individual salespeople, monthly and year to date. For each cell, I need to tell DCOUNTA that the date needs to be comprised in the current period (2 columns), what the name of the salespeople is, and what the nature of the information is, which could be more than 2 additional columns.
I can to some degree autofill most of this, with some OFFSET magic, but its still feels like an ugly way to achieve my objective.
View 9 Replies
View Related
Oct 30, 2013
I am using Excel 2010.
BTES worksheet:
acct# Oct-13 Nov-13 Dec-13
13245 850
12458 850
45864
12385 1500
MainList - F2= (current month)
Based on the current month (which I planned on typing in) on the MainList worksheet I need to count how many blank cells are in the table above. The information above is on a separate worksheet but I can change that in the formula. Every month gets a new column.
This is what I have but its not working correctly: =SUMPRODUCT(--(BTES!AD1:BQ18=F2),--(BTES!AD1:BQ18" "))
View 2 Replies
View Related
May 17, 2014
how to build a formula that would allow my remaining UOM and it's associated cells to move up to the days remaining UOM once my time goes below, say 31 days.
View 3 Replies
View Related
Feb 24, 2013
I write a excel file (with macros included) to tackle the datas regarding stock market.
The excel file encompasses some information about financial reports, and some functions (macro) like sorting and filtering.
I'm wondering if I can build it as an windows 8 App and put it on the App market.
Or, in other words, can I make a windows 8 App by the functions in Excel ?
Can I do that ?
View 1 Replies
View Related
Jul 14, 2009
Im creating a spreadsheet at work, which has a drop down box with the options of open and closed in it.
I also have a column for length of time of the claim, what i need to do, is now find a way of getting a average length of a claim, but only to see the closed option from the drop down box.
for example,
row 1
open date - 02/06, closed date - 01/07, dropdown box - closed, life - 29
row 2
open date - 10/06, closed date - n/a, dropdown box - open, life - -39400
as you can see from the example above, i cant simply do a average of the whole column, as would read the open ones as well. so is there a way of doing the formula, but telling it to only see the open, or closed ones?
View 12 Replies
View Related
Mar 6, 2014
i need a macro which puts the formulas into the cells as per attached.
the number of rows between the "beam" can be variable
the number next to the beam will be variable.
the number of "beam" rows is vaiable
my thoughts where to do a find"beam" and refernce the cells address the create formulas off those points but dont have the skill to code this
beam macro.xlsx
View 2 Replies
View Related
Apr 15, 2014
I'm using this formula to count how many times the date in column C and the text "WON" appears in column I and it falls within the month & year that is in Z65.
=COUNTIFS(INDEX(C:C,$AL$15):INDEX(C:C,$AL$17),">="&DATE(YEAR(Z65),MONTH(Z65),1),INDEX(C:C,$AL$15):INDEX(C:C,$AL$17),"
View 2 Replies
View Related
Apr 6, 2013
I need to run a loop to look up several columns.
My problem is that I need to build a formula that can sum each value in this row from the columns found when I'm done looping.
And then I was planning to just use "AutoFill", because of the large amount of rows this procedure has to cover.
Can I somehow create a code that will put each column number in memory, and then when done create formula like = "=SUM(G3, I3, AF3, BB3)" or something like "=SUM(& "i1" & 3, & "i2" & 3.... etc etc
I'm a little worried for overdoing loops as they have a tendency to slow down the speed.
View 4 Replies
View Related
Jul 31, 2008
I created a contact list that incorporates buttons at the top of the page to sort the data based on two customer type columns (thanks to Richard for help getting this far!).
I am attaching the file so that it will be easier to take a look. Four of the five macros are working fine but the second one ("All Clients") filters on the premise that "yes" is answered in either of the two customer type columns. In other words I am trying to show the records that have "yes" entered in either of these two columns, not necessarily both columns.
View 9 Replies
View Related
Oct 16, 2008
I have a worksheet with three columns. First Name, Last Name and email address. I need to combine the first name with the last name and add our @ourcompany.com to the end of it for the emaill address.
I've figured out how to do that, but my problem is, we don't use our first and last names in the email address. We use first initial and last name.
I know how to make Tom Jones become tomjones@ourcompany.com
What I need to know is how to make Tom Jones become tjones@ourcompany.com.
Can someone point me in the right direction here? I just need the first initial of the first name and combine that with the last name and then add in the @ourcompany.com.
View 3 Replies
View Related
Jan 13, 2008
I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...
View 12 Replies
View Related
Oct 3, 2006
Need to write a formula that would assign a letter grade (in row W) to the average in row V, based on the school's scale. So, row V contains my averages (in % form) and I want row W to have the letter equivalents.
93+=A
85-92=B
75-84=C
67-74=D
<66=F
View 2 Replies
View Related
Aug 5, 2014
I'm trying to build an Index-Match formula to retrieve a number from a "data" sheet onto a "summary" sheet.
In example spreadsheet, I need to retrieve the stock price on a specific row from "data" sheet, and as an alternative, the most recent price.
View 6 Replies
View Related
Apr 28, 2007
I'm trying to do at the moment is build a user interface for some Excel Data. Now, the natural way to do this would be to build a pivottable and let users move the fields around, and this is how the thing has worked in previous iterations of the workbook. It seems now, hoever, that we're trying to cater for users for whom pivot tables are just a little too much and to summaries data based on listboxes etc.
What I'm interested in knowing is whether there are ways of interacting with PivotTables beyond the GETPIVOTDATA function - is there any way, for example, to select different field items to show based upon an input from elsewhere? How does one tell a pivottable what to do from outside the pivottable?
View 3 Replies
View Related
