Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Formula hell in '97

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

    Default

    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?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

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

    Default

    Thanks Adam. That formula yields 20.

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

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

    Default

    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

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  8. #8
    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 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?

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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


  10. #10
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

Some videos you may like

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
  •