How To Calc How Far A 50% Event Is Off

This is somewhat complicated for a newbie to VBA or excel programming like
myself. I have data in excel that is arranged much like this below:

sample comp hzname
1 a A1
1 a A2
1 a Bt1
1 a Bt2
1 a R
2 b A
2 b Bt1
2 b Bt2
3 a A1
3 a A2
3 a Bt1
3 a Bt2
3 a R
4 a A1
4 a A2
4 a BC
4 a Bt

What I would like to do, probably with VB code because this will be expanded
upon, is compute a ratio that answers the question, "How many of the
"samples" with a "comp" value of "a" have a "hzname" value of "R", out of all
samples with comp value of a?" The thing that is tripping me up in
programming is that there are only 4 samples here, each sample has many
records. So the answer I am looking for in this example is "2 out of 3
samples with comp a have a hzname of R". I would then put the result in a
open cell in the spreadsheet. Any help??

Hello,
I have a set of data which includes 10 samples each having 775 possible data points. I want to see what is shared between these samples when I go down to 9, 8, 7, 6... n samples. I am hoping that I can do this with Visual Basic, or other Excel functions where I could essentially determine the average of this similarity after calculating what is shared between all possible combinations given the sample total I am on. For example if I want to see what is shared across 9 out of the 10 samples, then I would need to determine the average of this shared-ness between all possible combinations of 9. I would upload a dataset but they are too large. Let's say in A175 I have an ID and in columns A2 through A11 are the values corresponding to that ID for each sample, I want to take all possible combinations of n samples for this value. I apologize if this doesn't provide enough detail or is vague, I am relatively new to this type of analysis and am trying to teach myself in a limited timeframe. Thank you in advance.

-Provoxt

Hi All:

I have a sheet with sampling data, another sheet to calculate statistics.

each sample occupies a row worth of information under different heading in the columns. I assign each sample result a unique number. Most samples have a unique ID but some do not have one. Multiple samples are grouped to calulate a average.

I've already made a macro to copy user selection to stats sheet. But what I want to do now is this:

upon running the macro it should: sort by unique sample IDs, then by Date, then by Location, then by process, then by product, then by room/equipment. after this is done, it should be able to group the filtered values and copy values(from one column) to the stats sheet.

after filtering through & exporting to stats sheet, it should copy the filtered values under different heading to another sheet with the same column headings.

can anyone help me with this?

Thanks

Hi,
This is my problem and wonder if anyone can help

Event 1 Person A 70%
Person B 30%

Event 2 Person A 70%
Person B 30%

In order for either Person to win they must win both events, otherwise the event resets and is simply played again I.E Event 3 and Event 4

1. I can work out the chances of each person winning straight off

2. I can, using an IF calculation work out hte new % chance after the result of Event 1

3. I CANT!! work out how to input a formula which will either return a value of 1 ( Person winning event 1 has won event 2 ), or a value which reutrns me to the start of event 3 ( Person winning event 1 has lost event 2 )

Hope that makes sense and someone could help me as I'm tearing my hair out trying to get this right !!

Many thanks for your time

Hi All,

I have had some trouble and would greatly appreciate any help.
I will try and dot point this.
Any Cell numbers begining/finishing in " " relate to the entry with " "

Below is Table one with the information required. On Page Name: Results



Date Sampled "G12" "I12" "J12" "K12" "L12"
Water Type HPC FStrep TTColi EColi

4/05/2011 "Chlorine" "N/A" "N/A" "N/A" "N/A"
1/02/1915 Micro 15,000.00 <1 1.00 1.00
1/01/2011 Micro <1 1.00 <1 <1



Below is the Data I need to creat my required graphs on Page Name: Result Graph (Micro)

"A40" "B40" "C40" "D40" (ETC)
Samples HPC NON Exceedance HPC Exceedance (ETC)
"January" " " " " " "
Febuary -- -- -- --
March -- -- -- --


1.All Micro results for January on Page: "Results" are to be added as a individual sample and placed in Cell B40 on Page: "Result Graph"
2. On Page: "Results" - All January only HPC NON Exceedance are to added as a individual sample and placed in Cell C40 on Page "Result Graph"

Examples. If 2 samples were HPC exceedances in Febuary on "Results" Page then Cell D41 would display a result of 2.

