I am trying to figure out a formula for excel to look at two columns and
average the data in one of them if a certain number is in column A.

Example: I want Excel to look at the contents in column A for a range of
cells and if the content is the number 1, I want it to then average the
contents of what is in column D for all rows that have a 1 in column A.

I have tried using IF and average and I just get a return of #value. I
tried =average(if(a1:a20="1",d1:d20)) but it returns the error even when I
enter it as an array formula.

Any help is appreciated.

Dear Kris

Your formula is correct. 2 points

1. Check for Text "1" or numeric 1

2. Please note that this is an array formula. Within the cell in edit mode
(F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

=AVERAGE(IF(A1:A20=1,D1:D20))

3. Alternatively if you are using 2007 you can use
=AVERAGEIF(A1:A20,1,D1:D20)


If this post helps click Yes
---------------
Jacob Skaria


"Kris" wrote:

I am trying to figure out a formula for excel to look at two columns and
average the data in one of them if a certain number is in column A.

Example: I want Excel to look at the contents in column A for a range of
cells and if the content is the number 1, I want it to then average the
contents of what is in column D for all rows that have a 1 in column A.

I have tried using IF and average and I just get a return of #value. I
tried =average(if(a1:a20="1",d1:d20)) but it returns the error even when I
enter it as an array formula.

Any help is appreciated.

Thank you, it worked this time, don't know what I was doing wrong, but I got
my averages!

"Jacob Skaria" wrote:

Dear Kris

Your formula is correct. 2 points

1. Check for Text "1" or numeric 1

2. Please note that this is an array formula. Within the cell in edit mode
(F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

=AVERAGE(IF(A1:A20=1,D1:D20))

3. Alternatively if you are using 2007 you can use
=AVERAGEIF(A1:A20,1,D1:D20)


If this post helps click Yes
---------------
Jacob Skaria


"Kris" wrote:

I am trying to figure out a formula for excel to look at two columns and
average the data in one of them if a certain number is in column A.

Example: I want Excel to look at the contents in column A for a range of
cells and if the content is the number 1, I want it to then average the
contents of what is in column D for all rows that have a 1 in column A.

I have tried using IF and average and I just get a return of #value. I
tried =average(if(a1:a20="1",d1:d20)) but it returns the error even when I
enter it as an array formula.

Any help is appreciated.