Excel 2003 finding average with multiple spreadsheets

**dodger34** · 12-24-2012, 05:43 PM

Hi,

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.

Please help.

**oeldere** · 12-24-2012, 06:01 PM

You get better result if you and an excel file without confidentional information.

**dodger34** · 12-24-2012, 06:09 PM

The worksheet I'm working from are not valid Banks. The information I have is for training purposes only.

**6StringJazzer** · 12-25-2012, 07:36 AM

Welcome to the Forum!

You have not given enough information about how your data is organized. I think you misunderstood oeldere's suggestion. You get much better answers here if you give detailed information about what you need to do, and how your data is laid out. You can describe it, or simply attach your file as suggested by oeldere.

I will assume that:
Sheet1 has a list of bank names in column A and you want the average balance in column B.
Sheet2 has a list of bank names in column A and their corresponding balances in column B, starting in row 2 after one row of headings.

In Sheet1 column B use this formula starting in row 2 and copy down:

=SUMIF(Sheet2!A:A,A2,Sheet2!B:B)/COUNTIF(Sheet2!A:A,A2)

(If you were using a later version of Excel you could use AVERAGEIF, which doesn't exist for 2003.)

**dodger34** · 12-26-2012, 03:55 PM

I apologoze for the confusion.

In the first tab, I have a list of three Bank names (not valid banks).

in the second tab, I have a list of bank names with Balances.

what I am trying to find out is the average balance for each bank.

attahced is the worksheet I am working on. Thank you in advance.

**6StringJazzer** · 12-26-2012, 04:03 PM

Your data is exactly what I guessed, and so the answer is exactly what I gave.

In "Bank" column B use this formula starting in row 2 and copy down:

=SUMIF(Balances!A:A,A2,Balances!B:B)/COUNTIF(Balances!A:A,A2)

You will see that you get an error for 123 Bank. That is because you added an extra space in the name where it appears on sheet "Balances" and so no entries are found.

**dodger34** · 12-26-2012, 05:16 PM

Thank you, your example worked.

I applied the smae logic to my worksheet and got the results a Iwanted. Thank you.

=SUMIF('Data Set A'!C:C,Variables!B3,'Data Set A'!D:D)/COUNTIF('Data Set A'!C:C,Variables!B3)