Create Average Based On Grouping Of 3 Columns
Mar 22, 2013
If my spreadsheet has columns A-H.
A is Date
B is Room Number
C is Location
D,E,F all have numbers results in them.
G is the average of D,E,F for each person
H is group average.
If I want to define a group average in column H where it calculates the average of a group that attended the same location, date and room, for each group that attended would that be possible?
So For Example (Columns DEF left out for clarity)
Date
Rm
Location
G Inidivual Average
[Code]....
So because room number changed it calculated as a different group. Is this possible?
View 3 Replies
ADVERTISEMENT
Feb 26, 2008
=SUMPRODUCT(--($A$2:$A$12=$D$24),--($F$2:$F$12=$B$25),($H$2:$H$12))
This is the current formula I have, however I am working with averages. After the first two arguements are done I want the overall average of columun H.
View 9 Replies
View Related
Apr 16, 2014
I am trying to create a rolling 30 day average based on a date in a particular cell. I have 62 columns that I am looking at. We can call A1 7/1/14 and the 62nd column 8/31/14 for simplicity sake. I want to grab 30 values in row 2 as these columns are filled in daily so 8/1/14 takes the average of 7/2/14-8/31. On 8/5, I want 7/6-8/4 so on and so forth. The average that I need has to include at least one averageif stating that only nonzero "<>0" figures should be tabulated (just have business day transaction data but need all dates listed to tabulate 30 days). The range of dates will be fixed every month and info copied to tabulate on the first day.
View 1 Replies
View Related
Jul 29, 2014
I would like to average a column based upon another column value. Namely, if one column is a duplicate, I would like to take the column to its right value and average all the duplicates. For example
comp 1409 99.7
comp 1409 99.5
comp 1409 100
comp 1308 65.7
comp 1509 99.5
I would like that to become
comp 1409 99.73
comp 1308 65.7
comp 1509 99.5
View 6 Replies
View Related
Dec 30, 2008
I am trying to include numbers in an average if two separate columns prove true. For example, I want to include all numbers in column "C" in the average if the cell in column "A" matches a name and the cell in column "B" is equal to the number 1.
View 2 Replies
View Related
Jan 23, 2014
I have three columns of data, one containing numerical values. I want to average the numbers based on the other two columns of data. Attached is an example file with the desired output I'd like. What is a formula that will do this?
AverageExample.xlsx
View 7 Replies
View Related
Aug 1, 2014
It's probably best if you open up the attached example spreadsheet that contains sheets "Before" and "After" otherwise it might be pretty difficult to understand what I'm talking about.
So I have this spreadsheet where I have part numbers in column B, status of the orders in Column I and lead times in Column M. What I want to achieve is to get of Mode (col N), Median (col O) and Average (Col P) values of each part number and each of its status using the lead times in Column M. If possible I would only like this on the top lines of each of these. I have done the formulas manually on sheet After so if you click on the formula you can see the range's it covers.
I did create a concatenation in column K (conc of column B and I) as helper column because in theory this might make things a little bit easier.
Now going into second part. I would also like to do something very similar but only using the part number and status "Finished" and get the values in columns T,U, and V on the top row of each part number. I made some blue boxes and arrows to kind of point out the idea.
The solution can be either formula based or macro, it really doesn't matter, however I will intent to use it within macro either way.
View 2 Replies
View Related
Nov 14, 2009
I need to create a report based on three columns. I have explained in detail about my requirement in the attached sample workbook.
I hope someone will help me to solve the problem through VBA because the items & expenses may vary at times. Hence, I feel a Macro will be the best solution.
View 14 Replies
View Related
Jul 27, 2013
[URL] ......
In that file, you can see there is a list of somewhat similar data across 5 columns. Some of the entries are duplicates, some are different by only one character. What I want to be able to do is generate a list of all the unique rows of data, taking into account the data from ALL 5 columns. The list can be on another sheet or simply just further down on the same sheet, that doesn't matter too much.
I could do it if it were just one column of data, but struggling trying to do it with several columns.
View 7 Replies
View Related
Apr 23, 2008
I have a small sheet that has a number in Column A and a larger number in Column B along with some data in Columns C and D. I wish to make a separate sheet that has rows numbered using the numerical range from Column A and Column B along with the data from Columns C & D
View 2 Replies
View Related
May 2, 2008
I have been perusing various Excel sites in hopes of finding a macro or function that can create columns based upon two dates input into specific cells. My ultimate goal is to use this macro or automated function so that when the user inputs the dates, the columns would automatically be created for them, rather than having them do it manually.
ideally, the 'start' and 'finish' dates are named cells and the macro could determine the difference between the two and automatically insert columns to the left of a ' Totals' column.
View 9 Replies
View Related
Nov 1, 2011
I have spent a good amount of time trying to create the below using botched IF functions, SUMIFS, SUMIF formulas etc.
Basically I have a sheet (called Sheet1) with the following columns:
A - Date (by individual day DD/MM/YYYY)
B - A catagory (so using the old example, Apples, Orange, Pears)
C - Price.
I would like to be able to create a formula, whereby I can sum the Total sales for each catagory across each month onto a summary sheet (sheet2).
I would use pivot tables, however am not sure how i can make all the require data appear permanently in the best way, especially if there is a one liner that can be done more easily. So far I have managed to create a sum dependent on month, but have been unable to encorporate the catagory.
View 1 Replies
View Related
Aug 4, 2014
I receive a sheet that has forecasts by part number by week in columns. I don't care about the weekly total, I just want to see it by month. I inserted a row using a "=MID" function to remove date and year so all I have left is the month (see attached in row 2). How can I group horizontally by month?
example.xlsx
View 2 Replies
View Related
Jul 21, 2012
create a macro that will find and calculate based on the details of 4 columns on the same spreadsheet.
Basically, here is what I need to do:
1. Need to calculate how many QTY IN (Column F) and PALLETS IN (Column I) of a specific PART# (Column A) and LOT CODE (Column B) For example if enter a PART # and LOT CODE, it will calculate how many QTY IN and PALLET IN of that PART # and LOT CODE.
2. Need to do the same for QTY OUT (Column G) and PALLETS OUT (Column J) also.
3. Output should contain the PALLET # , QTY IN and PALLETS IN based on the PART # and LOT CODE search. I just want to simplify the search function in this spreadsheet because it is very cumbersome if you need to find how many items left for that PART # and LOT CODE.
Attached is the spreadsheet..
View 9 Replies
View Related
Apr 1, 2014
I'm building a sales tracking spreadsheet and need to find a way to auto group certain rows based on their cell value. In my tracker I'd like to group by company name and be able to collapse it to look at the details.
Here's an example of my starting point and how I'd like the data to look like in the end.
Starting State:
Company Name contact name sales stage Forcast amount
----------------------------------------------------------------------------------------------
Company A david brown contracts 10,000
Company A John Smith Won 20,000
Company A D'Arcy Davis Canceled 10,000
Company B
Company B
Desired State:
Starting State:
Company Name contact name sales stage Forcast amount
----------------------------------------------------------------------------------------------
+Company A 30,000
+Company B
View 1 Replies
View Related
Jun 5, 2012
I have a permutation with repeatition of 3 letters in 9 digits 3^9, and I'd like to be able to group all posibble permutations something like:
BBBBBB+2+1 = BBBBBBCCD = 252 rows
BBBB +3+2 = BBBBCCCDD = 1260 rows
5+4
5+3+1 and so on
View 2 Replies
View Related
Nov 19, 2012
I have situation where my account (from trail balance) will present to different grouping based on reporting, example below. How I can formulaize or using VBA to sum to amount based on the format.
Example :
if i want the format b : if i sumif HCM - the total should be from account code a1001,a1002,a1003 and hrd will sum up based from account code a1004,a1005,a1006
Account Code
format a
Format b
format c
format d
format e
a1001
adm
hcm
adm
adm
hrd
[Code] ........
View 7 Replies
View Related
Oct 21, 2013
I am working with a large data set containing information about certain items
The items are structured so that they all have seven main sections. Within each main section their is a variable number of parts, that the items are made of.
The items uses only one part from each main section. So all items consists of seven (identical) main sections and seven parts.
I am looking to group items togheter which are precisely identical. I mean the items that uses the same part in all the main sections.
Is there an Excel/math wizard out there? I have all the data, but I can't make Excel do this task for me..
View 8 Replies
View Related
May 1, 2014
i need to group the values based on A, B, C columns.
which means all "Chickpea_B-1_11area_3d.csv " will have same timestamp and mean_area_3d values, only D column vlaue changes.....
in the same way, all "Chickpea_B-1_12area_3d.csv" will have same timestamp and mean_area_3d values, only D column vlaue changes.....
now i need all Chickpea_B-1_11area_3d.csv values to be grouped into one. and chickpea_B-1_12area_3d.csv values to be grouped into one..
if you see the output sheet, you will understand clearly, which i did manually for your easy understanding...
i did manually and also attached the sheet for your kind reference and easy understanding... where i grouped all Chickpea_B-1_11_area_3daver_domain_area, fill_factor, leaf_angle_theta, plantHeight, projected_leaf_a into one.... where b and c cols are same for all the D E F G H I Values. in the same way for Chickpea_B-1_12_area_3d and all other below values to be grouped.
like this i have to do for 1000 files. please find the attachment...
View 12 Replies
View Related
Jul 7, 2009
I have a pivot table (vendor spend report) which has over 7000 rows of data. There are 38 vendors (some vendors appear multiple times i.e. Accenture Itay, Accenture Germany, etc.). Vendors names are in column B.
I need to have the VBA script search for each of the vendors (and related names) and group them together. Also, last one is for all the MISC. vendors which need to be group together (one's that dont belong to the 38 managed vendors).
Also, I need to rename the Group for each one; rather than Group1, I need to name it "Accenture All" etc...
View 7 Replies
View Related
Mar 18, 2012
I have a date field 08/09/2011 and want to monitor an item to ensure it's "Approved" before it reaches this date.
The groups are:
Late = this date or past
Within 1 week
Within 2 weeks
Within 3 weeks
Over 3 weeks
How to do this, I tried Nested, and I've tried lookup but I'm not sure how to tackle it.
View 5 Replies
View Related
May 27, 2014
Attach below are 2 sample file and inside it consist of device,grouping and lb1,tb1,ab1. How do I use excel formulae based on 2 condition and set from vlookup with 2 condition file to vlookup with 2 condition 1 file like a vlookup.
View 11 Replies
View Related
Mar 27, 2014
Essentially what I want, is to type a medication in, lets say, A2, and for a macro to produce a response in B2 based off of grouping criteria from another worksheet. My workbook better explains what I would like to see happen. A macro would be best for this, right?
View 7 Replies
View Related
Apr 28, 2014
I need a VBA macro code to get the output like in the excel file which I have attached with this thread. Which means, I need to group the data for every two hours. in the output I need all 4 cols namely A, B, C, D along with extra col namely, interval based on which I need these groupings to be done..
The code has to be really flexible so that it works for all dates and times in the files. Because like this I have to do for 2000 files.....
Original link: [URL] .....
plant area.xlsx
View 3 Replies
View Related
Dec 1, 2013
The attached file shows what I'm trying to do - I'd like to fill in the data in the top left corner table (blue-shaded) with the data combined from the two other tables. Think of the Greek letters as names that are grouped into the groups named {A,B,C,D,E} (the names have no significance, it's just an example).
I was able to do so using an array formula and an extra column (yellow shaded) as you can see in B2 - however, this solution doesn't suite my case because the actual tables will be coming from automated processes and cannot be easily manually altered.
I tried inserting the array formula into the sumproduct formula - see B3 - but for some reason this doesn't behave the same as when the array is outside the formula. I should also mention that I came across a user defined function solution - this too is not so suitable for me right now.
View 9 Replies
View Related
Apr 26, 2014
i am trying to complete a spreadsheet which most of it is done but one column i have i have to average everyother square in each column which i did but when i total those columns at the end i get the dreaded #div/01 which means any value that i dont have in a particular column it will not add is there anyway to fix this. i am attaching a sample of the spreadsheet so you can see exactly what i am trying to do .
View 6 Replies
View Related
Aug 20, 2008
I'm having difficulty understanding this concept, more so the formula.
So to figure out the MTD for Day 1,2,3,4, how would it be?
I want each day to have a MTD
etc.
Day 1: 33,966,611.42
Day 2: 33,966,611.42
Day 3: 33,966,611.42
Day 4: 34,074,098.36
View 9 Replies
View Related
May 26, 2009
I have 6 different sections of questions, each section having a final average score between 1 and 5.
I would like to somehow create a percentage weighting on each of these sections and then have a final score accross all section, taking into account this weighting?
View 9 Replies
View Related
May 28, 2014
I am trying to come up with a good way to average a particular question in a survey result for instructors, however the caveat has me pretty puzzled. We're looking to be able to filter by mm/dd/yyyy date (which pulls mm/dd/yyyy hh:mm from raw data) and then have one average of all scores for each instructor listed. It gets a bit complicated in that the actual survey has 6 drop down boxes with all the instructors names, so one instructor could have data for the same (or any) date in any of the 6 colored columns (name, score, qualitative x 6).
My initial thought was to list out the names on a second sheet and then have some sort of averageifs to combine the columns, but I am a bit lost on how to take the dates into account, let alone how to sum the columns then average.
View 2 Replies
View Related
Mar 20, 2008
I need to do the following and can't figure it out. How do I create a running average that will only calculate the averages in % each month. Example: Opt 1 for Jan, Feb, Mar =1 each= 3 total = 100%; OPt 2 for Jan, Feb, Mar =1,0,1= 2 = 66%; Opt 3 for Jan, Feb, Mar = 0, 0, 1 = 1 total = 33%. My problems is I want monthly running average that shows the yearly percentage up to date but only for the months there is a value 1 or 0. How can this be done because the way I have it now, those % are being divided by 12 and that isn't the correct %
View 9 Replies
View Related
