# Finding The Average Across Multiple Sheets

Oct 18, 2005
I'm trying to find the average for a person with scores in column 4 on 3 separate sheets. The following function returns #VALUE.. Because the person may be in a different row on each sheet, I'm thinking I need to use the VLOOKUP function.... Also, as an aside, the person may have a zero on one sheet that would affect the average score...

=AVERAGE(VLOOKUP(A4,Week03:Week01!A$4:G$30,4,0))

View 14 Replies
ADVERTISEMENT
Jul 30, 2014

I have a number of rankings, all from different spreadsheets that I wish to combine and average out.

Thus far I have pulled all the relevant worksheets into 1 spreadsheet. The issue I'm having is that each worksheet contains slightly different names.

So as an example, Sheet1 may have 4 columns: Bob Hope | 4 | SE | PM |

Sheet 2 may have 2 columns: | Bob Hope, PM, SE | 8 |

Sheet 3 may have 3 columns: | Bob Hope, SE | PM | 9 |

What I need to do is have a master sheet which just pulls through the name and then the average of the scores, i.e.: | Bob Hope | 7 |

I have approximately 400 people that I need to do this with.

View 3 Replies
View Related
Feb 27, 2013

I have to find the average of a set of data with a lot of different criteria. I think an array formula would work wonderfully but I just cannot seem to get it.

My example: I have a large set of data that I must get multiple averages from that fall within differing sets of criteria. For my example we'll just say that I have to find the average of the number column that occurs on or after Jan-12 (January 2012) and is type "p1" or one that has no date and is type "p1" and to not average numbers that are equal to 0. So logically it would look like this: If date

View 2 Replies
View Related
Jul 26, 2012

I am trying to average PT ScoreS from multiple groups based off of different criteria. Please see below tables:

KEY:

MS = Class CO = Company PLT = Platoon SQD = Squad

A/1 = Alpha Company, 1st Platoon (Example of Company and Platoon PT Score Average)

A/1/2 = Alpha Comapny, 1st Platoon, 1st Squad (Example of Company, Platoon and Squad PT Score Average)

F14 is the average of all PT Scores

A

B

C

D

E

F

1

NAME

MS

CO

PLT

SQD

PT SCORE

[Code] ....

I need to find the equations of different groups of PT Scores. I want the averages/equations to go below the different groups.

A

B

C

D

E

F

G

H

I

J

K

1

MS 1

MS 2

A

A/1

A/2

A/1/1

A/1/2

A/1/3

A/2/1

A/2/2

A/2/3

[Code] .......

View 2 Replies
View Related
Dec 24, 2012

I am currently using Excel 2003. I have a worksheet with two tabs.

First tab has a list of bank Names. Second list has Bank Names and balances.

I need to find out the the average from a Bank in the first tab, to the same bank on the second tab that reflects the balances.

View 6 Replies
View Related
Apr 16, 2007

In column A I have a list of 5 Auditors labelled Q1 - Q5, 5 Coolum’s across in column F I enter in their scores as a % e.g. 80%. ...So Q1 - 50%, Q2 - 60%. In column A37-A41 I have Q1-Q5 listed, in Column B37-B41 I need to calculate the average deviation per Auditor eg. If Q1 has 2 entries of 50% and 75% return average value in cell A37 which should be 62.50%. I am trying to calculate the average for each Auditor. find attached example.

View 2 Replies
View Related
Jan 5, 2014

I am working with a nonprofit to set up their financial ledgers. There is one workbook with 12 sheets, one for each month. The goal is to be able to set up a formula that searches through all 12 sheets for every donation that a specific individual has made. For instance, let's say that John Smith gave x amount of money on 1/1/2000, y amount of money on 1/10/2000, and z amount of money on 2/2/2000. This data will appear on 2 different worksheets. I have the following formula, which allows me to look through one sheet at a time:

