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?

 

[Asala42]

G'day

Have you tried something like:

=SUMPRODUCT((DataEntry!E3:E2000>=(TODAY()-30))+0,(DataEntry!R3:R2000="A")+0)

This should count the number of A's in column R under that date range.

Hope that helps somewhat,
Adam

 

[kojak43]

Thanks Adam. That formula yields 20.

 

[klb]

Have you tried:

an extra column in your dataentry sheet that has:

=If(and(not(isblank(e3)),r3<>"d",E3<=TODAY()-30),1,0)

Then copy the formula down the rest of the column.

Then on your statistics sheet simply sum the column with the if statement.


should work
This message was edited by klb on 2002-04-16 10:28
This message was edited by klb on 2002-04-16 10:29

 

[Aladin Akyurek]

On 2002-04-16 09:40, kojak43 wrote:
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?

Activate Insert|Define|Name.
Enter DateRecs as name in the Names in Worbook box.
Enter as formula in the Refers to box:

=MATCH(9.99999999999999E+307,DataEntry!$E:$E)

Activate Add. (Don't leave yet the Define Name window.)

Enter DATES as name in the Names in Worbook box.
Enter as formula in the Refers to box:

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

Activate Add. (Don't leave yet the Define Name window.)

Enter STATUS as name in the Names in Worbook box.
Enter as formula in the Refers to box:

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

Activate OK.

Don't be afraid of the apparent complexity of the foregoing. The names that are defined are now available for use in formulas anywhere in your workbook. They are dynamic name ranges, meaning that they will always refer to the ranges of interest even if you add new data to or remove data from them.

Use the following formula to compute the desired count:

=SUMPRODUCT((DATES>=TODAY()-30)*(STATUS="A"))

Note that you can put the conditions in cells of their own, say,

=TODAY()-30 in E1, and
A in E2, then change the above formula to:

=SUMPRODUCT((DATES>=E1)*(STATUS=E2))

Aladin
This message was edited by aladin akyurek on 2002-04-16 13:28

 

[kojak43]

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

 

[Aladin Akyurek]

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

That big number is the biggest number possible in Excel. The MATCH formula tries to find a closest match to this big number, failing to find one returns the row (or column) number of the last numeric cell. That's precisely what we are after to determine where a date or number range ends.

Aladin

 

[kojak43]

On 2002-04-16 11:40, Aladin Akyurek 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

That big number is the biggest number possible in Excel. The MATCH formula tries to find a closest match to this big number, failing to find one returns the row (or column) number of the last numeric cell. That's precisely what we are after to determine where a date or number range ends.

Aladin

Aladin, you are so cunning. Thanks for the big number answer.
Any idea why an =D returns 1 rather than 2?

 

[Aladin Akyurek]

On 2002-04-16 11:46, kojak43 wrote:

On 2002-04-16 11:40, Aladin Akyurek 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

That big number is the biggest number possible in Excel. The MATCH formula tries to find a closest match to this big number, failing to find one returns the row (or column) number of the last numeric cell. That's precisely what we are after to determine where a date or number range ends.

Aladin

Aladin, you are so cunning. Thanks for the big number answer.
Any idea why an =D returns 1 rather than 2?

Adam's formula is eq to mine. The only difference is in the range args. I don't see why they should differ wrt "D" as condition. Send over the WB if you want, it intrigues me too.

Aladin

aladin_akyurek@yahoo.com

 

[Yogi Anand]

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!