Results 1 to 10 of 10
Thread: Retrieve Specific Info (1997)

20040117, 21:24 #1
 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

20040117, 22:13 #2
 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 ctrlshiftenter): 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 601699 range. Expand the range from 1:1000 to whatever a desired
Steve

20040117, 23:49 #3
 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 (101209 & 601699) 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

20040118, 00:04 #4
 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

20040118, 03:19 #5
 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

20040118, 10:52 #6
 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

20040118, 12:54 #7
 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 18Jan04 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 ctrlshiftenter [if done properly, excel will add "squiggly brackets" around it ({}). Go to a cell, go inot edit mode (F2), then confirm with ctrlshiftenter
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 (ctrlshiftenter 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

20040118, 13:01 #8
 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

20040118, 14:10 #9
 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 18Jan04 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 ctrlshiftenter
select the formula in 'GQL Data'!B2,
<F2> (to get into edit mode)
ctrlshiftenter (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

20040118, 14:14 #10
 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