Formula hell in '97 - Page 2
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: Formula hell in '97

  1. #11
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,798
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

     
    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

  2. #12
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com