Excel Average Questions

So I have to excel sheets. Each sheet looks like this:

Name Value

Bob 1234

Sally 2343

Joe 564564

Essentially I have 300 names and their values. I have a second sheet that has the same names but with different values. I would like to know the easiest way to get the average value for everyone's name across the multiple sheets. I only have two sheets now, but I would like to be able to do it for more in the future. 开发者_如何学运维

If the position is the same in all sheets, this is fairly simple:

=AVERAGE( Sheet1!A1, Sheet2!A1, Sheet3!A1 )

or

=AVERAGE( Sheet1:Sheet3!A1 )

However, if the position varies, you'll have to make use of the formulas in the lookup category.

Is Bob on the same row in each sheet? Are there sheets where there is no Bob? Would bob show up multiple times?

Either way, this is something that would probably be more suitable to storing in a database. Excel can still grab things from the DB and display as a spreasheet, but doing average as you want will be far far far easier in a DB.

However, if Excel it has to be, then you'd need to use search functions to find Bob in each worksheet, then get his associated value. =LOOKUP() would probably help for the searching/lookup parts, but you'd still need to do this for every worksheet.

Lets say you have 3 sheets with the same names, in the same position. Insert a 4th sheet where you copy those names. In the results column, type =AVERAGE(Sheet1:Sheet3!A2), and copy that formula down.
Done.
If you plan to add more sheets later, keep a Special sheet that will always remain empty, and make your formula: =AVERAGE(Sheet1:Special!A2).

You will then be able to insert the new sheets BEFORE Special, and your average will need to be edited.

Use an array formula. Enter the following into the first cell replacing 'Bob' with the cell reference for the 'Bob' entry, and hit CTRL+SHIFT+ENTER:

= SUM((name_col2='Bob')*(value_col2))/sum((name_col2='Bob')*1)

There is no easy way in Excel. You have to write VBA code. You need to define all your sheets you need for your calculations go through all the names and put them in an array.

// [Name, Number, Count of Sheets] [["Bob", 1234, 1], ["Sally", 2343, 1], ...]

For every name in every sheet you need to check if it is included in your array.

If this is the case increase the number and the count otherwise add a new element with [Name, Number, 1]

At the end go through your array and output Name and Number / Count to a new sheet.

You need some basic programming skills.