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

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

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

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

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

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

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