|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 23rd, 2009, 05:51 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average function
I am trying to create a daily average in a spreadsheet. Here is my situation.
I have a row of sums of columns that I want to average. Data is filled into the columns daily in a month so there are days that have no data as that date hasn't gotten here yet, but there is a '0' in the sum row because of the sum function because there are line items in the column that are filled in when the day arrives. How do I create a 'running' DAILY average so that it does not count the '0' fields in the average? Example is dated columns, 4/1/2009, 4/2/2009, etc., each with lines items beneath that populate a 'sum' total say of volume. I want to create a running daily average field that shows what the average is as of the last data entered. I was thinking something like =Average (B180:i180) but this sure isn't working! Thanks! 
|
|
#2
April 23rd, 2009, 06:00 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average function
In general =AVERAGE() will ignore blanks, but accept zeros. One technique is
to force zeros to become blanks. For example, if you have =SUM(A1:A10) use: =IF(SUM(A1:A10)=0,"",SUM(A1:A10)) -- Gary''s Student - gsnu200848 "Harriet" wrote: I am trying to create a daily average in a spreadsheet. Here is my situation. I have a row of sums of columns that I want to average. Data is filled into the columns daily in a month so there are days that have no data as that date hasn't gotten here yet, but there is a '0' in the sum row because of the sum function because there are line items in the column that are filled in when the day arrives. How do I create a 'running' DAILY average so that it does not count the '0' fields in the average? Example is dated columns, 4/1/2009, 4/2/2009, etc., each with lines items beneath that populate a 'sum' total say of volume. I want to create a running daily average field that shows what the average is as of the last data entered. I was thinking something like =Average (B180:i180) but this sure isn't working! Thanks!
|
|
#3
April 23rd, 2009, 06:03 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average function
=SUM(B18:H18)/COUNTIF(B18:H18,"0")
If this post helps click Yes --------------- Jacob Skaria "Harriet" wrote: I am trying to create a daily average in a spreadsheet. Here is my situation. I have a row of sums of columns that I want to average. Data is filled into the columns daily in a month so there are days that have no data as that date hasn't gotten here yet, but there is a '0' in the sum row because of the sum function because there are line items in the column that are filled in when the day arrives. How do I create a 'running' DAILY average so that it does not count the '0' fields in the average? Example is dated columns, 4/1/2009, 4/2/2009, etc., each with lines items beneath that populate a 'sum' total say of volume. I want to create a running daily average field that shows what the average is as of the last data entered. I was thinking something like =Average (B180:i180) but this sure isn't working! Thanks!
|
|
#4
April 23rd, 2009, 06:09 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average function
It worked!!! Thanks for sharing the knowledge!
Harriet "Gary''s Student" wrote: In general =AVERAGE() will ignore blanks, but accept zeros. One technique is to force zeros to become blanks. For example, if you have =SUM(A1:A10) use: =IF(SUM(A1:A10)=0,"",SUM(A1:A10)) -- Gary''s Student - gsnu200848 "Harriet" wrote: I am trying to create a daily average in a spreadsheet. Here is my situation. I have a row of sums of columns that I want to average. Data is filled into the columns daily in a month so there are days that have no data as that date hasn't gotten here yet, but there is a '0' in the sum row because of the sum function because there are line items in the column that are filled in when the day arrives. How do I create a 'running' DAILY average so that it does not count the '0' fields in the average? Example is dated columns, 4/1/2009, 4/2/2009, etc., each with lines items beneath that populate a 'sum' total say of volume. I want to create a running daily average field that shows what the average is as of the last data entered. I was thinking something like =Average (B180:i180) but this sure isn't working! Thanks!
|
|
#5
April 23rd, 2009, 06:22 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average function
Thank for you for sharing your knowledge!
Harriet "Jacob Skaria" wrote: =SUM(B18:H18)/COUNTIF(B18:H18,"0") If this post helps click Yes --------------- Jacob Skaria "Harriet" wrote: I am trying to create a daily average in a spreadsheet. Here is my situation. I have a row of sums of columns that I want to average. Data is filled into the columns daily in a month so there are days that have no data as that date hasn't gotten here yet, but there is a '0' in the sum row because of the sum function because there are line items in the column that are filled in when the day arrives. How do I create a 'running' DAILY average so that it does not count the '0' fields in the average? Example is dated columns, 4/1/2009, 4/2/2009, etc., each with lines items beneath that populate a 'sum' total say of volume. I want to create a running daily average field that shows what the average is as of the last data entered. I was thinking something like =Average (B180:i180) but this sure isn't working! Thanks!
|
|
#6
April 23rd, 2009, 06:25 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average function
Cheers..
If this post helps click Yes --------------- Jacob Skaria "Harriet" wrote: Thank for you for sharing your knowledge! Harriet "Jacob Skaria" wrote: =SUM(B18:H18)/COUNTIF(B18:H18,"0") If this post helps click Yes --------------- Jacob Skaria "Harriet" wrote: I am trying to create a daily average in a spreadsheet. Here is my situation. I have a row of sums of columns that I want to average. Data is filled into the columns daily in a month so there are days that have no data as that date hasn't gotten here yet, but there is a '0' in the sum row because of the sum function because there are line items in the column that are filled in when the day arrives. How do I create a 'running' DAILY average so that it does not count the '0' fields in the average? Example is dated columns, 4/1/2009, 4/2/2009, etc., each with lines items beneath that populate a 'sum' total say of volume. I want to create a running daily average field that shows what the average is as of the last data entered. I was thinking something like =Average (B180:i180) but this sure isn't working! Thanks!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
