excel formula

  • Thread starter Thread starter sebasjuh
  • Start date Start date
S

sebasjuh

I have in column A1:A365 all dates of the year 2005 and in colum
B1:B365 the days of that date.

In column D1:D365, E1:D365 and F1;F365 I fill in a X for every actio
Succeeded.

SO if it is 1 January 2005 and the action in D1 and E1 and F1 are al
succeeded I fill in those fields an: X

I have in a cell a formula what checks how many % (procent) succeede
of the fields who are filled.
But when it is December he also calculates the X that are filled in A
etc.

Now I would like to make a formula which calculates how many action
went good in the last 30 days is this possible?
So that he only check the field form A1:A30 and automatically then goe
to A2:A31 etc.

I hope you guys understand what I am trying to explain :)
If not please tell me so I would try to explain it to you again...
 
sebasjuh,

Try something like:

=COUNTIF(OFFSET(B1,MATCH(DATEVALUE("1/11/2004"),A:A,FALSE)-10,2,10,3),"X")/9
0

HTH,
Bernie
MS Excel MVP
 
Of course, that version depends on the matched date being after the first 30
rows. To account for that, this will work even on shorter lists at the top
of the column:

=COUNTIF(OFFSET(B1,MAX(MATCH(DATEVALUE("1/11/2004"),A:A,FALSE)-30,0),2,MIN(3
0, MATCH(DATEVALUE("1/11/2004"),A:A,FALSE)),3),"X")/(3*MIN(30,
MATCH(DATEVALUE("1/11/2004"),A:A,FALSE)))

Format for percent.

HTH,
Bernie
MS Excel MVP

Bernie Deitrick said:
sebasjuh,

Try something like:

=COUNTIF(OFFSET(B1,MATCH(DATEVALUE("1/11/2004"),A:A,FALSE)-10,2,10,3),"X")/9
0

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Not sure if I understand, but I think you want to do this:

In G30 put the formula =COUNTA(D1:F30)/90
then fill it down the rest of that column and it will always look at all
three attributes for the last 30 days because the references for the range
will increment as the formula is filled down the column.

Carlos
 
Back
Top