Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

How To Calc How Far A 50% Event Is Off

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi wasn't sure if this would get an answer here, but it can't hurt to try.
I use lots of sports stats to calculate and predict results. Well all goes well but when I look for new things to predict, I am not really sure that I use the data my stats return in the best way. For instance lets say I run a particular theory over which I get 5000 results of a 50% event, and my data shows me a return of 54%. Making my theory correct to predict an event by 4%. I am sure at 100 samples my theory would be inconclusive. Due to the lack of data.
So my question is, in an event that is 50/50 how many samples would I need to make my theory somewhat conclusive. Like for example at 15000 samples at 54% where I need a 52% result to be conclusive. Is there a formula that would tell me if this would be enough samples to where there would be less than a 2% error. Thanks in advance Andy

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Return the Max Number from a Range That is Within a Minimum and Maximum Target Value in Excel - UDF
- Return the highest number between two numbers that is in a range of cells with this UDF (user defined function) in Excel
Filter Data in Excel to Display Results that Contain 1 of 2 Possible Values - AutoFilter
- This Excel macro filters data in Excel to display results that contain 1 of 2 possible values. This macro uses the xlor

Similar Topics

Hello there,

Thank you in advance for any help on my problem.

I have 2 rows.

In Row A I have a criteria such as "Practical" and "Theory".
In Row B I have the quantity of time to be spent on each for exampl
Practical 4
Practical 3
Practical 3
Theory 2
Practical 8
Theory 2

I am trying to write equation that will summ the toal amount of time as noted in ROW B only if it of value "Theory"

For example the answer to this sample would yeild:
Theory = 4
Practical = 18

Thank you again for any help yo are able to provide.

In peace,

p.s. sorry for what appears a duplicate post i was unable to find a soution that worked when fishing in the archives.

p.s.s. I am not very famaliar with pivot tables so for now I prefer to just use logic/functions.

I am trying to create a form that peforms 95% UCL statistical analysis for a varying number of sample points. I am trying to create a spreadsheet that will put the proper Student T in the formula. I have already created a form that works but only if you select the correct form for the number of samples that you collected.

For example:
If 6 samples are taken then the formula is =(A35+2*(A44/SQRT(6)))
If 3 samples are taken then the formula is =(A35+2.9*(A39/SQRT(3)))

If i make A1 a cell that gives me the number of samples (between 2 and 9) how can i get the formulas above to insert the correct Student T?

The Student T Chart for the number of samples is as Follows:
7thru 9=1.9

I am using Excel 2000 if that matters.

I hope someone can help me with this problem.

I collect samples for each unique HOLEID. Samples can be from 10 to 300 samples. Over 3 months, I collect samples from up to 100 holes.

Every 3 months I need to collect 450 samples from each group of holes and send them for testing. each samples is graded based on Low (0 to

I know this title isn't exactly very descriptive but, it was the best I could come up with.

I would like to calculate a percentage for the whole based on the result of its parts. Each sample I have has a varying number of sub samples. Now i could do this all by hand but, with around a thousand samples that would be just slightly tedious. Below is a piece of sample data to look at.

Sample Data

In that case I want to know the percentage of positive results for sample 0701950 and also 0701951. Positive results are anything with a result of "P", "R", "S", or not having an "-". I figured a SumProduct forumla would do it but becuase of the sub sample numbering that proved a mightly challenge for me. I tried to use the "*" wildcard but excel doesn't seem to recognize it or I used it incorrectly. I'd like to be able to just click/drag the formula so it auto fills for all my samples without having to adjust it for each new set as well. I'm hoping one of you here are much more cleaver then I when it comes to this .

Thanks in advance for any help you can offer.

Hi Guys

A technical question really. i am looking to make an excel chart, showing the scatter plots of data.

The data are predictions of an event happening against how often they actually happened.

Lets say I predict something will happen 60% of the time and I do this 100 times. So the average prediction is of course 60%. The actual event happened 59% of the time pretty good predictions. As well as this I predict something happening 30% of the time, but only once. and the vent has happened one time out of 1 guess, so therefore I am 100% correct. If I do a scatter chart for prediction and actual events taking place, then the 30% prediction has as much effect on the data as the 100 predictions of 60%.

I could of course leave the one piece of data out, but is there anyway i can weight it.

so that the 100 predictions are more relevant.

many thanks


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??

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.


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 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?


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!

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!


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:


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:

Thanks in advance!

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,

This is what I am trying to do:
1 Sample, 0 Errors = A
1 Sample, 1 Error = D
2 Samples, 0 Errors = A
2 Samples, 1 Error = C
2 Samples, 2 Errors = D
3 Samples, 0 Errors = A
3 Samples, 1 Error = B
3 Samples, 2 Errors = C
3 Samples, 3 Errors = D
4-10 = 0 errors = A, 1 Error = B, 2 Errors = C, 3 or more Errors = D

The formula i currently have just marks as N/A if less than 3.


Please i need an advise ASAP i have been using this statement and it cant help


and it just works for the first 2 values c1, c2 and doesn't fit for the others.
the case is i have more than one event at the same video and i need to confirm that no event was taken unless it is between start and end.

here are some samples:

Start dtime End Dtime Event Dtime
16/09/2013 22:13:34 16/09/2013 22:14:18 16/09/2013 22:13:38
16/09/2013 22:15:57 16/09/2013 22:24:30 16/09/2013 22:16:02
16/09/2013 22:24:30 16/09/2013 22:33:49 16/09/2013 22:17:32
16/09/2013 22:33:53 16/09/2013 22:35:05 16/09/2013 22:19:02
16/09/2013 22:35:05 16/09/2013 22:39:57 16/09/2013 22:20:02

So as you can see there are more than one event between one start and end dtimes

thanks guys


Please i need an advise ASAP i have been using this statement and it cant help


and it just works for the first 2 values c1, c2 and doesn't fit for the others.
the case is i have more than one event at the same video and i need to confirm that no event was taken unless it is between start and end.

here are some samples:

Start dtime End Dtime Event Dtime
16/09/2013 22:13:34 16/09/2013 22:14:18 16/09/2013 22:13:38
16/09/2013 22:15:57 16/09/2013 22:24:30 16/09/2013 22:16:02
16/09/2013 22:24:30 16/09/2013 22:33:49 16/09/2013 22:17:32
16/09/2013 22:33:53 16/09/2013 22:35:05 16/09/2013 22:19:02
16/09/2013 22:35:05 16/09/2013 22:39:57 16/09/2013 22:20:02

So as you can see there are more than one event between one start and end dtimes

thanks guys


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:


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??


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?



any macro samples will be great, thank you!! oh samples to be used with Excel 2010 please

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 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!

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 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:


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