Example 2. If 3 samples were HPC NON exceedances in January on "Results" Page then Cell C41 would display a result of 3.

Example 3. 15 Micro samples were taken in March some exceedances some not. Page "Result Graph" Cell B42 will display 15. (All samples taken for the month)

Note: all results for each month on the year will be displayed together on the same Page (Results)



This may be hard. It is something i need to achieve even if it requires restructuring my pages all together.


I am very appreciative for any help!

Cheers.

I tried a search for something similar but didnt find anything that would work. I think this is going to require a VBA function but I am not familiar with that at all.

Because excel does not allow formatting to stay through cell linking I lose all of my bold formats when I link a cell. I have a cell that contains the following:

IS(2) FOR SAMPLES
ISO(2) FOR SAMPLES
ISOT(2) FOR SAMPLES
THCI(2) FOR SAMPLES
TW(336) RUN ALL

I need all of the text after the ")" to be bold. The actualy SKU's, numbers, and parenthesees I do not want bolded. This is probably a real quick thing but I have no VBA experience.

I need this to work for cells:
C2:D9
H2:I9
M7:n7
C12:d18
h12:i20

Thanks in advance!

Here is my problem:

I have a list of days and hours, and data for whether a type of event either did or did not occur for each sampled hour. I need to return a new value, some sort of "Event Day" value, which will essentially tell me if an event occurred at least once for each day.

Attached is a sheet with 4 days' worth of data. Jan 1st and 3rd 2006 are my two "Event Days".

To complicate things, I can't be sure I have 24 hour readings for each day, so I can't assume each day is a data range (e.g. A1:A24). Hours might be missing. (As such, I am trying to organise along the lines of IF the preceding cell is the same date...)

I also have lots of data stored as months, so would love a formula I could just autofill down. All it needs to do is effectively flag up for me when a day has experienced one event.

I'm really stuck, and not sure if I can return my request. Can anyone recommend a method of doing this please?

Many thanks!

For quite some time I have had a problem that I can't seem to find a solution for. I was hoping that with your expertise you would be able to help me. What happens is the following:

A client gives us a spreadsheet with the samples that they sent to us. The samples are entered in a particular order by the client. In this case the "worksheet from client" is ordered using the position in the box the sample occupies. The client wants the results placed into this sheet after we run the samples. When I run my samples I can pull the results into a spreadsheet off of the instrument, but they are not in the same order as the "spreadsheet from client". The machine prints results in the order that they finish so I have no control over the order they are placed into my spreadsheet when I pull them. I know how to do a sort in excel, but unfortunately the samples are not in numeric order from the client either.

Is there a way to pull the answers from "results" and match them to the correct line in "worksheet from client" so I don't have to copy and paste, try to sort and match, or enter all data by hand? I apologize if this seems like a terribly easy question, but I am not very familiar with writing formulas or macros in excel and when I have tried to do it I can't make anything work the way I need it to.

I appreciate any insight you can offer into this problem.


Here is a small example of a spreadsheet from client
patient ID Box # Row Column 96184 1 1 1 96745 1 1 2 97228 1 1 3 97611 1 1 4 95155 1 1 5 250011 2 1 6 95016 2 1 7 95071 3 1 8 98976 3 1 9 99273 5 2 1

Here is a small example of the results spreadsheet from my machine

Sample ID Test Results 250011 < 0.015 95071 2909 96745 0.054 95016 12228 97611 < 0.015 96184 2955 97228 < 0.015 95155 < 0.015 98976 < 0.015 99273 311



both small examples of spreadsheets that contain thousands of patients.

Can I have a formula set up like this:

="Water Samples: " & C3 & ". (Test for Chlorine and leave both samples in the Lab for testing.) " & C4 & " Shift Only"


And get a result with the following formatting of only some of the text (bold/underline)?

Water Samples: Handwash Sink. Test for Chlorine and leave both samples in the Lab for testing. (1st Shift Only)

Hi everybody,

Firstly in cells A2:A10 I have 9 teams in cells B2:K10 where I have predict result for 10 games.

After the result declared I put them in cells B13:K21,

