Thanks:  0
Likes:  0

# Thread: Formula hell in '97

1. 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.
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
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.

2. 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.
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
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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•