{=INDEX(Jan!$B:$B,SMALL(IF(Jan!$A:$A=Smith!$A$2,ROW(Jan!$A:$A)),ROW(1:1)))}

Where Column A in each sheet is individual name, and column B is amount donated. Sheet "Jan" is the data for the month of January, and sheet "Smith" is the culmination of John Smith's donations for the full year. Using this array function, I am able to retrieve all data for John Smith in the month of January, but I can't find a way to make one function that searches for all of John Smith's donations in each month. Is there a way to build an array function in VBA that would accomplish this?

View 3 Replies
View Related
Oct 4, 2008

I have 10 sheets in my workbook that represent 10 employees. Column G, rows 5 through 739, represent the amount of time each employee spends doing a certain task for each day of the year. All these cells have zeros in them until a we come to that day and a time is entered for that day. The result in each cell is a certain amount of minutes spent on that task.

I can average each employees time individually on it's own sheet with this formula: {=AVERAGE(IF(G5:G739=0,"",G5:G739))}, but what I'm having a problem doing is averaging all 10 employees together on a separate sheet. I'm trying to figure out company wide how much time is spent on this particular task each day.

My depth of understanding here isn't too great, I'm learning as I go. I think I have to incorporate that formula while also selecting each sheet. I'm starting to get the BSOD in my head with this one.

View 3 Replies
View Related
Jun 6, 2014

Average.xlsxHi

I have changed the attachment to use CSV's

I am trying to create a report that I can enter the codes I am looking for and the formula will search the sheets within the report and once the results are found then find the average of those results.

What I am trying to do is search the codes from Sheet 1 under Outlet and ESA(in Blue) in sheet A,B and C. The problem is the ESA code seen in column I of A,B,C is only used when column L is empty in A,B,C.

Once the relevant codes are found I then need to look for the KPI's seen in sheet 1 B4, B6 and B8, they can be found in A,B,C in column R. then the result comes from column S in A,B or C.

Once the result is found for each code I want to find the average of them, with the answer to populate in yellow in sheet 1

I want the formula to be able to handle more or less codes as well as adjust the formula so I can add more sheets(possible D,E,F,G etc)

View 5 Replies
View Related
Mar 24, 2007

Sub maxTemperature()

Sheet6.Select

Dim myrange As Range

Range("b1").Select

myrange = ActiveCell. CurrentRegion

cellcount = myrange.Count

imax = ActiveCell.Value

i = 2

Do

Do While i < cellcount

If Cells(i, 2) > imax Then imax = Cells = Cells(i, 2).Value

i = i + 1

Loop

Range("a25").Value = imax

isum = 0

For i = 1 To cellcount

isum = isum + Cells(i, 2)

Next cellcount

ave = isum / cellcount

Range("a26").Value = ave

End Sub

View 8 Replies
View Related
Mar 10, 2009

I have 2 rows of data

1. title

2. value

eg

Row 1: Title1, Title2, Title1, Title4, Title2, Title1

Row 2: 2 , 3 , 0 , 1 , 2 , 6

In the above I am looking for the average of Title1, where the value is greater than 0. There are 3 occurances of Title1, but only 2 have values. The answer i am after is Sum is 8, average is 4 (ie 8/2 and not 8/3)

View 3 Replies
View Related
Nov 20, 2009

I am using EXCEL 2003. I am to find Average using few conditions. I have explained the requirement in the sample workbook.

View 7 Replies
View Related
Jan 2, 2009

I’m using the following formula:

SUMPRODUCT(--(Issue="Yes"),--(Month=Aug),--(Building=$A$9))

To find the number of instances where there was an issue in a certain building during a certain month.

What I can’t figure out is how to find the Average number of such instances for the past 3 months, the last 6 months & the trailing twelve months. If I change the formula to:

SUMPRODUCT(--(Issue="Yes"),--(Month>April),--(Building=$A$9))

I get the correct sum for the months of May, June, July & Aug but I need an average number of instances per month for the last three, last 6 and TTM but not to include the current month.

