Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
CSI -
I will have approx 300 so on's. "csi.sec" wrote: 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
ditzy me - i wrote my question wrong again. But I have to add thank you for
answering my original question and it solved part of the problem for me. But I have to take it one step even further. I need <bTHE AVERAGE IF</b average if Column A contains DFW and F 0, then I need G to = Bob average if column A contains AUS and F 0, then I need G to = Lynette so on so on so on. And there will be approx 300 If's. "Tammy" wrote: CSI - I will have approx 300 so on's. "csi.sec" wrote: 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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
Hi again Tammy,
i was surprised by the 300 ifs you need to build, and you still like to go forward with the Average IF...with CTRL-SHFT-ENT. so from my first and 2nd reply, wherein you need to extend the conditions to if Column A contains DFW and F 0, then I need G to = Bob try this =AVERAGE(if(((A1:A65000="DFW")*(F1:F650000%)*(G1: G65000="Bob")),F1:F65000)) and if column A contains AUS and F 0, then I need G to = Lynette =AVERAGE(if(((A1:A65000="AUS")*(F1:F650000%)*(G1: G65000="Lynette")),F1:F65000)) so on so on so on. you can see the pattern from the two sample formula... and press CTRL-SHFT-ENTER not just enter. regards -- ***** birds of the same feather flock together.. "Tammy" wrote: ditzy me - i wrote my question wrong again. But I have to add thank you for answering my original question and it solved part of the problem for me. But I have to take it one step even further. I need <bTHE AVERAGE IF</b average if Column A contains DFW and F 0, then I need G to = Bob average if column A contains AUS and F 0, then I need G to = Lynette so on so on so on. And there will be approx 300 If's. "Tammy" wrote: CSI - I will have approx 300 so on's. "csi.sec" wrote: 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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
dRILLER - is there a better way? Should I create some type of macro?
Can I type all 300 If's in one cell? "driller" wrote: Hi again Tammy, i was surprised by the 300 ifs you need to build, and you still like to go forward with the Average IF...with CTRL-SHFT-ENT. so from my first and 2nd reply, wherein you need to extend the conditions to if Column A contains DFW and F 0, then I need G to = Bob try this =AVERAGE(if(((A1:A65000="DFW")*(F1:F650000%)*(G1: G65000="Bob")),F1:F65000)) and if column A contains AUS and F 0, then I need G to = Lynette =AVERAGE(if(((A1:A65000="AUS")*(F1:F650000%)*(G1: G65000="Lynette")),F1:F65000)) so on so on so on. you can see the pattern from the two sample formula... and press CTRL-SHFT-ENTER not just enter. regards -- ***** birds of the same feather flock together.. "Tammy" wrote: ditzy me - i wrote my question wrong again. But I have to add thank you for answering my original question and it solved part of the problem for me. But I have to take it one step even further. I need <bTHE AVERAGE IF</b average if Column A contains DFW and F 0, then I need G to = Bob average if column A contains AUS and F 0, then I need G to = Lynette so on so on so on. And there will be approx 300 If's. "Tammy" wrote: CSI - I will have approx 300 so on's. "csi.sec" wrote: 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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
Hi again Tammy,
no way for 300 if's in one cell... ifrom your original post, you are looking for the average of a single column of data based on different filtering/search match...I cannot see until now that you will need 300 types of match for a single column of data.... what kinda data-base you are doing...Have u tried the excel function like DAVERAGE(database,field,criteria) Database is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column. you may need it for the better in such huge data-base.. regards -- ***** birds of the same feather flock together.. "Tammy" wrote: dRILLER - is there a better way? Should I create some type of macro? Can I type all 300 If's in one cell? "driller" wrote: Hi again Tammy, i was surprised by the 300 ifs you need to build, and you still like to go forward with the Average IF...with CTRL-SHFT-ENT. so from my first and 2nd reply, wherein you need to extend the conditions to if Column A contains DFW and F 0, then I need G to = Bob try this =AVERAGE(if(((A1:A65000="DFW")*(F1:F650000%)*(G1: G65000="Bob")),F1:F65000)) and if column A contains AUS and F 0, then I need G to = Lynette =AVERAGE(if(((A1:A65000="AUS")*(F1:F650000%)*(G1: G65000="Lynette")),F1:F65000)) so on so on so on. you can see the pattern from the two sample formula... and press CTRL-SHFT-ENTER not just enter. regards -- ***** birds of the same feather flock together.. "Tammy" wrote: ditzy me - i wrote my question wrong again. But I have to add thank you for answering my original question and it solved part of the problem for me. But I have to take it one step even further. I need <bTHE AVERAGE IF</b average if Column A contains DFW and F 0, then I need G to = Bob average if column A contains AUS and F 0, then I need G to = Lynette so on so on so on. And there will be approx 300 If's. "Tammy" wrote: CSI - I will have approx 300 so on's. "csi.sec" wrote: 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 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
Hi driller - I tried that exactly and got #DIV/0!
Any thoughts please sir? "driller" wrote: Hi again Tammy, i was surprised by the 300 ifs you need to build, and you still like to go forward with the Average IF...with CTRL-SHFT-ENT. so from my first and 2nd reply, wherein you need to extend the conditions to if Column A contains DFW and F 0, then I need G to = Bob try this =AVERAGE(if(((A1:A65000="DFW")*(F1:F650000%)*(G1: G65000="Bob")),F1:F65000)) and if column A contains AUS and F 0, then I need G to = Lynette =AVERAGE(if(((A1:A65000="AUS")*(F1:F650000%)*(G1: G65000="Lynette")),F1:F65000)) so on so on so on. you can see the pattern from the two sample formula... and press CTRL-SHFT-ENTER not just enter. regards -- ***** birds of the same feather flock together.. "Tammy" wrote: ditzy me - i wrote my question wrong again. But I have to add thank you for answering my original question and it solved part of the problem for me. But I have to take it one step even further. I need <bTHE AVERAGE IF</b average if Column A contains DFW and F 0, then I need G to = Bob average if column A contains AUS and F 0, then I need G to = Lynette so on so on so on. And there will be approx 300 If's. "Tammy" wrote: CSI - I will have approx 300 so on's. "csi.sec" wrote: 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 |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
avg ?
hi again Tammy,
it is quite odd that the formula will result to a #DIV/0! error message...Better use auto-filter on the column where there are division formulas. Maybe there are uncompleted data results #DIV/0! along these column(s)...otherwise you can try and repost again your problem, surely many will reply to your meaningful post... regards and good luck -- ***** birds of the same feather flock together.. "Tammy" wrote: Hi driller - I tried that exactly and got #DIV/0! Any thoughts please sir? "driller" wrote: Hi again Tammy, i was surprised by the 300 ifs you need to build, and you still like to go forward with the Average IF...with CTRL-SHFT-ENT. so from my first and 2nd reply, wherein you need to extend the conditions to if Column A contains DFW and F 0, then I need G to = Bob try this =AVERAGE(if(((A1:A65000="DFW")*(F1:F650000%)*(G1: G65000="Bob")),F1:F65000)) and if column A contains AUS and F 0, then I need G to = Lynette =AVERAGE(if(((A1:A65000="AUS")*(F1:F650000%)*(G1: G65000="Lynette")),F1:F65000)) so on so on so on. you can see the pattern from the two sample formula... and press CTRL-SHFT-ENTER not just enter. regards -- ***** birds of the same feather flock together.. "Tammy" wrote: ditzy me - i wrote my question wrong again. But I have to add thank you for answering my original question and it solved part of the problem for me. But I have to take it one step even further. I need <bTHE AVERAGE IF</b average if Column A contains DFW and F 0, then I need G to = Bob average if column A contains AUS and F 0, then I need G to = Lynette so on so on so on. And there will be approx 300 If's. "Tammy" wrote: CSI - I will have approx 300 so on's. "csi.sec" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|