In cell B13 I have formula conditional formatting =ISERROR(FIND(B14,B2)) and copied format in cells B13:K21, which highlight in red if result is failed as per prediction in cells B2:K10

Now I want to count failed predict instance has been occurred continuous for each team as shown in cells B24:K32
For example: prediction for Team2 has failed in game 5 so far in cell F25 count 1 and then failed again in game 6 so far in cell G25 count will be 2 and count must continue till result is failed continuously, as shown till 5 instance been failed and will be same for team 7 & 9 as shown.

Example Table:
* A B C D E F G H I J K 1 Team Predict for game 1 Predict for game 2 Predict for game 3 Predict for game 4 Predict for game 5 Predict for game 6 Predict for game 7 Predict for game 8 Predict for game 9 Predict for game 10 2 1 1X 1X X2 1 X2 1 1 1X 2 1X 3 2 2 1X 1X 1X 2 1 X2 1 1 1 4 3 X2 1 X2 1X 1X X2 1X 1X 1X 1X 5 4 X2 1 1 1X X2 1 1 1 1X 1 6 5 2 1 X2 X2 1 1X 1 1X 1X 1 7 6 1 1 1X 1X 1X 1X 1X 1X 1 1X 8 7 X2 1X 1X 1 1 1X X2 1 X2 X2 9 8 1X X2 1 1 1X 1 2 1X 1X 1X 10 9 X2 1 2 1X X2 1 1 1X 1 1X 11 12 Team Result for game 1 Result for game 2 Result for game 3 Result for game 4 Result for game 5 Result for game 6 Result for game 7 Result for game 8 Result for game 9 Result for game 10 13 1 X 1 2 1 1 1 2 1 2 1 14 2 2 1 X 1 X X 1 2 X 1 15 3 1 1 2 X 1 X X 1 1 X 16 4 2 2 1 2 2 1 1 1 1 1 17 5 X 1 2 1 1 1 1 1 2 1 18 6 1 1 X X 2 X 1 1 1 1 19 7 2 2 1 X 2 1 X 1 X 2 20 8 X X 1 1 1 X 2 X 1 1 21 9 2 X 2 2 2 1 1 1 X 2 22 23 Team Times wrong result for game 1 Times wrong result for game 2 Times wrong result for game 3 Times wrong result for game 4 Times wrong result for game 5 Times wrong result for game 6 Times wrong result for game 7 Times wrong result for game 8 Times wrong result for game 9 Times wrong result for game 10 24 1 0 0 0 0 1 0 1 0 0 0 25 2 0 0 0 0 1 2 3 4 5 0 26 3 1 0 0 0 0 0 0 0 0 0 27 4 0 1 0 1 0 0 0 0 0 0 28 5 1 0 0 1 0 0 0 0 1 0 29 6 0 0 0 0 1 0 0 0 0 0 30 7 0 1 0 1 2 0 0 0 0 0 31 8 0 0 0 0 0 1 0 0 0 0 32 9 0 1 0 1 0 0 0 0 1 2


Please help with formula or VBA solution.


Thanks And Regards,
Moti

Hi

As part of trying to analyse some sports results, I have come up with some match ratings for difference teams and then tied them up with whether the rating ended up as a home or away win. Such as below

8 h
8 h
-1 a
-1 a
-2 a
3 a
9 h

In theory a -ve result should indicate an away team win and a +ve result a home win. I hope to be able to make a chart (which might be apporximately a normal curve) from this data. I want to be able to say from a graph that a certain % of matches with a rating of 8 had home wins for example

Can give me any tips about how to graph this data?

thanks

Ian

Hi,

I have bitten off more than I can chew.

I have created a diary to summarise events relating to a transport fleet - dates along the top, assets listed on the left hand side. The event related to an asset is recorded under the corresponding date column and in the corresponding row for the asset. I'm hoping the image of the diary will show from the thumbnail below:

Spreadsheet 1.jpg



I want to then extract specific events into a log against which I can record the detail about that event. For example, for every instance of "Accident Repair Damage" I want to have a list auto-populate with the asset identifier and the date of the event.

What I'm trying to achieve is a list that looks like this:

Spreadsheet2.jpg

The complexity comes in that under any given date there could be multiple "Accident Repair Damage" events for different assets or none at all and I don't want any gaps in my list.

