# Excel 2003 :: Finding Average With Multiple Spreadsheets?

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
ADVERTISEMENT
May 2, 2014

I have two Spreadsheets on Excel 2003, one provided by a service supplier (i), and one from my own system (ii).

I need a macro attached to spreadsheet ii that looks for data matches between the F column of my spreadsheet, and the C column of the suppliers. Where data matches (i.e. if cell C42 in spreadsheet ii is the same as cell F23 in spreadsheet i) it transfers the contents of the A column of spreadhseet i and places it in the corresponding cell in the C column of spreadsheet ii.

Example 1 : In spreadsheet i cell C20=BN213YD. In spreadhseet ii cell F55=BN213YD. Therefore, the macro takes the contents of cell A20 in spreadsheet i and places it in cell C55 of spreadheet ii

Example 2 : In spreadsheet i Cell C75 = TN142XS. In spreadsheet ii this data is not found. The macro therefore takes no further action.

Basically, the suppliers spreadsheet has less information per row, but more entries overall. My spreadsheet is lacking a lot of suppliers references, but has more comprehensive information elsewhere. Until now i have been doing a manual search of the postcodes between the spreadsheets and rectifying them manually, but this is taking longer and longer as the spreadsheets grow.

View 1 Replies
View Related
Nov 8, 2011

I have two work sheets where I have data.

Sheet1 contain daily input table which as follows:

Name salaryBonusXX10002XY9001YY11002ZY15003ZZ12502AA10502AZ9501

Sheet2 have table where all the information is saved. So we can say this is database of sheet1. Which store every day information of sheet 1.

SALARY DATABASEMonth Name Salary Bonus

Problem:

I want that when I finished writing on the sheet 1(which i do manually) then with macro it go the sheet 2 and find first empty cell in the name column and past the all the data of sheet1 table. Months I will put manually. I am working on MS2003.

View 9 Replies
View Related
Jan 26, 2012

I know you can't use the SUMIFS function in 2003 but give alternative in 2003

