Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average If Statement (Excel 2003)
I am trying to find the formula for averaging cells in column B if the data
in Ais (a specific criteria). I've tried some formulas in the threads related to average, but nothing seems to work. Example: If A1:A110 equals 01 Then Average B1:B110 (cells related to the criteria in A1:A110) When using formulas, should cells have a specific format? Because there is text involved, I set the cell format to general. Do you suggest differently? Also, I'm working with data that I exported from a Microsoft Access 2007 database (SQL) but I'm using Excel 2003. Will this cause a problem? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average If Statement (Excel 2003)
See if this works:
Array entered** : =AVERAGE(IF(A1:A110="01",B1:B110)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) The problem is more than likely the 01. Excel doesn't recognize leading zeros as part of a numeric string. In order to get Excel to display leading zeros you either have to apply a custom number format or the entry has to be a TEXT string. In this case I'm "guessing" that 01 is a TEXT string. -- Biff Microsoft Excel MVP "--Viewpoint" wrote in message ... I am trying to find the formula for averaging cells in column B if the data in Ais (a specific criteria). I've tried some formulas in the threads related to average, but nothing seems to work. Example: If A1:A110 equals 01 Then Average B1:B110 (cells related to the criteria in A1:A110) When using formulas, should cells have a specific format? Because there is text involved, I set the cell format to general. Do you suggest differently? Also, I'm working with data that I exported from a Microsoft Access 2007 database (SQL) but I'm using Excel 2003. Will this cause a problem? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average If Statement (Excel 2003)
=AVERAGE(IF(A1:A100=1,B1:B100,""))
this is an array formula that must be enter with CNTRL-SHFT-ENTER rather than just the ENTER key. -- Gary''s Student - gsnu200821 "--Viewpoint" wrote: I am trying to find the formula for averaging cells in column B if the data in Ais (a specific criteria). I've tried some formulas in the threads related to average, but nothing seems to work. Example: If A1:A110 equals 01 Then Average B1:B110 (cells related to the criteria in A1:A110) When using formulas, should cells have a specific format? Because there is text involved, I set the cell format to general. Do you suggest differently? Also, I'm working with data that I exported from a Microsoft Access 2007 database (SQL) but I'm using Excel 2003. Will this cause a problem? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average If Statement (Excel 2003)
An alternative to an array formula would be to use sumif divided by countif:
=sumif(a1:a110,"01",b1:b110)/countif(a1:a110,"01") "--Viewpoint" wrote: I am trying to find the formula for averaging cells in column B if the data in Ais (a specific criteria). I've tried some formulas in the threads related to average, but nothing seems to work. Example: If A1:A110 equals 01 Then Average B1:B110 (cells related to the criteria in A1:A110) When using formulas, should cells have a specific format? Because there is text involved, I set the cell format to general. Do you suggest differently? Also, I'm working with data that I exported from a Microsoft Access 2007 database (SQL) but I'm using Excel 2003. Will this cause a problem? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average If Statement (Excel 2003)
Thanks. I did have that formula and it didn't work but once you wrote it, I
realized that the data was linked and that I had to add the sheet name. Once more question: Using the same formula, rather than using the equal sign, I want to select a group that contains PO1 or P02. How can I do that? "T. Valko" wrote: See if this works: Array entered** : =AVERAGE(IF(A1:A110="01",B1:B110)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) The problem is more than likely the 01. Excel doesn't recognize leading zeros as part of a numeric string. In order to get Excel to display leading zeros you either have to apply a custom number format or the entry has to be a TEXT string. In this case I'm "guessing" that 01 is a TEXT string. -- Biff Microsoft Excel MVP "--Viewpoint" wrote in message ... I am trying to find the formula for averaging cells in column B if the data in Ais (a specific criteria). I've tried some formulas in the threads related to average, but nothing seems to work. Example: If A1:A110 equals 01 Then Average B1:B110 (cells related to the criteria in A1:A110) When using formulas, should cells have a specific format? Because there is text involved, I set the cell format to general. Do you suggest differently? Also, I'm working with data that I exported from a Microsoft Access 2007 database (SQL) but I'm using Excel 2003. Will this cause a problem? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average If Statement (Excel 2003)
Thank you.
"Gary''s Student" wrote: =AVERAGE(IF(A1:A100=1,B1:B100,"")) this is an array formula that must be enter with CNTRL-SHFT-ENTER rather than just the ENTER key. -- Gary''s Student - gsnu200821 "--Viewpoint" wrote: I am trying to find the formula for averaging cells in column B if the data in Ais (a specific criteria). I've tried some formulas in the threads related to average, but nothing seems to work. Example: If A1:A110 equals 01 Then Average B1:B110 (cells related to the criteria in A1:A110) When using formulas, should cells have a specific format? Because there is text involved, I set the cell format to general. Do you suggest differently? Also, I'm working with data that I exported from a Microsoft Access 2007 database (SQL) but I'm using Excel 2003. Will this cause a problem? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average If Statement (Excel 2003)
Thanks. I actually tried this formula before posting my message and it
didn't work. However, I forgot that I had linked data on my worksheet and that I had to enter the sheet name into the formula. "bpeltzer" wrote: An alternative to an array formula would be to use sumif divided by countif: =sumif(a1:a110,"01",b1:b110)/countif(a1:a110,"01") "--Viewpoint" wrote: I am trying to find the formula for averaging cells in column B if the data in Ais (a specific criteria). I've tried some formulas in the threads related to average, but nothing seems to work. Example: If A1:A110 equals 01 Then Average B1:B110 (cells related to the criteria in A1:A110) When using formulas, should cells have a specific format? Because there is text involved, I set the cell format to general. Do you suggest differently? Also, I'm working with data that I exported from a Microsoft Access 2007 database (SQL) but I'm using Excel 2003. Will this cause a problem? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average If Statement (Excel 2003)
Try one of these array formulas** :
=AVERAGE(IF((A1:A10="P01")+(A1:A10="P02"),B1:B10)) Or, use cells to hold the criteria: D1 = P01 D2 = P02 =AVERAGE(IF((A1:A10=D1)+(A1:A10=D2),B1:B10)) =AVERAGE(IF(ISNUMBER(MATCH(A1:A10,D1:D2,0)),B1:B10 )) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "--Viewpoint" wrote in message ... Thanks. I did have that formula and it didn't work but once you wrote it, I realized that the data was linked and that I had to add the sheet name. Once more question: Using the same formula, rather than using the equal sign, I want to select a group that contains PO1 or P02. How can I do that? "T. Valko" wrote: See if this works: Array entered** : =AVERAGE(IF(A1:A110="01",B1:B110)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) The problem is more than likely the 01. Excel doesn't recognize leading zeros as part of a numeric string. In order to get Excel to display leading zeros you either have to apply a custom number format or the entry has to be a TEXT string. In this case I'm "guessing" that 01 is a TEXT string. -- Biff Microsoft Excel MVP "--Viewpoint" wrote in message ... I am trying to find the formula for averaging cells in column B if the data in Ais (a specific criteria). I've tried some formulas in the threads related to average, but nothing seems to work. Example: If A1:A110 equals 01 Then Average B1:B110 (cells related to the criteria in A1:A110) When using formulas, should cells have a specific format? Because there is text involved, I set the cell format to general. Do you suggest differently? Also, I'm working with data that I exported from a Microsoft Access 2007 database (SQL) but I'm using Excel 2003. Will this cause a problem? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average If Statement (Excel 2003)
This is the first chance I had to try the formula and it seems I typed it
correctly and it appears OK. Thanks for the help. "T. Valko" wrote: Try one of these array formulas** : =AVERAGE(IF((A1:A10="P01")+(A1:A10="P02"),B1:B10)) Or, use cells to hold the criteria: D1 = P01 D2 = P02 =AVERAGE(IF((A1:A10=D1)+(A1:A10=D2),B1:B10)) =AVERAGE(IF(ISNUMBER(MATCH(A1:A10,D1:D2,0)),B1:B10 )) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "--Viewpoint" wrote in message ... Thanks. I did have that formula and it didn't work but once you wrote it, I realized that the data was linked and that I had to add the sheet name. Once more question: Using the same formula, rather than using the equal sign, I want to select a group that contains PO1 or P02. How can I do that? "T. Valko" wrote: See if this works: Array entered** : =AVERAGE(IF(A1:A110="01",B1:B110)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) The problem is more than likely the 01. Excel doesn't recognize leading zeros as part of a numeric string. In order to get Excel to display leading zeros you either have to apply a custom number format or the entry has to be a TEXT string. In this case I'm "guessing" that 01 is a TEXT string. -- Biff Microsoft Excel MVP "--Viewpoint" wrote in message ... I am trying to find the formula for averaging cells in column B if the data in Ais (a specific criteria). I've tried some formulas in the threads related to average, but nothing seems to work. Example: If A1:A110 equals 01 Then Average B1:B110 (cells related to the criteria in A1:A110) When using formulas, should cells have a specific format? Because there is text involved, I set the cell format to general. Do you suggest differently? Also, I'm working with data that I exported from a Microsoft Access 2007 database (SQL) but I'm using Excel 2003. Will this cause a problem? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average If Statement (Excel 2003)
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "--Viewpoint" wrote in message ... This is the first chance I had to try the formula and it seems I typed it correctly and it appears OK. Thanks for the help. "T. Valko" wrote: Try one of these array formulas** : =AVERAGE(IF((A1:A10="P01")+(A1:A10="P02"),B1:B10)) Or, use cells to hold the criteria: D1 = P01 D2 = P02 =AVERAGE(IF((A1:A10=D1)+(A1:A10=D2),B1:B10)) =AVERAGE(IF(ISNUMBER(MATCH(A1:A10,D1:D2,0)),B1:B10 )) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "--Viewpoint" wrote in message ... Thanks. I did have that formula and it didn't work but once you wrote it, I realized that the data was linked and that I had to add the sheet name. Once more question: Using the same formula, rather than using the equal sign, I want to select a group that contains PO1 or P02. How can I do that? "T. Valko" wrote: See if this works: Array entered** : =AVERAGE(IF(A1:A110="01",B1:B110)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) The problem is more than likely the 01. Excel doesn't recognize leading zeros as part of a numeric string. In order to get Excel to display leading zeros you either have to apply a custom number format or the entry has to be a TEXT string. In this case I'm "guessing" that 01 is a TEXT string. -- Biff Microsoft Excel MVP "--Viewpoint" wrote in message ... I am trying to find the formula for averaging cells in column B if the data in Ais (a specific criteria). I've tried some formulas in the threads related to average, but nothing seems to work. Example: If A1:A110 equals 01 Then Average B1:B110 (cells related to the criteria in A1:A110) When using formulas, should cells have a specific format? Because there is text involved, I set the cell format to general. Do you suggest differently? Also, I'm working with data that I exported from a Microsoft Access 2007 database (SQL) but I'm using Excel 2003. Will this cause a problem? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average If Statement (Excel 2003)
So far so, good. But I think there might be an easier way to copy formulas
with arrays. Currently, I'm and using "fill right" but then I must adjust the "if" part of the array (that data is static for my purposes). Is there a method of copying formulas retaining the "if" part of the array so that I only have to change the remainer of the formula? "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "--Viewpoint" wrote in message ... This is the first chance I had to try the formula and it seems I typed it correctly and it appears OK. Thanks for the help. "T. Valko" wrote: Try one of these array formulas** : =AVERAGE(IF((A1:A10="P01")+(A1:A10="P02"),B1:B10)) Or, use cells to hold the criteria: D1 = P01 D2 = P02 =AVERAGE(IF((A1:A10=D1)+(A1:A10=D2),B1:B10)) =AVERAGE(IF(ISNUMBER(MATCH(A1:A10,D1:D2,0)),B1:B10 )) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "--Viewpoint" wrote in message ... Thanks. I did have that formula and it didn't work but once you wrote it, I realized that the data was linked and that I had to add the sheet name. Once more question: Using the same formula, rather than using the equal sign, I want to select a group that contains PO1 or P02. How can I do that? "T. Valko" wrote: See if this works: Array entered** : =AVERAGE(IF(A1:A110="01",B1:B110)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) The problem is more than likely the 01. Excel doesn't recognize leading zeros as part of a numeric string. In order to get Excel to display leading zeros you either have to apply a custom number format or the entry has to be a TEXT string. In this case I'm "guessing" that 01 is a TEXT string. -- Biff Microsoft Excel MVP "--Viewpoint" wrote in message ... I am trying to find the formula for averaging cells in column B if the data in Ais (a specific criteria). I've tried some formulas in the threads related to average, but nothing seems to work. Example: If A1:A110 equals 01 Then Average B1:B110 (cells related to the criteria in A1:A110) When using formulas, should cells have a specific format? Because there is text involved, I set the cell format to general. Do you suggest differently? Also, I'm working with data that I exported from a Microsoft Access 2007 database (SQL) but I'm using Excel 2003. Will this cause a problem? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average If Statement (Excel 2003)
If I understand, you want the reference to column A to be static?
Use absolute references: =AVERAGE(IF(($A1:$A10="P01")+($A1:$A10="P02"),B1:B 10)) -- Biff Microsoft Excel MVP "--Viewpoint" wrote in message ... So far so, good. But I think there might be an easier way to copy formulas with arrays. Currently, I'm and using "fill right" but then I must adjust the "if" part of the array (that data is static for my purposes). Is there a method of copying formulas retaining the "if" part of the array so that I only have to change the remainer of the formula? "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "--Viewpoint" wrote in message ... This is the first chance I had to try the formula and it seems I typed it correctly and it appears OK. Thanks for the help. "T. Valko" wrote: Try one of these array formulas** : =AVERAGE(IF((A1:A10="P01")+(A1:A10="P02"),B1:B10)) Or, use cells to hold the criteria: D1 = P01 D2 = P02 =AVERAGE(IF((A1:A10=D1)+(A1:A10=D2),B1:B10)) =AVERAGE(IF(ISNUMBER(MATCH(A1:A10,D1:D2,0)),B1:B10 )) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "--Viewpoint" wrote in message ... Thanks. I did have that formula and it didn't work but once you wrote it, I realized that the data was linked and that I had to add the sheet name. Once more question: Using the same formula, rather than using the equal sign, I want to select a group that contains PO1 or P02. How can I do that? "T. Valko" wrote: See if this works: Array entered** : =AVERAGE(IF(A1:A110="01",B1:B110)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) The problem is more than likely the 01. Excel doesn't recognize leading zeros as part of a numeric string. In order to get Excel to display leading zeros you either have to apply a custom number format or the entry has to be a TEXT string. In this case I'm "guessing" that 01 is a TEXT string. -- Biff Microsoft Excel MVP "--Viewpoint" wrote in message ... I am trying to find the formula for averaging cells in column B if the data in Ais (a specific criteria). I've tried some formulas in the threads related to average, but nothing seems to work. Example: If A1:A110 equals 01 Then Average B1:B110 (cells related to the criteria in A1:A110) When using formulas, should cells have a specific format? Because there is text involved, I set the cell format to general. Do you suggest differently? Also, I'm working with data that I exported from a Microsoft Access 2007 database (SQL) but I'm using Excel 2003. Will this cause a problem? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If statement then Average | Excel Discussion (Misc queries) | |||
excel 2003-countif statement | Excel Discussion (Misc queries) | |||
Excel 2003 and AVERAGE function | Excel Worksheet Functions | |||
Average Line - Excel 2003 | Charts and Charting in Excel | |||
Average with more than one IF statement | Excel Worksheet Functions |