View 9 Replies
View Related
Jul 9, 2008

I am trying to get an average of a couple numbers, but I have to enter both numbers in one cell.

I have to enter the numbers in a cell as a range (ex. "1000-3000"). I need to convey it as a range in the spreadsheet I am doing, but in a separate cell I need the average of the extremes (1000 & 3000). Is there a formula or anything that would let me get the average of those two numbers(2000) directly from that one cell? If needed, I could make the cell "1000,3000" instead. I just don't want to make two separate cells, one saying 1000 and the other saying 3000.

View 11 Replies
View Related
Jun 1, 2009

I'm working on a project in which i had to calculate the average of particular field and that also with a macro in this application i had done that that's working supperb but i'm coming accross a problem according to which the range which i had to take average dosen't contains all integers

eg if range is A1:A10

then data is like

79

80

98

TBA

98

TBA

TBA

N/A

N/A

N/A

now ave for this range can't be calculated directly as many values are strings

what i need is

using a avg function on this range where TBA(To Be Anounced) is to be treated as 0 and N/A(Not Applicable) as null value

here's da avg dunction which i had used in macro

View 6 Replies
View Related
Jul 20, 2013

Finding an average age from a list of dates. This is just a small sample:

7/9/2013

7/10/2013

7/10/2013

7/10/2013

7/11/2013

7/11/2013

7/14/2013

7/14/2013

7/19/2013

7/19/2013

I tried using =AVERAGE(DAY(A1:A10)) CTRL + SHIFT + ENTER, but that just doesn't seem correct.

View 9 Replies
View Related
Mar 11, 2014

I am trying to calculate the average headcount for different categories (over a certain period of time, by product, by job type etc) and I want to do it using a formula instead of a pivot table.

I have this set of data with a detailed headcount for every month. And I can find out the total HC for different categories using sumif/sumifs but if want to find out the average for a certain period, I need to split that total to the number of months where I have a HC.

For example, if the HC per month would be:

May 2013 - 12

June 2013 - 10

July 2013 - none

August - 5

in a 4 months period I would have a total HC of 27 and the average would be 9 (27/3 - July shouldn't be counted). How can I get the average in a single formula?

Month

Type

L/H

Prod type

HC

[Code].....

View 9 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
Aug 4, 2009

I have 5 years of data in 2 columns:

Col A. Col B.

8/2/2004 Value 1

to

7/31/2009 Value n

I have a table set up as follows

1 2 3 4 5 6 7 8 9 10 11 12

2004

2005

2006

2007

2008

2009

I was wondering how I could construct a conditional statement to pull the associated values with the given month and year in the table...

I tried the following to no avail... I'm just getting a zero value:

=AVERAGE(IF(MONTH(J6:J1255)=AC$35,IF(YEAR(J6:J1255)=$AB37,K6:K1255)))

View 9 Replies
View Related
Aug 25, 2014

Please find the attached MS Excel 2010 file <average set.xlsx>.

There is set of positive set & negative set of values available in the Column A. The values are plotted against Column A in Column B. Light green are positive sets and light yellow are negative sets.

Now I want to calculate the average for the positive set & negative set of values as shown in light majenta in the cells F4:F9. Also all majenta cells to be plotted by formula.

The Column H and Column I also same as like above , but here negative set of numbers starts first.

View 4 Replies
View Related
Jun 25, 2013

I have an excel file which contains two sheets. In one sheet there are some numbers in a single column. In the other sheet again there are certain numbers span across rows and columns (a number may be duplicated). I need to check whether there are numbers which exist in both the sheets. If such a number is found it may be marked (say with a color) in both the sheets.

View 2 Replies
View Related
Oct 11, 2011

I have in Sheet 1 a list of product codes (that should be only numbers, i.e: 1010) on column A, product description on B and a sales column on C.

On Sheet 2 I have on column A I have in the same cell a letter "C" the product code and the product description(i.e.: C1010 Onions). On column B I got the sales total. ( I dont care about the letter "C" it can be deleted)

I would like to use a VBA code to find the same product codes between sheet 1 & 2 and copy the sales total from sheet 2 to sheet 1 when the product code is the same.

View 1 Replies
View Related
Nov 5, 2009

I would like is the average of numbers that are on certain sheets with a certain criteria. I have an excel example that might explain more.

View 11 Replies
View Related
Jun 12, 2014

I have a couple of issues and if its okay will post two threads to make it easier to follow due to my basic skills in Excel.

I have a workbook with 5 sheets. The first sheet is a stat sheet which picks up data from four other spreadsheets. I want to work out the average working days taken to complete a case but this data is across multiple sheets.

So for example Column E in each sheet tells you what type of case it is i.e. investigation. Then column T tells you number of workings day it took to complete the case. To work this out I have used the following formula:

=SUM(SUMIF(Further_Action!E:E,"Investigation",Further_Action!T:T),

SUMIF(Court_Application!E:E,"Investigation",Court_Application!T:T),

SUMIF(No_Further_Action!E:E,"Investigation",No_Further_Action!T:T),

SUMIF(Closed!E:E,"Investigation",Closed!T:T))/SUM(COUNTIF(Further_Action!E:E,"Investigation"),

COUNTIF(Court_Application!E:E,"Investigation"),COUNTIF(No_Further_Action!E:E,"Investigation"),

COUNTIF(Closed!E:E,"Investigation"))

I know its very basic but its the only way I know how to work out an average in Excel 2003. I need to add a second criteria where on every sheet it looks in column G for date the case was allocated. As I'm only looking to report in the current report year I want it to look for cases where the value in column G is =>01/04/14 but =<31/03/14.

View 2 Replies
View Related
Feb 26, 2012

I have a workbook that updates from external source and creates sheets depending on a cell range.

I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets

What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc

This is what i have so far

I get compile error here ........Sheets(ArrSh(1)).Activate

Also need it to work for all the other rows.

Sub hardcode()

'

'Sheets("Summary"). Select

If Range("a7") = "complete" Then

'

Sheets(Array("1", "0")).Select

Sheets(ArrSh(1)).Activate

[Code] ......

View 2 Replies
View Related
Aug 9, 2007

I have been running a simulation for about 18 hours now and just received:

Run-time error '1004':

Method 'Add' of object ' Sheets' failed

I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:

ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)

The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...

View 9 Replies
View Related
Feb 18, 2014

I have a work sheet named "Main_List"...In column D starting with "D2" I would like to list worksheets that I would like to have printed via VBA.

The workbook has several hundred worksheets and I would like to list in column D only worksheets that I would like to print with VBA code.

View 3 Replies
View Related
Dec 26, 2009

I have a workbook with 26 sheets, labelled A to Z. Column A in all the sheets have names from rows A6:A35.

I need a macro or a code to extract all the names from each of the 26 sheets and paste it to a new sheet 'Names' under column A, such that names starting with 'B' paste under all the names 'A' and so forth till 'Z'.

View 9 Replies
View Related
Feb 19, 2012

I've got data being scraped from a site, putting 1 new workbook in a folder each day

each workbook has 40 sheets in it.

i need to run 5 modules in sequence on a sheet then loop to the next sheet and run the same 5 modules.

ive writen all the modules, and can loop them through the sheets in sequence but i cant work out how to loop them through the each workbook in the folder..

is there an easy way to do this or can it not be done because it would need access to the folder that holds all the wordbooks which lives outside of excel on the desktop ?

View 5 Replies
View Related
May 27, 2014

Actually i have to two sheets. In (1st file) I have the data of customers. And in (2nd file) I get the approved sales. So i have to find the numbers.

In my sales sheet, there is data of customers like name, address, phone number etc.

