Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieve Specific Info (1997)

    I have a spreadsheet (SEE ATTACHED) that I would like to retrieve information from. It consists of 3 sheets (GQL Test, GQL Data & GQL Graph. Within the GQL Test sheet I want to take column C and glean any number >=101:<=209 & >=601:<=699 that corresponds to MP 1088.0 to MP 1193.0 and paste it in the GQL Data sheet next to a specific Mile Post (Column A). (In the GQL Test sheet, the company inserts into column E the milepost in this format

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Retrieve Specific Info (1997)

    Not sure I understand completely (so what is new). This ARRAY formula (confirm with ctrl-shift-enter): placed in (eg)B2

    =SUM(IF(((LEFT('GQL Test'!$E$1:$E$1000,7))=("MP "&TEXT(A2,"0000")))*ISNUMBER('GQL Test'!$C$1:$C$1000)*('GQL Test'!$C$1:$C$1000>=101)*('GQL Test'!$C$1:$C$1000<=209),'GQL Test'!$B$1:$B$1000))

    and copied down the column will check the "MP number" vs A2 and also test that the row in C >=101 and C<=209. FOr all the rows that that is true it will sum the values in COl B

    Similarly:
    =SUM(IF(((LEFT('GQL Test'!$E$1:$E$1000,7))=("MP "&TEXT(A2,"0000")))*ISNUMBER('GQL Test'!$C$1:$C$1000)*('GQL Test'!$C$1:$C$1000>=601)*('GQL Test'!$C$1:$C$1000<=699),'GQL Test'!$B$1:$B$1000))

    Will look at the 601-699 range. Expand the range from 1:1000 to whatever a desired


    Steve

  3. #3
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve Specific Info (1997)

    Steve, I want to look at column B in the GQL Data Page for ONLY the ranges of 101 - 209 and 601 - 699 (The company puts in ranges from 001 - 1000) and have the amount of tests shown for each mile post as it goes down the line to the nearest whole number and have the information from columns B & E in the GQL Test page entered into column B in the GQL Data page. So, for instance, if I wanted to view the data in the in the GQL Data page for the Mile Post(s) listed (Mile post 1118 for instance) I would get the amount of test done for that mile post (acquired from columns B & E in the GQL Test page) and placed in column B in the GQL Data page.

    Will the formula cover just the specified test numbers (101-209 & 601-699) and the specified mile posts? We are only tested for these specific test numbers on the trains. All other test numbers are for other crafts. I only want to glean tests for train personnel.

    The other thing is that I want to build a graph in the GQL Graph sheet to reflect the results with the column showing total number of tests and row showing the mile post (in other words show how many tests for each mile post)

    Thanx

    Larry

  4. #4
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve Specific Info (1997)

    Steve, Look @ the attached, GQL Data page. There should be only one (1) test performed for mile post 1088 in this scenario. I would like to combine columns E & F in the GQL Data sheet into one formula and place that formula into column B if possible.

    Thanx

    Larry

  5. #5
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve Specific Info (1997)

    Steve,

    Have tried in vain to get the formula to work correctly. Please see attached. Please compare amount of tests for a given milepost (hopefully rounded to the nearest whole number). I think I'm close, but am missing it.

    Larry

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Retrieve Specific Info (1997)

    I think I answered your questions in my last post, but some other suggestions:
    To learn more about arrays:
    A "primer" by Chip Pearson and an article by Bob Umlas

    Also, This data set, for this example, is a much better dataset, than in your other examples in this thread, since it has a variety of possible outputs. The original set did not truly "test" the conditons.

    Also if you would try to provide the "correct answers" (manually determined if need be) for the set, we would know what to compare them to, and better understand your goals.

    Steve

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Retrieve Specific Info (1997)

    <P ID="edit" class=small>(Edited by sdckapr on 18-Jan-04 08:54. Corrected mistake in truncating note)</P>1) I mentioned this in the origianl post, the formula is an ARRAY. Do not confirm it with <enter>, confirm it with ctrl-shift-enter [if done properly, excel will add "squiggly brackets" around it ({}). Go to a cell, go inot edit mode (F2), then confirm with ctrl-shift-enter

    2) You were not clear in your question or the sheet. I thought that col b had the number of tests done. I quess I will assume it is a "type of test". You get the "Sum" = 2 for this case since column B, has a 2 in it.

    3) If you want the sum of the 2 conditionals that is possible

    Using Point 2 and 3 together I get:
    =SUM(IF(((LEFT('GQL Test'!$E$1:$E$1000,7))=("MP "&TEXT(A2,"0000")))*ISNUMBER('GQL Test'!$C$1:$C$1000)*(('GQL Test'!$C$1:$C$1000>=101)*('GQL Test'!$C$1:$C$1000<=209)+('GQL Test'!$C$1:$C$1000>=601)*('GQL Test'!$C$1:$C$1000<=699)),1))

    Remember point 1 (ctrl-shift-enter to confirm the formula)
    What the formula is counting the number of times:

    1)number in Col E = "mile number"
    AND
    2) Col C is a number
    AND EITHER
    3a) 101<=Col c <=209
    OR
    3a) 601<=Col c <=699

    Note the comparison (in 1) is not based on rounding but on truncating. "MP 1088.2", "MP1088.8", etc will all match 1088, but "MP1087.8" will not.

    If this is an issue, your best bet is to add a column (eg Col Q, in 'GQL Test') with the formula in Q2 (and copied down the row:

    =IF(ISBLANK(E2),0,ROUND(VALUE(MID(E2,4,6)),0))

    Then use the formula
    =SUM(IF(('GQL Test'!$Q$1:$Q$1000=A2)*(LEFT('GQL Test'!$E$1:$E$1000,2)="MP")*ISNUMBER('GQL Test'!$C$1:$C$1000)*(('GQL Test'!$C$1:$C$1000>=101)*('GQL Test'!$C$1:$C$1000<=209)+('GQL Test'!$C$1:$C$1000>=601)*('GQL Test'!$C$1:$C$1000<=699)),1))

    But it requires an intermediate col

    You will NOT be able (at least I have not thought of a way) to add this IF to the array formula directly, unless you define the ranges so it does not get any blank rows (use dynamic range names perhaps), since the part of the formula:
    ROUND(VALUE(MID('GQL Test'!$E$1:$E$1000,4,6)),0)
    you would add to the array would yield an error instead of a True/false.

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve Specific Info (1997)

    Steve,

    I am trying to retrieve ALL tests done at a specific mile post (Rounding to the nearest whole number) in the GQL Test page. When looking @ the GQL Test page, I want my formula to look at column E and retrieve the number of tests done at the mile post and round it to the nearest whole number, and place the total number of tests done from that location into column B in the GQL Data page next to it's respective mile post number. In the attached, the proper answer in column B of the GQL Data sheet are as follows:

    B2 1 Look @ GQL Test Page E2
    B3 2 Look @ GQL Test Page E3 & E8
    B4 1 Look @ GQL Test Page E4
    B5 1 Look @ GQL Test Page E5
    B6 1 Look @ GQL Test Page E6
    B7 1 Look @ GQL Test Page E7
    B8 1 Look @ GQL Test Page E10
    B9 1 Look @ GQL Test Page E9
    B10 0 No tests done @ mile post 1096

    Hopes this helps

    Thanx

    Larry

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Retrieve Specific Info (1997)

    <P ID="edit" class=small>(Edited by sdckapr on 18-Jan-04 10:10. Corrected some typos)</P>1) The formula I gave[=IF(ISBLANK(E2),0,ROUND(VALUE(MID(E2,4,6)),0))] goes in col Q of the 'GQL Test' sheet not in the 'GQL Data' Sheet
    2)This is required: you still have not confirmed with ctrl-shift-enter

    select the formula in 'GQL Data'!B2,
    <F2> (to get into edit mode)
    ctrl-shift-enter (all three keys at the same time)
    [if done correctly the formula bar will show:
    {=SUM(IF(('GQL Test'!$Q$1:$Q$1000=A2)*(LEFT('GQL Test'!$E$1:$E$1000,2)="MP")*ISNUMBER('GQL Test'!$C$1:$C$1000)*(('GQL Test'!$C$1:$C$1000>=101)*('GQL Test'!$C$1:$C$1000<=209)+('GQL Test'!$C$1:$C$1000>=601)*('GQL Test'!$C$1:$C$1000<=699)),1))}
    Note the formula is enclosed in "squiggly brackets" by excel - do NOT add them yourself]

    Copy B2 to B3:B10


    3) I get a different number in B7. You claim the number of tests is 1, I get 0 since the "No" in Col C is 222 which is NOT between 101and 209, nor between 601 and 699. Have I misunderstood the criteria? I thought we were only choosing within those 2 ranges of Col C "No"s.

    Steve

  10. #10
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve Specific Info (1997)

    You are correct. I apologise.

    Thanx

    Larry

Posting Permissions

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