Trying to average based on 2 criteria in 2003

[boznian]

I have read several other threads on this but haven't found anything that will work for me - maybe because I am still using Office 2003? Anyway, I need an average using the following:

Column A is a value of 1 through 5. Column F is either an "X" or blank cell. Column D contains the (dollar value) to be averaged.

So in the end I will need 10 formulas (I hope I can get the other 9 if someone helps me with the first): 5 that average Col D based on a 1, 2, 3, 4 or 5 in Col A and an "X" in Col F, and then 5 that average Col D based a 1, 2, 3, 4 or 5 in Col A and a blank cell in Col F.

I have tried many variations similar to this without success:

=AVERAGE(IF($A$3:$A$500="1",IF($F$3:$F$500="",$D$3:$D$500)))
... all resulting in #DIV/0! or #VALUE!

BTW, Col a value is the result of this formula should that make any difference:
=IF(B6>2500,5,(IF(B6>1000,4,(IF(B6>500,3,(IF(B6>250,2,(IF(B6>0,1,"")))))))))
I have been using CTRL+SHIFT+ENTER for all my tries on the formula in question...

Help or advice please?

 

[T. Valko]

I have read several other threads on this but haven't found anything that will work for me - maybe because I am still using Office 2003? Anyway, I need an average using the following:

Column A is a value of 1 through 5. Column F is either an "X" or blank cell. Column D contains the (dollar value) to be averaged.

So in the end I will need 10 formulas (I hope I can get the other 9 if someone helps me with the first): 5 that average Col D based on a 1, 2, 3, 4 or 5 in Col A and an "X" in Col F, and then 5 that average Col D based a 1, 2, 3, 4 or 5 in Col A and a blank cell in Col F.

I have tried many variations similar to this without success:

=AVERAGE(IF($A$3:$A$500="1",IF($F$3:$F$500="",$D$3:$D$500)))
... all resulting in #DIV/0! or #VALUE!

BTW, Col a value is the result of this formula should that make any difference:
=IF(B6>2500,5,(IF(B6>1000,4,(IF(B6>500,3,(IF(B6>250,2,(IF(B6>0,1,"")))))))))
I have been using CTRL+SHIFT+ENTER for all my tries on the formula in question...

Help or advice please?

You were pretty close with your first formula. Remove the quotes from around any numbers. Like this.

Array entered**:

=AVERAGE(IF($A$3:$A$500=1,IF($F$3:$F$500="",$D$3:$D$500)))

=AVERAGE(IF($A$3:$A$500=1,IF($F$3:$F$500="x",$D$3:$D$500)))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[boznian]

That's it - thanks!!

 

[T. Valko]

That's it - thanks!!

You're welcome. Thanks for the feedback!