In my sales report sheet, i got multiple numbers in a notepad file which are rejected sales.

So basically, i have to copy every number one by one, and then have to find it in my excel sheet with CTRL+F and then i change the color of that cell which is rejected.

I want magic by which i simply add all the rejected numbers in one column of my file, and then do Abra Cadabra by which all the numbers which are rejected get in red colour automatically in the column in which there are all the numbers (approved as well as rejected).

View 13 Replies
View Related
Jun 14, 2014

I'm currently doing a project which requires me to find and replace a large number of values.

ie. Column A Row 1 = 80099, Column A Row 2 = 554441, Column A Row 3 = Z5478, CAR4 = B89877 etc...

I need to replace these numbers with meaningful values and I have a reference table to tell me what these codes mean ie. 80099 = apple juice, 554441 = orange puree, Z5478 = pumpkin soup, B89877 = banana smoothie. The reference table is in a separate excel spreadsheet and is tabulated in two columns ie. Column A = "the code", Column B = "the value".

There are like 1000+ codes I need to find and replace which I know is going to kill me if I did it manually. How do I find and replace all the values?

View 14 Replies
View Related
Nov 21, 2008

I have revenue sheet that spans 12 months kind of looks like this:

0 | 240 | 261 | 0 |317 | 0 |0 | 0 | 0 | 0 | 0 | 155

So that is 1 row, my problem is I have about 8 thousand of these, and what I'm looking for is trends in lapsed accounts (accounts with no spend, the "0's"). As you can see I have 6 zero's in a row, which means this customer was lapsed for 6 months.

I want to run something that will find the average of zero's in a row for all 8,000 rows. For example one customer has 6 zero's in a row the other one has 3 zero's in a row so the average would be 4.5. I only care about zero's in a row.

View 3 Replies
View Related
Jan 21, 2010

I'm working on a macro that's supposed to copy certain cells from a sheet, and paste them in another. But the problem is that I can't find a way to get them all in one go. The sheet that I am copying from has a mile-long list that looks something like this:

OR0220018NO-RING 21,95 x 1,78 N270 BS-0205,00

OR0220018N90O-RING 21,95 x 1,78 N290 BS 02022,00

OR0220018VO-RING 21,95 x 1,78 V275 BS-02010,00

OR0220018V90O-RING 21,95 x 1,78 V290 BS 02013,00

OR0220020NO-RING 22,00 x 2,00 N2709,00

OR0220020VO-RING 22,00 x 2,00 V28024,00

OR0220025NO-RING 22,00 x 2,50 N2705,00

It goes over 3 columns. I want to select column A, B and C in every row that contains the letters "N270".

View 2 Replies
View Related
Dec 14, 2012

The goal of this is to find the best combination of a captain (out of 5 possible choices), 5 crewmates (out of 25, no repeats), deck items (2 items, can be repeated, 20 total options), ram (1, out of 13), hull (1, 13 choices), and rudder (1, 7 choices)

Each choice in every category has 4 attributes - fighting, morale, seafaring, and speed. The ship parts also have a true/false field called "unlocked" - the false fields obviously can't be chosen.

A mission gives numbers saying what total attributes are needed for a 100% chance of success. If the mission has 2 attributes that are required (say seafaring and fighting, for instance) then it takes whichever chance of success is lowest and counts that as the mission success chance.

Example: A mission requires 2000 fighting and 2000 seafaring - I can reach 2000 fighting but 1500 seafaring, chance of success is 75%. It would be better for me to get a combination that gave me an 87% success rate in both fields.

How do I have excel go through every combination (and is that even plausable? I would imagine that being in the billions of combination choices) and highlight (turn a cell green or something) the combination that has the highest minimum chance of success in all fields?

View 2 Replies
View Related
Feb 28, 2008