=SUMIFS($D$3:$D$30000,$C$3:$C$30000,"IOP",$E$3:$E$30000,">=01/03/2011",,"

View 3 Replies
View Related
Mar 7, 2013

I have a spreadsheet and on sheet 1 it currently has 45791 rows filled with data and it increases each day.

Each row looks like this

A RACECOURSE

B DATE

C RACE DETAILS

D HORSES NAME

E RATING OF THE HORSE

On Sheet 2 I have 3 columns. These are the list of horses that are running on a particular day.

A DATE

B RACECOURSE

C NAME OF RACEHORSE

What I want to do is to be able to list in columns D,E,and F on sheet 2 the last three ratings the horses achieved from sheet 1.

how to acquire the latest rating of the horse by using the following formula.

=IF(ISNUMBER(v(LOOKUP(9.9999999999999E+307,1/SEARCH(C2,Sheet1!$D$3:$D$45790),Sheet1!$E$3:$E$45790))),v(),"")

This works a treat but for the life of me I can't fathom out how to get the formula to pick the latest three ratings and place them on sheet 2.

I am using excel 2003

View 9 Replies
View Related
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
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
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
Jan 5, 2012

I have a sheet set up with date in Column A, time in column C and numbers in column D, starting in row 2 down. I made a pivot table with the dates grouped by day, 7, to show a week and I can group the time by hours, 6am, 7am, 8am. but what I want to do is group the time for times between 2 times, like from 6:00 AM to 10:00 AM, 10:01 AM to 2:00 PM and 2:01 PM to 8:00 PM, what I am wanting to do is group by week, group by the times above and average the numbers for that time. Is there anyway to do this in a pivot table? Or some other way? this is for use in Excel 2003

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
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
Dec 4, 2013

I have a worksheet which has various figures for each day of the week however I need to establish the weekly average of these figures.

Due to the way in which the figures are displayed, I am unsure how to use a formula which does not require a range with cells located adjacent to one another.

I have attached a test sheet as an example. The cells in yellow require the formula and I need a weekly average for criteria 1-3. This formula also needs to be compatible in Excel 2003

Test Sheet.xls

View 3 Replies
View Related
May 29, 2013

I am trying to do the following.

Monday=0

Tuesday=5

Wednesday=10

Thursday=0

Friday=5

Saturday=0

Sunday=0

Averages

Monday=0

Tuesday=5

Wednesday=7.5

Thursday=0 (I have got a value return of 7.5)

Friday=5

Saturday=0 (I have got a value return of 5)

Sunday=0 (I have got a value return of 5)

I need to work out averages for cells higher than zeros, in other words, I need to ignore those.

Also say I have got an average of 5 by Tuesday and no number yet for the rest of the week, I still get an average of 5 for all days left which I do not want.

I am using excel 2003 and formula =SUM(RANGE)/COUNTIF(RANGE,">=0").

I would also like to hide the annoying #DIV/0! error.

View 9 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
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
May 19, 2014

How can i combine multiple Excel Spreadsheets into one Spreadsheet ?

View 3 Replies
View Related
May 29, 2012

Is there a way to lock and unlock multiple excel spread sheets with only one password at one go? For example if I have 5 sheets, I would like to be able to lock and unlock Sheets 2-5 with one password at one go while leaving sheet 1 unprotected. Do not want to type a pwd for each sheet.

View 9 Replies
View Related
Sep 13, 2013

I'm trying to create some vba code that will go into other .xlsx documents, pull all cells with a value in the first 30 columns and then return them as a table. Basically each work crew has a .xlsx spreadsheet containing their schedule and I am trying to bring them all together into one nice little package. If I go to import from external sources then click XML data, navigate to the folder they are in and them click show all files and pick my spreadsheet it imports nicely. Then I go to the next blank cell in column "A" and repeat for the 4 crews. Now I can filter by each crew but all 4 are visible on a single page as a table. I recorded a macro that shows everything I did, but it is not dynamic (the .xlsx source files name changes based on the month) I'm running XP sp3 with Excel 2010

Here is a copy of what I have

Code:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _

"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:Documents and SettingsUSERNAMEDesktopSCHEDULE STUF" _

, _

"FAcrewCurrent.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""" _

, _

";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;J" _

, _

"et OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt " _

[code].....

Is there a better or simpler way to do this? The tables don't need to be forever linked to their previous spreadsheets so if copy/pasting is a better way than I am all for that as well.

View 1 Replies
View Related
Feb 9, 2014

I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.

I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.

Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.

I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.

View 12 Replies
View Related
Oct 27, 2008

I have two spreadsheets, spreadsheet A and B. In each spreadsheet there is a column labeled 'event' and a number in that column. I need to match the event number in spreadsheet A with the same number in spreadsheet B. In spreadsheet B in the same row as the event number is another value that I need copied back to spreadsheet A.

Manually I would have to copy the event number from spreadsheet A and use 'find' in spreadsheet B. Then check in the same row as the event number in spreadsheet B for a specific number, copy that, head back to spreadsheet A and paste it next to the event number.

I've tried to create a macro to do this but when using 'find' to match the event number in spreadsheet B it keeps on using the first event number I used instead of moving down a row onto the next number. I can upload the spreadsheet if needed.

View 2 Replies
View Related
Oct 20, 2012

I have a table in A2:F200 and want to sum the entries in column C for those rows containing both an F in column E and an L in column F. I thought this would be easy but I cannot figure it out.

I use Excel 2003 so SUMIFS don't work.

View 2 Replies
View Related
Jan 26, 2013

How many tickets are created and fixed by a user for a certain period. See below. I've tried countif but it doesn't seem to be working for many criteria.

Start Date

11/1/2012

End Date

11/31/2012

Assigned To

Created

Fixed

[Code] .....

View 8 Replies
View Related
Nov 24, 2013

I have Excel 2003. I am working on a problem. I have multiple sheets for various purposes of my customers with all various columns. However i require to have a master sheet which gives me the due dates customer wise in one place. i.e it selects the customer, the worksheet purpose (say upcoming event) and the due date filed from various worksheets and combines into a master sheet,sorts the same customerwise,due date wise.

Using VBA i did create a worksheet which does this but using advanced filter but however how do i do it for all worksheets?

Using macro to go individually into each worksheet and collate data into one seems very unreliable to me. Is there a solution?

View 1 Replies
View Related
Jan 1, 2014

How to use countif in Excel 2003, for multiple range and criteria ? Can 'Nested ifs' be used? If so, a sample of such ifs

View 3 Replies
View Related
Mar 20, 2014

So I am trying to pull data from multiple sheets. I've gone through the thread, but haven't found an answer yet (or didn't work hard enough). On Sheet1, yellow highlighted column, I am trying to look up the I.D. Code for 36 month residuals. As you can see, I have to use multiple conditions on different worksheets.

I have to use most of Sheet1 columns to find the answer. I just can't figure the formula out.

I don't have Excel 2007, only 2003 I have.

View 1 Replies
View Related
Dec 2, 2011

I have a sheet that column G and column H has duplicate values. I would like to be able to put a formula in a cell P4 that will search thru column G and column H and if there are any matching items then add the corrisponding number in column I.

So say cell G8, G25, and G30 have the same and H8 and H30 have the same value, i would like this to add the values of cell I8 and I30.

Windows XP

Excel 2003

View 9 Replies
View Related
May 10, 2012

I wrote a macro to select multiple sheets by name and hide them, but I keep getting the following error message:

"Object variable or with block variable not set".

Below is the macro:

Sub HideSheets1A()

Dim ws As Worksheet

Application.DisplayAlerts = False

If ws.Name = "Variance Evaluation" Or "Investment" Or "Costs & Incentives" Or "Revenues Total" Then ws.Visible = False

End Sub

I use Excel 2003

View 5 Replies
View Related
Nov 28, 2012

Link: Create a workbook from every worksheet in your workbook

It works great, but is it possible to modify this to export each worksheet as an html file instead of an Excel workbook?

I'm using Excel 2003.

View 2 Replies
View Related
Apr 20, 2013

I am using the following formula:

=IFERROR(INDEX(drange,SMALL(IF(AND(qrange="SH",trange>30),ROW()-6),ROW(A1))),"")

to return the name (drange) of a person who was visited by "SH" (qrange), more than 30 days ago (trange).

There are faults in my formula, and if I'm honest I don't entirely understand it !

I need to return all of the names of people who were visited by "SH" more than 30 days ago. So I need the next value, and the next which is also over 30, by copying the formula down to the next cell and the next. My problem is that I get the first value (which is correct) and then the next ones are blank.

To make matters worse, the first value I get is only correct if i DON'T enter it as an array. If I do enter it as an array, I get the first row of the spreadsheet.

Using Excel 2003

View 9 Replies
View Related
Mar 20, 2014

I would like to add hyperlink to multiple JPEG images (separately) in Excel 2003 and send those images via email to another person. On receiving the mail at the opposite end, the person concerned should be able to view the images by clicking the link. How to do?

View 1 Replies
View Related