Performing hypothesis testing on data comprising of hours devoted to patient care per week for practitioners in conventional and alternative medical practices. I have used data analysis -> z-test-two sample for means to perform my calculations.
I would like to know if this is the correct way to perform the hypothesis calculation. See attachment for more details.
I am running excel 2010. I have set up a variable " testvalue" type variant
I would like the prog to step through a row of data which can contain text or numbers. If any number is greater than 3,000,000 I want exit the do. However, if no number is greater than 3,000,000 I want to record this as a "bad file". I have the follow code which trips at the first "case".
Code:
testcol = 1
testrow = myTextRows - 1 'penultimate row
Do While Len(wbText.Sheets(1).Cells(testrow, testcol)) > 0
testvalue = wbText.Sheets(1).Cells(testrow, testcol).Value
Select Case testvalue
[Code] ........
I've currently written the following code to make sure exactly 2 single cell selections are made. But now, I want to extend this code to make sure only cells from rows 5 & 7 are selected and the cells are in the same column.
View 2 Replies View RelatedI have a form that information is entered in. At the end the user can then save the information in a spreadsheet by pressing a command button. The updating to the spreadsheet works, it is the part where the last entry is tested against the info on the form to prevent a duplicate entry that do not work.
I have tested that the program reads the info from the spreadsheet (see the commented lines).
I can attach the whole spreadsheet, but some of the entries on the form are linked to another spreadsheet that make it a bit cumbersome to attach two files.
VB:
Private Sub cmdUpdate_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("TonnageInput")
[Code] .....
I am trying to write an Excel formula to test if there is data in pairs of cells and if there is I want to copy the entire record to another Excel spreadsheet. Example: if there is data in row 1, column A and data in row 1, Column C or row 1 column D, then copy this record to another spreadsheet; if there is data in row 1 column B and data in column C or column, then copy this record to another spreadsheet. Since there is no data in row 4, column C or column D, this row would not be copied. Row 1, row 2, row 3, and row 7 would be copied to another spreadsheet. The data can be numeric or alpha numeric.
I tried using the ISBLANK statement and can't seem to get this to work properly.
This code is functional but very redundant and I figured that has to be an easier way to go about this.
View 5 Replies View RelatedI have a program that relies on a remote drive being open and ready. My current tactic (which works perfectly well) is to have a file in the destination drive and ask the date that file was created. If an error occurs I know the destination drive is not ready.
Code:
On Error Resume Next
temp = FileDateTime(destDrive & "xDeletedStuff" & "testFile.txt")
If Err Then
MsgBox "Destination Drive / Folder Not Ready"
Exit Sub
End If
On Error GoTo 0
Idealy I would rather not use the "testFile" I would prefer to just test to see if the folder existed, "destDrive & "xDeletedStuff"
I working on a loop that would find blank cell. These cells have a formula in excel, but if certain conditions are met, the cell is left blank. What property in VBA checks for blank cell? I don't thin it's the "empty" property?
View 9 Replies View RelatedI have to test if todays day is Saturday. Don't know the syntax.
View 3 Replies View RelatedTesting For First Letter Of Entry. within an if statement, i'm testing for the first letter of another cell with the function: left(e273,1)= "D". this works well, but i only have a few letters to test for and i'd like to test for not "D", etc. (i.,e, what if the first letter is not D, then do...)...?
View 2 Replies View Relatedscenario testing and coming up with a simple "multiply by" that I can drag across a table.
View 5 Replies View RelatedI have the following codes to test Caps Lock whether it is on or off. Instead of showing a msgbox when the Caps Lock is on, I there another way to show comment or other object as well as windows UserForms
View 3 Replies View RelatedI have simple data points based on a question that how many people liked product A between 2011 and 2012.
It looks like below
2011 2012
# of Respondents 319 329
% of respondents liking the product 78% 84.5%
The question is: is the increase from 78% to 84.5% statistically significant at either 95% or 99% confidence level. Is it possible to do this basic analysis using Excel?
Basically, looking at the range E6:L19 I need to ensure that any data that is entered into any of these cells ends in either H or W, and if it doesn't flags up a warning message (which I am hoping to link in with the Worksheet_SelectionChange event)
I'm managing it for one cell, but not a whole range.
In a table I engaged the Subtotal (Sum) feature.
Then I did the Data, Subtotals.. Remove All
Jumping to the immediate window to enter
activesheet.select
? ActiveWindow.DisplayOutline
True
Excel 2007 on XP, must be compatable with '03
I have a macro that sorts sets of data that is imported from a LIMS database (SQL). The macro then sorts the data based on a characteristic of the dataset that was pulled in.
I did have the macro working, however now I must add in some AI that groups together certain datasets that don't share the same parameter, as well as discard incomplete datasets, and group together unknown datasets.
In the code following the trouble section, I isolate the required tag, placing it in labelDecoded(2). Now I must test this variable against a couple of cases to see if it is one of the exceptions to the normal rule (which is to Place it in the worksheet named after what is in labelDecoded(2). A switch statement is nessasary as this is still in a prototype/proof of concept phase, and the sorting AI will be expanded in the finished product (As in more cases will be added).
Select Case labelDecoded(2)
Case Mid(labelDecoded(2), 1, 1) = "G"
getSheetName = Gas
Case "DPLS" Or Mid(labelDecoded(2), 1, 2) = "UD"
getSheetName = "Other"
Case Else
getSheetName = labelDecoded(2)
End Select
I have highlighted the sections that is giving me an error in red. The first section "Mid(labelDecoded(2), 1, 1) = "G"" Fails to evaluate to true. The Second section "Mid(labelDecoded(2), 1, 2) = "UD"" Throws a type mismatch error, that causes the macro to execute some error handling code, which by co-incidence, is the same as that case evaluating to true.
When a switch statement is called is there an object or variable that holds the information that is being processed by the switch statement? I recognize that my problem is most likely that I am calling the variable that I have allready tested to be tested again.
I want to solve the problem of giving an alert for opening a new reagent vial for blood sugar testing
View 4 Replies View RelatedI'm trying to identify where a set of values 'agree' within a specified tolerance.
I have a list of scores from different sources, and wish to both average them (easy to do) but also to identify how well they 'agree' by flagging those which have a difference above a specified tolerance. The numbers are all integers, and the tolerance will be an integer.
To give an example, I might have cells with values (2,3,2,2) and these agree within a tolerance of 1. If I had cells of (2,4,2,2) these wouldn't agree. I'd like to flag (by the creation of an 'X' within a separate column) where these cells do not agree.
I've done this previously by using chained If and Or statements (e.g. if A1 = D1 or A1 = D1+1 etc etc) but this is laborious and means I have to change logic every time I add a new sample.
Is there a way of specifying a range of cells (e.g. A1,D1,H1, J1) and seeing if the values of these cells agree to within a stated tolerance? I'm happy to define named cells to specify and argument/attribute.
I have set up a linear regression array in Excel and now want to test the significance of my r2 value at a certain level of significance. I've only been able to find tables that give the critical r value, but I want to test it at 99.73% level of confidence and none of them contain that specific value. Is there a way to do this in Excel?
View 3 Replies View RelatedI have an excel template that is used for communicating in projects to teams their responsibilities and defining which stream/group has Accountability/Responsibility/Consulted/Informed (known as a RACI model) for the activity.
For any given project activity, there are rules:
One person is responsible for performing the activity (R)
Only one person can be accountable (A)
Many people can be consulted through the activity (C)
Many people can be informed of progress (I)
You must have an A and and an R for each activity
And one person can be accountable AND responsible A/R
Given that project activities can run to 100 + activities in some of our more complex programmes.... I'm looking to provide a flag in the spreadsheet for when an error has occurred and a gap in Responsibility or Accountability is created. (Less worried about testing for C's and I's)
See attached, I hope it is clear.
I'm the final stages of testing a userform that, in response to a button click, copies certain cells from a big messy worksheet and pastes the relevant ones (based on user input) in a clean sheet. Suddenly, I started getting a 'divide by zero' error for the following line:
VB : UpCount = PickNum - 6 + ((PickNum / 12))
UpCount and PickNum are both declared as Double, though this shouldn't matter. UpCount is being assigned a value here for the first time, and PickNum varies from 1 to about 250 depending on input.
Obviously I'm only dividing by a constant here, which is VISIBLY not zero. This error only occurs for certain ranges of PickNum...something like 50-70. Interestingly, in trying to debug it, I added:
VB:
Msgbox(PickNum)
Msgbox(54/12)
...since PickNum was 54 as I was getting this error. Just dividing 54 by 12 ALSO got a div by zero error.
Perhaps I should mention I'm using VBA in Excel 2010 for Mac.
I have two columns, some of the cells in these columns contain more than one value separated by a space. Example:
Col A Col B
1.99 1.69
39.95 6 119.94 29.99 6 149.94
135 250 135.00 250.00
11.6 11.60
What I am trying to achieve is to test that the values in column A match those in column B and have a TRUE/FALSE response in column C. At present i'm just using =a=b, however I want the formula to give me a TRUE response for the third and fourth lines in my example, as I want it to ignore the zero after the decimal place, at present it gives me FALSE.
So the results I want to see in col C for the above example are:
FALSE
FALSE
TRUE
TRUE
I need to format a spread sheet to return a value from a range of 4 values (A,B,C,D) by testing a data range against a table that establishes the true value for each separate possible value within the data range.
In other words, ItemXXX will have a value that is contained within a table on a separate worksheet within the book. I need to test the value of ItemXXX against the range of values on Sheet B and return the corresponding value from sheet B to the main spread sheet.
=If($Bxxx=SheetB!(D18:D35);A;IF(Bxxx=SheetB!(D36:44;B;If($Bxxx=SheetB!(D(45:D56);C;(IF(Bxxx=SheetB!(D57:D65);D;"RFI"))))
But I find nothing in the help pages that tells me it is possible to test a range of cells agains a single criteria in an IF statement.
Look at the tab "testing-allproduct" cell C2. If that value exists in the tab name "store-allproduct", cell c2, then take the value from "store-allproduct" cell a2, and put that value into "testing-allproduct cell" A2.
View 2 Replies View RelatedI have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
View 4 Replies View RelatedI have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem
...
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
Else
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
End If
...
End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty
' or
.Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
I am trying to create a user form that will allow the user to type int values in boxes. Then when the user clicks the submit button the code needs to add the values from each user input box to the existing values in specific cells accross multiple worksheets. Then the form needs to be cleared after the cell values are updated. I can create the form it is the code on the submit button i am lost on. Also it is important that some boxes may be left blank.
If you click the Grey "Qty Form" button on sheet1 the form will open. User data numbers can be entered in the blank boxes. When the submit button is pressed the form needs to add the user entered numbers to the numbers in the corresponding cells in sheet 1 and sheet 2. How to code the submit button to do this properly. Also after the data on the spreadsheet is updated the form needs to be cleared and start the cursor back in the Item 1 box on the form.
- I have total of 13 sheets in a workbook - 12 sheets represent 12 months with data; 13th sheet is single sheet in which i would like to get complete overview of 12 months
- each of 12 sheets has actually sales results for multiple products with following data: internal code, manufacturer part number, name, and qty sold in that month
- situation is that some products have been phased out during the year and some were introduced so each sheet is slightly different in terms of in which row certain product is located
What i would like to achive is to make 13th sheet (whole year overview) do the following:
- there is a list of all the products in it, each product has unique internal code - this code (from each line) should be used to find that code in each monthly sheet, then find its monthly sales value (copy it) and paste it in sheet 13 in cell that represents this product and particular month.
In other words i would like to see for each product what was monthly sales throughout this year, but avoid manually filling in qty for each product per month.
I have attached a sheet that has part of a data list, sheet called (Full Data) what i am trying to do is seperate the data into event locations into individual sheets.
The data ref will be column F which is the different event locations.
I thought the best way to try and do this was to create an if/ match formula using the sheet tab names as the if match, with the event locations in column F.
I have added two sheet tabs so you can see that i require the data for (Ain) to be put into the Ain sheet.
The data list in Full Data sheet will be continuously updated so i will need the range to be around 10,000 entries.
I am attempting to create a macro to generate emails based on data in a sheet. The goal is to run the Macro, and have it generate emails to send to contractors letting them know what they are going to be paid. For instance:
Name in Column J
Email in Column L
Memo in Column N
Balance in Column T
Due Date in Column P
Week Ending Date in Column H
Now what I would like to happen, is to tie a macro into a button that will create the email as follows:
To Field: Email address from Column L
Subject: "Company Payment Remittance Payment Date *Date from Column P*"
Body: Hello *Name from Column J*,
For *WE Date in Column H* you will be paid *Balance from Column T* for the time worked of *Memo in Column N*
Now the tricky part is that I want the email to contain all line items for each email address. So instead of sending one email per line, have the macro automatically put all of the information that needs to be sent to one email address into the message. I don't know if that is possible, but it sure would make my life easier if it was.
I have attached a sample workbook of the data that will be used
Example Workbook for Email Macro.xlsx