Formula hell in '97

[kojak43]

I am just guessing what to do here.
I have job entry worksheets. One is where I enter all the data. I also have a sheet that shows rudimentary "statistics" That is, how many jobs on the list, how many are active, how many are done, how long they have been on the list, etc.

Right now there is only two symbols representing status, A=active and D=done. That is the "R" column. The "E" column represents the date the job was placed on the list. What I want to do is count all the A's that fall within a certain time frame. What I am tring to determine is those active jobs that have been on the list for at least 30 days.

This is my first attempted formula:

=AND(COUNTIF(DataEntry!E3:E2000,">="&TODAY()-30))*(COUNTIF(DataEntry!R3:R2000,"<>D"))
It advises there are 1996 active jobs.
I know this is incorrect, as the model I am using only has 48 total rows. Of that 46 are "A"'s and 2 are "D"'s. So this counts the empty cells right?

This is my second attempted formula
=AND(COUNTA(DataEntry!E3:E2000,">="&TODAY()-30))*(COUNTA(DataEntry!R3:R2000,"<>D"))
It yields 49. As there are only 46 cells that are true to this formula, where did the extra 3 come from?

What am I doing wrong?

 

[Aladin Akyurek]

On 2002-04-16 13:27, Yogi Anand wrote:

On 2002-04-16 11:32, kojak43 wrote:
A hand recount of my data shows that Adam's formula was correct with 20.
Aladin's formula also yields 20.
The hand count shows 20 A's, 2 D's and someone slipped in 3 "C's".
When I tested both formulas with <>D I get 23, the correct number.
When I tested Adam's formula with =D, I get 2,
with Aladin's it says 1. I've rechecked my keystrokes and they seem to mirror Aladin's message, so I don't understand why the difference. Any opinion here?
Aladin, also, if you have a chance, can you tell me why there is that very large number within the Match function, i.e. what does that do?
I'll do more research and learn what OFFSET does on my own.
Thanks you both.
K

Hi Kojak, Adam and Aladin:
I have looked into the difference in count of "D"s from Adam's formula and Aladin's formula. I will surmise that your last entry in column R is "D".
The reason I can say this is because Aladin's formulas for ranges for Dates and Status are short by 1 row ... tht is why the last D is not picked up by Aladin's formulas.
So to fix your ranges, if you go back to
INSERT|NAME|DEFINE -- change Dates to

=OFFSET(DataEntry!$E$3,0,0,DateRecs-ROW(DataEntry!$E$3)+1,1)

and Status to

=OFFSET(DataEntry!$R$3,0,0,DateRecs-ROW(DataEntry!$R$3)+1,1)

and you will get the right results both from Adam's and Aladin's formulas

Regards!

+1 was supposed to be there . Upon seeing the Kojak's WB I discovered the omission. Kojak has now the updated formulas.

BTW, the DateRecs-ROW(DataEntry!$E$3)+1 takes care of the possiblity of adding extra rows before the actual data row or removing rows that come before the actual data row.

Aladin

 

[kojak43]

On 2002-04-16 13:27, Yogi Anand wrote:

On 2002-04-16 11:32, kojak43 wrote:
A hand recount of my data shows that Adam's formula was correct with 20.
Aladin's formula also yields 20.
The hand count shows 20 A's, 2 D's and someone slipped in 3 "C's".
When I tested both formulas with <>D I get 23, the correct number.
When I tested Adam's formula with =D, I get 2,
with Aladin's it says 1. I've rechecked my keystrokes and they seem to mirror Aladin's message, so I don't understand why the difference. Any opinion here?
Aladin, also, if you have a chance, can you tell me why there is that very large number within the Match function, i.e. what does that do?
I'll do more research and learn what OFFSET does on my own.
Thanks you both.
K

Hi Kojak, Adam and Aladin:
I have looked into the difference in count of "D"s from Adam's formula and Aladin's formula. I will surmise that your last entry in column R is "D".
The reason I can say this is because Aladin's formulas for ranges for Dates and Status are short by 1 row ... tht is why the last D is not picked up by Aladin's formulas.
So to fix your ranges, if you go back to
INSERT|NAME|DEFINE -- change Dates to

=OFFSET(DataEntry!$E$3,0,0,DateRecs-ROW(DataEntry!$E$3)+1,1)

and Status to

=OFFSET(DataEntry!$R$3,0,0,DateRecs-ROW(DataEntry!$R$3)+1,1)

and you will get the right results both from Adam's and Aladin's formulas

Regards!

My Gawd, how do you people know this stuff?
Aladin fixes it in half a heartbeat, Yogi figures it out from the formula and Adam knew what to do first.
I am in awe.
Thank you all very much.
K