Can I calculate how many days have been done?

[Nybbe]

I have two columns, one with planned start date and the next with planned end date. Is it possible to create a formula that calculates the average progression in procent?
I want to calculate the progression in procent per line (0-100%), and then calculate the average of these procents.
I can make it by adding an extra column, with the formula
=IF(TODAY()<G6,0,IF(TODAY()>H6,1,(TODAY()-G6)/(H6-G6)))
(column G is startdate and column H is enddate).
I can then get the average of this new column.

But is it possible to do this in one formula?
I tried this CSE-formula which is basicly the same as above, but with average around and entered with Ctrl+Shift+Enter. It does does not give me the correct result though:
=AVERAGE(IF(TODAY()<(Activities!$G$6:$G$500),0,IF(TODAY()>(Activities!$H$6:$H$500),1,(TODAY()-(Activities!$G$6:$G$500))/((Activities!$H$6:$H$500)-(Activities!$G$6:$G$500)))))

I am running excel 97.

 

[s-o-s]

Hi Nybbe,

I have slightly amended your array formula but this works see formula below in I18.


Hope this helps.

Book1
	G	H	I	J
1	Start	End	%Complete
2	01/08/02	31/08/02	73%
3	02/08/02	31/08/02	72%
4	03/08/02	31/08/02	71%
5	04/08/02	31/08/02	70%
6	05/08/02	31/08/02	69%
7	06/08/02	31/08/02	68%
8	07/08/02	20/08/02	100%
9	08/08/02	20/08/02	100%
10	09/08/02	20/08/02	100%
11	10/08/02	20/08/02	100%
12	11/08/02	31/08/02	60%
13	12/08/02	31/08/02	58%
14	13/08/02	31/08/02	56%
15	14/08/02	31/08/02	53%
16
17	CheckUsingColumn		75.08%
18	UsingArrayFormula		75.08%
Sheet2