Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Function
Is there anyway to establish an Average function that adjusts to only
populated fields? Example: I have a spreadsheet established that pulls from 12 other sheets. As a conseqence,the fields on this sheet only update each month (the other sheets are monthly sheets, and thus each one is used within the appopriate month). I would like to AVERAGE the fields on the13th sheet, showing a monthly average of the values derived from the other 12. However, I want the AVERAGE to reflect only the populated fields, so that I receive an accurate "running" average. Ex: if only January and February have data, then the average would be divided by 2, not all 12 fields. Is there any way to accomplish this? I figure it is a combination of the IF and AVERAGE formulas, but I've no idea how to do this. Thank you in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Function
"Stacy C" wrote:
I want the AVERAGE to reflect only the populated fields, so that I receive an accurate "running" average. Ex: if only January and February have data, then the average would be divided by 2, not all 12 fields. Are you using AVERAGE, as you say, or AVERAGEA? AVERAGE, not AVERAGEA, behaves the way you wish, ignoring empty cells as well as cells with text, notably "" (null string). I suspect the root cause of your problem is the way in which you are pulling data from the other worksheets into the 13th worksheet. Use a formula like the following: =if(sheet2!A1="", "", sheet2!A1) If you do simply =sheet2!A1, you will get zero instead of "" in the corresponding cell in the 13th worksheet. ----- original message ----- "Stacy C" wrote in message ... Is there anyway to establish an Average function that adjusts to only populated fields? Example: I have a spreadsheet established that pulls from 12 other sheets. As a conseqence,the fields on this sheet only update each month (the other sheets are monthly sheets, and thus each one is used within the appopriate month). I would like to AVERAGE the fields on the13th sheet, showing a monthly average of the values derived from the other 12. However, I want the AVERAGE to reflect only the populated fields, so that I receive an accurate "running" average. Ex: if only January and February have data, then the average would be divided by 2, not all 12 fields. Is there any way to accomplish this? I figure it is a combination of the IF and AVERAGE formulas, but I've no idea how to do this. Thank you in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Function
I am using Average, but as you say, it is the pull of the data. I will adjust
my pull method and try it out. Thank you. "JoeU2004" wrote: "Stacy C" wrote: I want the AVERAGE to reflect only the populated fields, so that I receive an accurate "running" average. Ex: if only January and February have data, then the average would be divided by 2, not all 12 fields. Are you using AVERAGE, as you say, or AVERAGEA? AVERAGE, not AVERAGEA, behaves the way you wish, ignoring empty cells as well as cells with text, notably "" (null string). I suspect the root cause of your problem is the way in which you are pulling data from the other worksheets into the 13th worksheet. Use a formula like the following: =if(sheet2!A1="", "", sheet2!A1) If you do simply =sheet2!A1, you will get zero instead of "" in the corresponding cell in the 13th worksheet. ----- original message ----- "Stacy C" wrote in message ... Is there anyway to establish an Average function that adjusts to only populated fields? Example: I have a spreadsheet established that pulls from 12 other sheets. As a conseqence,the fields on this sheet only update each month (the other sheets are monthly sheets, and thus each one is used within the appopriate month). I would like to AVERAGE the fields on the13th sheet, showing a monthly average of the values derived from the other 12. However, I want the AVERAGE to reflect only the populated fields, so that I receive an accurate "running" average. Ex: if only January and February have data, then the average would be divided by 2, not all 12 fields. Is there any way to accomplish this? I figure it is a combination of the IF and AVERAGE formulas, but I've no idea how to do this. Thank you in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average function | Excel Discussion (Misc queries) | |||
Is it possible to use address function in average function | Excel Discussion (Misc queries) | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
I want to use the MATCH function with the AVERAGE function but I . | Excel Worksheet Functions | |||
How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions |