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
|
|||
|
|||
Help with a formula
Alright here is what I have
- a column that I fill in either a 1 or 0 for each corresponding date. - I have another Cell for which I am calculating the average of the 1's based on the total number of cells. eg: =SUM(B5:B39)/ROWS(B5:B22) The Problem: I am filling in either a 1 or 0 each day depending on requirements and the average only works when all the cells are filled in with a one or a zero. I am looking a formula that will do something similar to the following: SUM([ALL_ROWS]) / ROWS([IF_CONTAINING_DATA]) Any ideas would be appreciated Regards, Diilb |
#2
|
|||
|
|||
I think I'm reading you right, you want to take an average of values only if
they have a 1 in the 1's and 0's column? In this case...assume the column containing 1's and 0's is A and the values you want to average are in column B. =AVERAGE(IF(A1:A1000=1,B1:B1000,"") Change the 1000 to wherever your data ends. Does that help? -- Regards, Dave "Diilb" wrote: Alright here is what I have - a column that I fill in either a 1 or 0 for each corresponding date. - I have another Cell for which I am calculating the average of the 1's based on the total number of cells. eg: =SUM(B5:B39)/ROWS(B5:B22) The Problem: I am filling in either a 1 or 0 each day depending on requirements and the average only works when all the cells are filled in with a one or a zero. I am looking a formula that will do something similar to the following: SUM([ALL_ROWS]) / ROWS([IF_CONTAINING_DATA]) Any ideas would be appreciated Regards, Diilb |
#3
|
|||
|
|||
Forgot to mention one thing... that function is an array formula so you will
need to use ctrl+shift+enter to enter it in. -- Regards, Dave "DaveB" wrote: I think I'm reading you right, you want to take an average of values only if they have a 1 in the 1's and 0's column? In this case...assume the column containing 1's and 0's is A and the values you want to average are in column B. =AVERAGE(IF(A1:A1000=1,B1:B1000,"") Change the 1000 to wherever your data ends. Does that help? -- Regards, Dave "Diilb" wrote: Alright here is what I have - a column that I fill in either a 1 or 0 for each corresponding date. - I have another Cell for which I am calculating the average of the 1's based on the total number of cells. eg: =SUM(B5:B39)/ROWS(B5:B22) The Problem: I am filling in either a 1 or 0 each day depending on requirements and the average only works when all the cells are filled in with a one or a zero. I am looking a formula that will do something similar to the following: SUM([ALL_ROWS]) / ROWS([IF_CONTAINING_DATA]) Any ideas would be appreciated Regards, Diilb |
#4
|
|||
|
|||
This is the data that I am using (for example). The reason I want the
average is find out the % of times that the item is closed (that is why I am using 1 and 0's) . But since I fill in the data everyday, I want know the running average as I am entering the values. Rather then having a column for calculating the current average next to every date. I tried your formula and it was not exactly what I was looking for (besides it didn't work) I hope this helps better understand the problem. [A] [b] Date Closed July 31, 2005 0 August 1, 2005 1 August 2, 2005 0 August 3, 2005 0 August 4, 2005 1 August 5, 2005 1 August 6, 2005 0 August 7, 2005 0 August 8, 2005 1 August 9, 2005 1 August 10, 2005 1 August 11, 2005 1 August 12, 2005 0 August 13, 2005 1 August 14, 2005 0 August 15, 2005 0 August 16, 2005 1 August 17, 2005 1 August 18, 2005 August 19, 2005 August 20, 2005 August 21, 2005 August 22, 2005 August 23, 2005 August 24, 2005 August 25, 2005 August 26, 2005 August 27, 2005 August 28, 2005 August 29, 2005 August 30, 2005 August 31, 2005 September 1, 2005 September 2, 2005 September 3, 2005 |
#5
|
|||
|
|||
Diilb Wrote: Alright here is what I have - a column that I fill in either a 1 or 0 for each corresponding date. - I have another Cell for which I am calculating the average of the 1's based on the total number of cells. eg: =SUM(B5:B39)/ROWS(B5:B22) The Problem: I am filling in either a 1 or 0 each day depending on requirements and the average only works when all the cells are filled in with a one or a zero. I am looking a formula that will do something similar to the following: SUM([ALL_ROWS]) / ROWS([IF_CONTAINING_DATA]) Any ideas would be appreciated Regards, Diilb Hi Diilb Try this =SUM(A1:A7)/COUNTA(A1:A7), change the ranges to suit your data Hope this helps Paul -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=396884 |
#6
|
|||
|
|||
I was just searching the Help file for IF Statement... And found the
formula I was looking for: =SUM(B5:B35)/COUNTIF(B5:B39,"-1") Thanks for pointing me in the right direction Diilb |
#7
|
|||
|
|||
You could use a pivot table to accomplish this. Make the date (column A) the
page field. Make the "Closed" (column B) the data value. You don't need row fields. You can add the "Closed" to the data section 3 times if you want. Make one a "Sum of Closed" field. Make another a "Count of Closed field". Make the third a "Average of Closed" field. You do this by setting the pivot field setting properties. If a date does not yet have a value in the "Closed" column, it is not counted (and of course sumed), and not included in the average. You'll just have to refresh the pivot table (exclamation button) everytime you add data to your table. Hope this offers an alternative. Bill Horton "Diilb" wrote: Alright here is what I have - a column that I fill in either a 1 or 0 for each corresponding date. - I have another Cell for which I am calculating the average of the 1's based on the total number of cells. eg: =SUM(B5:B39)/ROWS(B5:B22) The Problem: I am filling in either a 1 or 0 each day depending on requirements and the average only works when all the cells are filled in with a one or a zero. I am looking a formula that will do something similar to the following: SUM([ALL_ROWS]) / ROWS([IF_CONTAINING_DATA]) Any ideas would be appreciated Regards, Diilb |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula checking multiple worksheets | sonic-the-mouse | Worksheet Functions | 11 | June 6th, 2005 06:37 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 08:51 PM |
Formula works in some cells of a colum but not others? | Jerry W. Lewis | Worksheet Functions | 4 | May 8th, 2004 04:50 AM |
generating formula with a formula | Lisa Linard | Worksheet Functions | 2 | November 27th, 2003 12:03 PM |
Copy formula problem | AWilliams722 | Worksheet Functions | 0 | November 13th, 2003 10:37 PM |