what's wrong with this statement?

=AVERAGE(if A1:A65000="DFW",F1:F65000)

i want my spreadsheet to give me the average of the percentages listed in
column F where column A = dfw ...
thanks

Try,
=SUMPRODUCT(--(A1:A65000="DFW"),--(F1:F65000))/COUNTIF(A1:A65000,"DFW")
Regards,
Alan.
"Tammy" wrote in message
...
what's wrong with this statement?

=AVERAGE(if A1:A65000="DFW",F1:F65000)

i want my spreadsheet to give me the average of the percentages listed in
column F where column A = dfw ...
thanks

hi Tammy,
try it as array formula..
=AVERAGE(if(A1:A65000="DFW",F1:F65000))
press CTRL-SHFT-ENTER not just enter.
regards
--
*****
birds of the same feather flock together..



"Tammy" wrote:

what's wrong with this statement?

=AVERAGE(if A1:A65000="DFW",F1:F65000)

i want my spreadsheet to give me the average of the percentages listed in
column F where column A = dfw ...
thanks

thanks guys - i need to add one more twist. I do not want it to include in
the average where there is a value of 0%. can you please tell me how to add
that?

"driller" wrote:

hi Tammy,
try it as array formula..
=AVERAGE(if(A1:A65000="DFW",F1:F65000))
press CTRL-SHFT-ENTER not just enter.
regards
--
*****
birds of the same feather flock together..



"Tammy" wrote:

what's wrong with this statement?

=AVERAGE(if A1:A65000="DFW",F1:F65000)

i want my spreadsheet to give me the average of the percentages listed in
column F where column A = dfw ...
thanks

hi tammy,

u have new condition but u can try this also Average + IF functions - array
formula

=AVERAGE(if(((A1:A65000="DFW")*(F1:F650000%)),F1: F65000))
press CTRL-SHFT-ENTER not just enter.

or u can try also sumproduct + countif functions

regards
*****
birds of the same feather flock together..



"Tammy" wrote:

thanks guys - i need to add one more twist. I do not want it to include in
the average where there is a value of 0%. can you please tell me how to add
that?

"driller" wrote:

hi Tammy,
try it as array formula..
=AVERAGE(if(A1:A65000="DFW",F1:F65000))
press CTRL-SHFT-ENTER not just enter.
regards
--
*****
birds of the same feather flock together..



"Tammy" wrote:

what's wrong with this statement?

=AVERAGE(if A1:A65000="DFW",F1:F65000)

i want my spreadsheet to give me the average of the percentages listed in
column F where column A = dfw ...
thanks

Another option is to insert a column next to F which we will call G (which
you can hide so it does not show on the table) Then create an if statement
for the cells on this new column and now drag & fill the cells in this new
column with the new formula
=IF(A1="DWF",IF(F10,F1,""),"")
which will only return the values for DWF that are greater than zero and
leave all the other cells blank for that column. The "" leaves the cells
blank so they are not populated with zeros which allows you to average the
only the cells with a numeric value. You can now place the average function
=average(G1:G65000) in any open cell to pull an average of the new column G
you created which you can now hide if you want to clean up you sheet.

There are other ways to do this with multiple formulas. The above is simple
& works

"Tammy" wrote:

thanks guys - i need to add one more twist. I do not want it to include in
the average where there is a value of 0%. can you please tell me how to add
that?

"driller" wrote:

hi Tammy,
try it as array formula..
=AVERAGE(if(A1:A65000="DFW",F1:F65000))
press CTRL-SHFT-ENTER not just enter.
regards
--
*****
birds of the same feather flock together..



"Tammy" wrote:

what's wrong with this statement?

=AVERAGE(if A1:A65000="DFW",F1:F65000)

i want my spreadsheet to give me the average of the percentages listed in
column F where column A = dfw ...
thanks

thanks everyone. both of those worked.


"csi.sec" wrote:

Another option is to insert a column next to F which we will call G (which
you can hide so it does not show on the table) Then create an if statement
for the cells on this new column and now drag & fill the cells in this new
column with the new formula
=IF(A1="DWF",IF(F10,F1,""),"")
which will only return the values for DWF that are greater than zero and
leave all the other cells blank for that column. The "" leaves the cells
blank so they are not populated with zeros which allows you to average the
only the cells with a numeric value. You can now place the average function
=average(G1:G65000) in any open cell to pull an average of the new column G
you created which you can now hide if you want to clean up you sheet.

There are other ways to do this with multiple formulas. The above is simple
& works

"Tammy" wrote:

thanks guys - i need to add one more twist. I do not want it to include in
the average where there is a value of 0%. can you please tell me how to add
that?

"driller" wrote:

hi Tammy,
try it as array formula..
=AVERAGE(if(A1:A65000="DFW",F1:F65000))
press CTRL-SHFT-ENTER not just enter.
regards
--
*****
birds of the same feather flock together..



"Tammy" wrote:

what's wrong with this statement?

=AVERAGE(if A1:A65000="DFW",F1:F65000)

i want my spreadsheet to give me the average of the percentages listed in
column F where column A = dfw ...
thanks

Driller & CSI -

I another function too please. I'm sorry I ddin't mention before. I didn't
see it til now.

I need
if Column A contains DFW and F 0, then I need G to = Bob
if column A contains AUS and F 0, then I need G to = Lynette
so on so on so on.

Thanks,
Tammy



"csi.sec" wrote:

Another option is to insert a column next to F which we will call G (which
you can hide so it does not show on the table) Then create an if statement
for the cells on this new column and now drag & fill the cells in this new
column with the new formula
=IF(A1="DWF",IF(F10,F1,""),"")
which will only return the values for DWF that are greater than zero and
leave all the other cells blank for that column. The "" leaves the cells
blank so they are not populated with zeros which allows you to average the
only the cells with a numeric value. You can now place the average function
=average(G1:G65000) in any open cell to pull an average of the new column G
you created which you can now hide if you want to clean up you sheet.

There are other ways to do this with multiple formulas. The above is simple
& works

"Tammy" wrote:

thanks guys - i need to add one more twist. I do not want it to include in
the average where there is a value of 0%. can you please tell me how to add
that?

"driller" wrote:

hi Tammy,
try it as array formula..
=AVERAGE(if(A1:A65000="DFW",F1:F65000))
press CTRL-SHFT-ENTER not just enter.
regards
--
*****
birds of the same feather flock together..



"Tammy" wrote:

what's wrong with this statement?

=AVERAGE(if A1:A65000="DFW",F1:F65000)

i want my spreadsheet to give me the average of the percentages listed in
column F where column A = dfw ...
thanks

It depends on how many "so ons" you require. I am fairly certain if
statements only go 7 ifs deep. You need to explain better what you are trying
to accomplish. How many names do you potentially have? 5, 10 , 20 ? Are they
for information only? Do you need to count them or do anything special with
the names?

Last of all if you are using the method I described column G is already
being populated by numbers the formula is pulling out of column A for your
average. This would create a conflict since the cell can't equal both "bob"
and bob's "total" from A.

"Tammy" wrote:

Driller & CSI -

I another function too please. I'm sorry I ddin't mention before. I didn't
see it til now.

I need
if Column A contains DFW and F 0, then I need G to = Bob
if column A contains AUS and F 0, then I need G to = Lynette
so on so on so on.

Thanks,
Tammy



"csi.sec" wrote:

Another option is to insert a column next to F which we will call G (which
you can hide so it does not show on the table) Then create an if statement
for the cells on this new column and now drag & fill the cells in this new
column with the new formula
=IF(A1="DWF",IF(F10,F1,""),"")
which will only return the values for DWF that are greater than zero and
leave all the other cells blank for that column. The "" leaves the cells
blank so they are not populated with zeros which allows you to average the
only the cells with a numeric value. You can now place the average function
=average(G1:G65000) in any open cell to pull an average of the new column G
you created which you can now hide if you want to clean up you sheet.

There are other ways to do this with multiple formulas. The above is simple
& works

"Tammy" wrote:

thanks guys - i need to add one more twist. I do not want it to include in
the average where there is a value of 0%. can you please tell me how to add
that?

"driller" wrote:

hi Tammy,
try it as array formula..
=AVERAGE(if(A1:A65000="DFW",F1:F65000))
press CTRL-SHFT-ENTER not just enter.
regards
--
*****
birds of the same feather flock together..



"Tammy" wrote:

what's wrong with this statement?

=AVERAGE(if A1:A65000="DFW",F1:F65000)

i want my spreadsheet to give me the average of the percentages listed in
column F where column A = dfw ...
thanks

Have a look at Excel help for the LOOKUP series of functions.
--
David Biddulph

"Tammy" wrote in message
...
Driller & CSI -

I another function too please. I'm sorry I ddin't mention before. I
didn't
see it til now.

I need
if Column A contains DFW and F 0, then I need G to = Bob
if column A contains AUS and F 0, then I need G to = Lynette
so on so on so on.

Thanks,
Tammy