I have managed to make a work queue and lots of other stuff for the model, but I can't get it to take orders in the way I want it. Each order has a order number (from 1 to 100) and the orders come in almost randomly e.g. 3, 5, 11, 2, 7, etc. What I want to do is to take the smallest available order that has not been processed in.

The available orders column and processed orders look something like this:

A B C D

Time, Available, Processed, Start processing

5 2 0 2

10 0 0 0

15 0 0 0

20 0 0 0

25 5 0 0

30 0 0 0

35 0 0 0

40 0 0 0

45 4 2 4

50 0 4 0

55 7 0 5

60 6 5 6

Zero means no new orders or no processed orders. Now the Start processing column should select the smallest not processed order if previous order has been processed.

A have, for now at least, all other problems solved, but can't figure out how to get start processing column check for the smallest not processed order line. I have tried combination of Min and Max functions with If, but it soon requires too many Ifs to make any sense out of it. I also tried the Dmin function, but it wasn't up to the task becouse the model requires ~1000 lines and as Dmin only takes criterias vertically I ran out of columns . So how could find minimum from row one until current row excluding values processed so far and only checking orders available so far?

View 9 Replies
View Related
Sep 3, 2009

Is there a way to return not only one cell but two with LARGE?

I'm still working on my top 5 sellers list and trying to generate the list correctly. As of now when I have multiples of the same value I get the same name. Here's what I have now:

Name---Sales

Bob---75

Sue---68

Tim---66

Tim---66

Tim---66

What I'm trying to do is this:

Name---Sales

Bob---75

Sue---68

Tim---66

Jane---66

Dave---66

View 9 Replies
View Related
Feb 18, 2010

I have a master project worksheet that has projects on it and costs it looks like the following.

Master Project Sheet

ABCDEFG1

Plumbing

Plumbing Invoice

Electrical

Electrical Cost

Flooring

Flooring Invoice

2Project 001

ContractorName

Invoice Amount

ContractorName

Invoice Amount

ContractorName

Invoice Amount

3Project 002

ContractorName

Invoice Amount

ContractorName

Invoice Amount

ContractorName

Invoice Amount

Individual Contractor Invoice ABCD1Project 001Job PerformedContractor NameAmount

** Note all invoices are in the same folder

I essentially would need the Master Project Sheet, to have instructions to say. Research the folder of invoices, when you find project number 001 and plumbing (job performed), then grab C1 and D1 off of the contractor invoice and place it into the corresponding project name on the master, in the case above B2 and C2. Is that possible?

View 9 Replies
View Related
Nov 15, 2009

At my work we use excel to keep a large number of labels done in one day, those labels are then added up to give our weeks total, running total and average by day.

What i am looking to do though on a seperate sheet though is find out which day we had the most, an example of what it looks like

View 2 Replies
View Related
Jan 4, 2013

I am not sure if Excel is able to do this but basically I am looking to find out which rows have some duplicate values. I have just read this back and it doesn't make a great deal of sense so I have attached an example spreadsheet.

Basically I am looking to find if E1:G1 duplicates further on down the list, hope this makes a bit more sense with the example attached.

Trial Upload.xlsxâ€Ž

View 3 Replies
View Related
Mar 14, 2014

I have a list of names that are mixed and in no order (and need to stay out of order, so not sorting allowed). I need to parse this list to give me the FIRST instance that each name shows up in the list. The one exception is that the first name, cell A1, will be auto-generated from a different workbook, and it is automatically named Name1.

So, in the example spreadsheet, Name1 (cell E2) must equal "Alice". The problem arises on Names2-4. Name2 (cell E3) should be David, Name3 (cell E3)=Jerry, and Name4 (cell E4)=Mark. I tried an array formula:

{=INDEX(A2:A13,MATCH(TRUE,A2:A13<>Name1),0)},

But it is yielding "Jerry" as Name2, when it needs to be Name3. And, of course, this doesn't work on Names3-4 at all. I don't think it matters, but just in case, A2:A13 will be data validation lists.

View 5 Replies
View Related