AVERAGE using LOOKUP, INDEX, or MATCH
 

I have 2 separate sheets: "Analysis" and "RawData". I am using the
Analysis page for various formulas that interpret the RawData.

This is the basic layout of the RawData sheet:

A B C D
1 Acct 1 Acct 2 Acct 3
2 Day 1 balance balance balance
3 Day 2 balance balance balance
4 Day 3 balance balance balance

I am looking at bank account balances on specific days. There are
roughly 100 accounts. Since their positions are subject to change, I
want to use dynamic formulas to analyze the data.

My Analysis page will have the account numbers in Row 1, just as they
are on the RawData page. However, they are not necessarily in the
same order, and they are subject to moving around.

Assuming that "Acct 1" is in cell B1 on the Analysis page, I want to
reference that account number and pull the average of all days for
that account from the RawData page.

Since I know the location of Acct 1, I could very simply just use
AVERAGE('RawData'!$B:$B). However, I will not know the column of
every account.

Thanks for your help.

Kevin

Answer: AVERAGE using LOOKUP, INDEX, or MATCH
 

Hi Kevin,

To pull the average of all days for a specific account from the RawData sheet to the Analysis sheet, you can use the AVERAGE, INDEX, and MATCH functions together.

Here are the steps:

1. In the Analysis sheet, enter the account number you want to reference in a cell. Let's say you enter it in cell B1.
2. In the cell where you want to display the average, enter the following formula:
   
   
   Formula:

   ---

   =AVERAGE(INDEX(RawData!$A:$D, MATCH(B1, RawData!$B:$B, 0), 2):INDEX(RawData!$A:$D, MATCH(B1, RawData!$B:$B, 0), 4)) 


   ---

   Let's break down this formula:
   
   - INDEX(RawData!$A:$D, MATCH(B1, RawData!$B:$B, 0), 2) returns the cell in the RawData sheet that corresponds to the row where the account number is found (using the MATCH function) and the second column (where the balances for Day 1 are located).
   - INDEX(RawData!$A:$D, MATCH(B1, RawData!$B:$B, 0), 4) returns the cell in the RawData sheet that corresponds to the row where the account number is found (using the MATCH function) and the fourth column (where the balances for Day 3 are located).
   - The colon between the two INDEX functions creates a range of cells that includes all the balances for the specified account.
   - AVERAGE calculates the average of all the balances in the range.
   
3. Press Enter to calculate the average.

This formula will dynamically update if the account number moves to a different column or if new data is added to the RawData sheet.

AVERAGE using LOOKUP, INDEX, or MATCH
 

Try this:

=AVERAGE(INDEX(B2:D4,,MATCH(acct_num,B1:D1,0)))

Biff

"DoooWhat" wrote in message
ups.com...
I have 2 separate sheets: "Analysis" and "RawData". I am using the
Analysis page for various formulas that interpret the RawData.

This is the basic layout of the RawData sheet:

A B C D
1 Acct 1 Acct 2 Acct 3
2 Day 1 balance balance balance
3 Day 2 balance balance balance
4 Day 3 balance balance balance

I am looking at bank account balances on specific days. There are
roughly 100 accounts. Since their positions are subject to change, I
want to use dynamic formulas to analyze the data.

My Analysis page will have the account numbers in Row 1, just as they
are on the RawData page. However, they are not necessarily in the
same order, and they are subject to moving around.

Assuming that "Acct 1" is in cell B1 on the Analysis page, I want to
reference that account number and pull the average of all days for
that account from the RawData page.

Since I know the location of Acct 1, I could very simply just use
AVERAGE('RawData'!$B:$B). However, I will not know the column of
every account.

Thanks for your help.

Kevin

AVERAGE using LOOKUP, INDEX, or MATCH
 

I tried that but for some reason it didn't work. It seems like it
would, but the totals do not add up correctly. This is the exact
formula I typed into my Analysis page:

=AVERAGE(INDEX('RawData'!$B$2:$IV$65000,,MATCH(B$1 ,'RawData'!
$1:$1,0)))

B1 (on the Analysis page) contains the account number I want.

AVERAGE using LOOKUP, INDEX, or MATCH
 

Nevermind. I toyed around with it and got it to work. Thanks for the
quick and effective response!

Kevin

AVERAGE using LOOKUP, INDEX, or MATCH
 

Using:

MATCH(B$1,RawData!$1:$1,0)

You're off by 1 column

Either:

MATCH(B$1,RawData!$1:$1,0)-1

Or:

MATCH(B$1,RawData!B$1:IV$1,0)

Thanks for the feedback!

Biff

"DoooWhat" wrote in message
ps.com...
Nevermind. I toyed around with it and got it to work. Thanks for the
quick and effective response!

Kevin