My theory goes that since this is logical it should be possible I just lack the skill to make it happen. I think what I am trying to achieve is to say: for every instance of "Accident Repair Damage" populate the date of the instance and the asset ID

Please, please can you help??

I have the following formula

=VLOOKUP("Incomplete"&$A2,'Non FG Samples'!$A$1:$AC$1026,1,TRUE)

What I was hoping for is that it would look up the value of "Incomplete" and the number after it (A2 has numbers 1 to 1000) in the table array 'Non FG Samples'!$A$1:$AC$1026, and return the value from column A in that spreadsheet.

The problem here is that it is only returning one value and not the one that has the text "Incomplete1" , "Incomplete2" etc in the row.

What do i need to do to make this formula work.

Thanks in advance

I have written this formula

=IF(MATCH('Imported Data'!A1,'Event Coding'!A:A,0),LOOKUP('Imported Data'!A1,'Event Coding'!A:A,'Event Coding'!B:B),"Could not find result")

And it was working fine until I tried to basically smarten up the xl sheet I'm trying to create, the code is still exactly the same as before but now instead of giving me the correct "Event Coding description" from 'Event Coding'B:B it is throwing up the last result from 'Event Coding'B:B for every result
(I have the formula copied down the column ) - so the last formula at the moment is

=IF(MATCH('Imported Data'!A13,'Event Coding'!A:A,0),LOOKUP('Imported Data'!A13,'Event Coding'!A:A,'Event Coding'!B:B),"Could not find result")

I'm unable to add an attachment for some reason or I would have done to make it a bit more clear what Im trying to explain!

Can someone look at the attached and see if they can help me create the correct formula? I have two date columns, a column of diameters, and I need to predict an event for 2009.

I would like to learn how to enter data on excel faster. I have a large
datasheet, my samples are in "rows" while the tests are in "columns". The
samples are arranged in numerical order but when I'm testing them, I group
them into fixed groups of around 90-100 members. The members of these groups
are randomly arranged. I get my result one group at a time. And I conduct
different tests on each fixed group. I just score the test as normal or
abnormal.
For example, I'm testing test A on goup 1 which is a fixed group of 90
members and the result is normal for all, I have to go through my samples
numbered 001-900's to score each member of group 1 (randomly arranged) as
normal. I don't want to sort my samples according to the fixed groups because
I want to preserve the numerical order of the samples as much as possible. Is
there a way to enter my data faster without rearranging the samples?
Thanks.

I posted here a while back with this same problem with the same statement, but this time the problem is with a constant instead of a variable.

The following Statement runs perfectly normal:

Code:

.CommandText = Array("SELECT event.date, event.time, event.endtime, event.duration," _
        & " event.ccode, event.""desc"", event.staff, event.client, event.eve1_03_01" _
        & " FROM TimeMatters9.tm9user.event event" _
        & " WHERE (event.date >= '" & startdate & "')" _
        & " And (event.date = '" & startdate & "')" _
        & " And (event.date

I have hundreds of rows of patient data and within that data I need to look
at each individual patients data.
For every patient I have several rows of data, each row contains the same
fields but they relate to different events in their hospital stay which are
numbered 1,2,3,etc. I am trying to compare the latest event with the previous event (e.g. event 3 with event 2) and show whether their test results (which is a number) increase or decrease and if for instance it increases and is now over 30, count it as a yes and if it goes down count it as a no. The macro will need to identify the most recent event and then compare this to the previous event, this will be different for each patient record. Can you help?

Could anyone send me some user form samples or send me links they know of?

marklangendoerfer@hotmail.com

I want to display data and step down through the rows.

I also want to have a check box and only display data with data in a specific cell, for instance if M22 has an x show the data though if M21 doesn't don't...

Hi all
I have a MASSIVE spreadsheet I need some help formulating results on. There are a total of 650 rows of information that are all similar.
I have attached a sample as the formating is not corrct when I post

The dots in the report number represent three samples on one report. I need to be able to count the total number of samples taken, how many were on pipe and how many were on foam, how many were NAD and how many were not NAD. I have tried the COUNTIF, SUMIF, COUNT and several other function, all of which return a 0 result.

If anyone could help me you would save me numerous hours of going through each report. Thanks guys and have an awesome day