AverageIf

Maybe I'm looking at this in the entirely wrong way, but I seem to be having some difficulty with using the conditional If with the Average:
Right now I need to find the average of a group of numbers that correspond only to a specific person. I have the table organized with the NAME in column A, other data in B through E, and then the numbers I need averaged in F. I am using the formula: =Average(If(Sheet1!A:A="NAME",Sheet1!F:F,FALSE))

If I simply hit ENTER after typing the formula, I get a value of 2.52, but if I use Ctrl+Shift+Enter I get the #NUM error. Further, when I double checked my answer by manually selecting the cells that correspond to one NAME by using =AVERAGE(Sheet1!F1:F40) I get 1.18, which I know to be the correct average.

I then tried using the AverageIf formula again, only this time modifying it so as to be: =Average(If(Sheet1!A:A="NAME",Sheet!F1:F40,False)) and I get the 1.18 that I am looking for, however this obviously defeats the purpose of using If. Normally I would just bite the bullet and do the average manually, but I want to get everything set up so I can continually change the data in Sheet 1 and just have the formulas in Sheet 2 do all the work for me.

If anyone has any advice or knows what I may be doing wrong, it would be greatly appreciated. Thanks!

I am not sure why but i think you can't select a whole entire column. you have to select a range. for ex. you have to put Average(If(Sheet1!A2:A100="NAME",Sheet1!F2:F100,FALSE)). that should work. .. and then ctrl, shft, enter

Perfect! Thank you so much. Does anyone know why you cannot select a column, but instead need to define a range? I guess it doesn't really make a difference since I seem to be able to select as large a range as needed; it just seems odd to me.

Thanks again!

Note: you don't really need FALSE, this would be sufficient

=Average(If(Sheet1!A2:A100="NAME",Sheet1!F2:F100))

In Excel 2007 you can select the whole column for these type of formulas but for earlier versions you can't. In general you can only use whole columns with functions like SUMIF and COUNTIF....so if you really want to use the whole column you could combine those two functions to get an average, i.e.

=SUMIF(Sheet1!A:A,"NAME",Sheet1!F:F)/COUNTIF(sheet1!A:A,"NAME")