How to get date averages

K

Kathryn J Bittman

Here is the problem:
A is application date
11/01/2005
11/01/2005
11/01/2005
B is process date
11/07/2005
11/09/2005
11/12/2005

My boss built in a formula (I haven't seen it yet) to find the difference
between the dates and then copied that formula into 120 rows of data, but
now he wants a summary sheet showing the average length of time, the minimum
time and the maximum time. Because it is all user-defined, everything we
have tried still calculates all the "zeros" into the average. Solutions? or
do I need to clarify more?
 
B

Biff

Hi!

Array entered using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(C1:C100>0,C1:C100))

Biff
 
K

Kathryn J Bittman

Thanks..I tried it out, but still counting the zeros. Here is a sample:

App Date
Process Date

Num Days


11/1/2005
11/3/2005

2
0.22

11/1/2005
11/3/2005

2


11/1/2005
11/5/2005

4


11/1/2005
11/5/2005

4


11/1/2005
11/7/2005

6


11/1/2005
11/9/2005

8





0





0





0





0





0





0





0




The actual average should be 4.33, not 0.22

ideas?
 
K

Kathryn J Bittman

Sorry, the formatting didn't work.

Here is the sample again.

App Date Process Date Num Days
11/01/2005 11/03/2005 2
11/01/2005 11/03/2005 2
11/01/2005 11/03/2005 4
11/01/2005 11/05/2005 4
11/01/2005 11/07/2005 6
11/01/2005 11/09/2005 8
0
0
0

Using a total of 120 rows, the formula returns a value of 0.22, the actual
answer is 4.33.

Thanks in advance for the help!
 
B

Biff

Hi!
The actual average should be 4.33, not 0.22
Click to expand...

I get 4.33.

If I do =AVERAGE(C1:C10) I get 2.6 (which includes the zeros).

Have no idea why you would get 0.22.

Biff
 
M

Max

11/01/2005 11/03/2005 4
Click to expand...
Think there's an error in the data line above (3rd data line),
it should be 2, not 4

Anyway, guess we could also try these slight variations
(all to be array-entered, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER)

=AVERAGE(IF(C2:C120<>0,C2:C120))
=AVERAGE(IF((A2:A120<>"")*(B2:B120<>""),C2:C120))

Based on the sample data, with the 3rd data line corrected to "2",
both variations should yield identical results, i.e.: 4

But if we forgot to array-enter the formulas,
then the wrong results returned would be: 2.67
 
K

Kathryn J Bittman

I have a sheet with a range from 2:121 with 6 rows of current data. Hence
the 0.22. I need a formula that will ignore the zeros without having to
constantly change the range.
 
K

Kathryn J Bittman

THANKS!

It was the ctrl-shift-enter I wasn't getting.

Never used an array before. Working great now!
 
Top