# Formula Help

This is a discussion on Formula Help within the Excel Questions forums, part of the Question Forums category; I have a sheet with two columns and am trying to build a formula to scan the the first column ...

1. I have a sheet with two columns and am trying to build a formula to scan the the first column for a certain number, example 2, and then if that row has that value in the column to average all rows that match the first column in the second column.

First column
1
2
5
8
15
2
2

Second column

\$10.15
\$2.15
\$8.85
\$0
\$6.01
\$.58
\$.04

So the formula should look at the first column and if that column is a 2 than average the last column but only use the \$ amounts for those that were a 2. In this case the answer is the average of \$2.15, \$.58 and \$.04 which is \$.92

I have tried several ways and just cannot get it to work and thought maybe one of you could help. Thank you!!

2. You could use
=SUMIF(A1:A7,2,B1:B7)/MAX(1,COUNTIF(A1:A7,2))

which is less "pretty", but probably a better bet then the array alternative of:
=AVERAGE(IF(A1:A7=2,B1:B7))

Good luck.

3. IML
U've solved my problem to!!!!!!!!!
THANKS!!!!!!!!

Chunk

4. IML
U've solved my problem to!!!!!!!!!
THANKS!!!!!!!!

Chunk

5. The first one worked great!! I double checked it on two of the groups and got the same answer. Thank you so much!! You are amazing.

6. The first one worked great!! I double checked it on two of the groups and got the same answer. Thank you so much!! You are amazing.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•