My question is how can I calculate a p-value for a negative t-value?

In several tests the t-test value is negative and I cannot use the standard

TDIST(x,df,1) function. Is it correct to calculate the p-value of negative

t-values as:

1 - absolute(P[x])?

If the above is correct, is this the correct adaption to the TDIST function:

=IF(x>0,TDIST(x,deg_freedom,tails),(1-(TDIST(ABS(x),deg_freedom,tails))))

--

Mahalo for your help,

Camper

In several tests the t-test value is negative and I cannot use the standard

TDIST(x,df,1) function. Is it correct to calculate the p-value of negative

t-values as:

1 - absolute(P[x])?

If the above is correct, is this the correct adaption to the TDIST function:

=IF(x>0,TDIST(x,deg_freedom,tails),(1-(TDIST(ABS(x),deg_freedom,tails))))

--

Mahalo for your help,

Camper

- Calculating the future value of a present value
- Calculate the y-values for the prescribed x-values in the peicewise
- Excel only calculates the confidence interval for a population .
- Calculating the maximum value in a range of cells in a closed work
- Calculating the remaining shifts for a week?
- How can I calculate the Moving-Range without returning negatives
- Finding the best match for a particular answer
- How to attain the MAX value from a Sumproduct formula
- Chi-squared P-value Algorithm in Excel
- Finding the maximum value in a range
- Cell column number for the max value of a range of numbers
- Calculating p-value from Fisher's Exact Test
- Excluding the 2 lowest and 2 highest values from a rolling STDEV
- I want to use the last value in a column for a formula
- Is there a property to get the values for a point in a chart series?
- Set the Maximum value for a Form Scrollbar
- (SOLVED) Help with the IF function to calculate my ordinary hours for a timesheet
- Identify the area code for a telephone number
- You tried to assign the Null value to a variable that is not a variant data type
- Getting The Scroll Row For A Sheet Just Before Workbook Is Deactivated
- Calculate the growth rate for each year
- Finding the Lowest Value of a Named Range
- Return the last value in a column
- Calculating Median Value in a large array

that you know the interest rate for that period of time. for example, $1

today will be worth how much in 10 years given a 6% interest rate compounded

annually?

Use a sing excel formula to evaluate the first y-value for its coressponding first x-value. The x-values start from -4 and change incremently with 0.2 through to 4. Th peicewise is described as follows: If x is between -4 and -3, then function is x+4, otherwise if x is between -3 and -2, the function is -x-2, otherwise if x is found between -2 and 2, the function to use is square root of 4-x^2, otherwise if x is found between 2 and 3, the function is x-2, otherwise the function to use is -x+4.

Confused..

the =CONFIDENCE() function. I want the flexibility to calculate the

confidence interval for a sample mean as well. Can Microsoft develop a

CONFIDENCE function for sample data and not just population data?

closed workbooks.

I have this in cell A1

'Y:Folder1Folder2[doc.xls]Title'!$A$1:$A$20

I have this in cell A2

=MAX(INDIRECT.EXT(A1))

What am I missing?

Thanks in advance,

Barb Reinhardt

I have a need for a formula that will calculate the remaining shifts for a week, based on the current time, and output it as a decimal. I have a shift that begins on Sunday night at 10 PM, and runs until Friday night at 10 PM. 15 total shifts in 8 hour increments. I'd like a formula that will give me a nice one decimal figure for the shifts remaining. Would one of you kind forum members give me some direction with this?

Thank you

I need to calculate the Moving Range for a population of data without

returning any negative values.

returning any negative values.

I am trying to get excel to calculate emprical formulas for me based on accurate mass.

Basically an empirical formula will be of the form CxHyOz etc. where x, y and z are whole numbers. I have accurate values for C, H, O etc.

What I would like to do is be able to enter a number into a cell and have excel give me the best values for x, y, z etc. and tell me how different the number I entered is compared to the calculated value.

One important thing is I need to be able to force the calculation to use certain values for X, Y or Z and be able to change these values.

For example, to set z as 8 and then excel calculates the best values for x and y or set x as 2 y as 3 and then excel calculates the best value for z etc.

X, y and z is a starting point but this will probably need to be increased to 9 or 10, based on the empirical formula.

Is this possible to do in excel?

Many thanks,

DT

I'm writing this in reference to my original problem - which was partly solved. The orginal problem (alongwith a sample data) can be found at:

http://www.excelforum.com/excel-prog...vot-table.html

So the question is how can I display the Maximum value for a therapist in a particular week with the sumproduct formula?

The sumproduct formula I got:

K2: =SUMPRODUCT(--($B$2:B2=B2),--($G$2:G2=G2),--($H$2:H2=H2),($D$2:D2))

From this I want to get the MAX value and use it to calculate the commission, by the formula:

M2: =IF(K2>L2,(K2-L2)*0.1,0)

So do i use the max function? Or is there another way around the problem?

I am working with Chi-squared at the moment and wondering how Excel calculates the P-value for Chi-squared? E.g. the method Excel uses to calculate it?

Many thanks,

Duy

I need to find out the maximum value of a variable range of data and then lookup the corresponding person for the maximum value. I'm having difficulties writing the code.

My table will look something like:

Bob Sally Sarah Maximum Winner

1 2 3 3 Sarah

3 5 5 5 Draw

My code so far is:

Private Sub Winner()

Dim MaxCount As Integer

Dim MyCells As Range

FinalRow = Cells(65536, "D").End(xlUp).Row

For i = 2 To Cells(FinalRow, "H")

MyCells = Range("i, D"), (i, "F")

I’ve tried something like: Max(Range("i, D"), (i, "F") but it doesn’t work.

Obviously I want to express it for each row of data so that when I add a new line of data the maximum and winner fields are calculated.

Note: I want to write this in VBA (not using formula).

Thanks for your help!

value of a range of numbers but it didn't work. i know that a solution for

this exist but i have tried different ways and in vain. any ideas?!

> Me on Microsoft > wrote in

> message >...

> > Say I have a 2x2 table like:

> >

> > 1 5

> > 30 20

> >

> > and I want to determine whether the rows are significantly different.

> I'd

> > like to use Fisher's Exact Test, and because I'm not a statistician

> I'd like

> > to cut to the chase and just calculate the p-value associated with

> the

> > two-sided test.

> >

> > Can I do this in Excel? If so, how?

> >

> > Thanks!

>

> You're going to need a function to calculate the tail probabilities of

> the hypergeometric distribution. VBA code for these calculations can

> be found in http://members.aol.com/iandjmsmith/Examples.xls.

>

> You'll then need to add the VBA function

>

> Public Function Fishers_exact_test(a As Double, b As Double, c As

> Double, d As Double) As Double

> Dim det As Double, temp As Double, sample_size As Double, pop As

> Double

> det = a * d - b * c

> If det > 0 Then

> temp = a

> a = b

> b = temp

> temp = c

> c = d

> d = temp

> det = -det

> End If

> sample_size = a + b

> temp = a + c

> pop = sample_size + c + d

> Fishers_exact_test = cdf_hypergeometric(a, sample_size, temp, pop) +

> comp_cdf_hypergeometric(a - 2 * det / pop, sample_size, temp, pop)

> End Function

>

> Then the p-value for the two sided test for the 2x2 table

>

> a b

> c d

>

> is given by Fishers_exact_test(a,b,c,d)

>

> Ian Smith

The VBA function should be

Public Function Fishers_exact_test(a As Double, b As Double, c As

Double, d As Double) As Double

Dim det As Double, temp As Double, sample_size As Double, pop As

Double

det = a * d - b * c

If det > 0 Then

temp = a

a = b

b = temp

temp = c

c = d

d = temp

det = -det

End If

sample_size = a + b

temp = a + c

pop = sample_size + c + d

det = (2 * det + 1) / pop

If det < -1# Then

Fishers_exact_test = cdf_hypergeometric(a, sample_size, temp, pop) +

comp_cdf_hypergeometric(a - det, sample_size, temp, pop)

Else

Fishers_exact_test = 1#

End If

End Function

Thanks to Einar Andreas Rødland for spotting the error!

Ian Smith

--

Ian Smith

------------------------------------------------------------------------

Ian Smith's Profile: http://www.excelforum.com/member.php...o&userid=27612

View this thread: http://www.excelforum.com/showthread...hreadid=268855

=STDEV(OFFSET(INDEX('[2011 NA Weekly.xlsx]Sheet1'!$B$5:$BA$5,,COUNT('[2011 NA Weekly.xlsx]Sheet1'!$B$5:$BA$5)),,-17,1,18))

However, I can't figure out how to exclude the two highest and two lowest values from this range. Any help is appreciated. Thanks!

Y values to set the Left and Top positions of some arrows I have placed on

the chart. I've got the regression formulas for converting the coordinates

of each data point into Left and Top values. I just can't figure out how to

access the X-value and Y-value of each point during the loop so that I can

plug them into my conversion formulas. Is there no property to get the

actual values of a point in a chart series?

Bruce

I have a dynamic table where the number of rows change based on a user selection. I have set up a Form Scrollbar and I want to be able to change the 'Maximum Value' setting for the scrollbar using VBA.

I get the number of rows in the table using a 'MATCH' function in cell A2 to give me an Integer value.

I have looked at a number of solutions, such as:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then If IsNumeric(Target.Value) Then ActiveSheet.Shapes("Scroll bar 1").ControlFormat.Max = Target.Value End If End If End Sub

and

Private Sub ScrollBar1_Change() ScrollBar1.Min = Range("A1").Value ScrollBar1.Max = Range("A2").Value End Sub

and many variations along similar lines, however everything I try generates an error.

The errors are either saying I need to declare a variable, but I don't know what I should be declaring, do I need to declare the scrollbar itself as a variable or the Range? or that

'it Cannot run the Macro because it may not be available in this workbook or all macros may be disabled' which they aren't because I have other macros running.

QuestionsShould I be right clicking on the scrollbar and using the Assign Macro to add any code or should I be right clicking the sheet tab and using the 'View Code' option?

Could anyone please show me the code I need to add and where and how I need to add it in full, including any variables I may need to declare.

I'm sure it is simple when you know how, but when you get an error and you don't understand it, it is hard to fix...

Many thanks

Im am having trouble with the IF function in excel 2010, for a template of a pay sheet.

I have been asked to -Using an IF statement in cell G8, calculate the ordinary hours component of total hours.

Using a nested IF statement in H8, calculate the overtime hours.

Using a nested IF statement in I8, calculate the time and a half (T ½) overtime hours

Using a nested IF statement in J8, calculate the double time hours.

The ordinary hours are 8hrs a day.

On weekdays, the first 2 overtime hours are paid at time and a half and remaining overtime hours are paid at double time rates.

Hours worked on Saturday are considered overtime, with the first 2 hours worked at time and a half rates and remaining overtime hours are paid at double time rates.

As you can see in my file i have just put the normal hours which is 7.30am - 4.00pm,with a 30 min lunch break, so i am not sure how to work out the IF functions.

I will be putting the employees actual hrs in my next spreadsheet, this will be the template i will use to calculate the info.

Can anyone please help me with this function,

I would greatly appreciate it!

natasha9794

I have attached my file!

I'm trying to find a way, using VBA, of identifying the area code for a telephone number.

I have a list of UK area codes, and a list of phone numbers (attached). If the telephone number(column a) starts with the area code (column d) I want to place the location (column e) next to the phone number (column b). The trouble I'm having is that area codes can be between 2 and 6 characters long, so I can't just set up sub to look for say, the first 5 digits of the telephone number and find the matching value in the area code list.

Can anyone give me some starters? Thanks very much.

and here is the SQL of the subform:

VB:But everytime that i try to add the article in the subform i got this error You tried to assign the Null value to a variable that is not a variant data type".SELECT purchase.purchase_num, purchase.Invoice_num, purchase.article, purchase.size, purchase.quantity, item.Price, item.Price_after_discount, purchase.unit_price, purchase.Difference, item.st_q1, item.st_q2, item.st_q3, item.st_q4, item.st_q5, item.st_q6, item.st_q7, item.st_q8, item.st_q9, item.st_q10, [item solderie].st_q1, [item solderie].st_q2, [item solderie].st_q3, [item solderie].st_q4, [item solderie].st_q5, [item solderie].st_q6, [item solderie].st_q7, [item solderie].st_q8, [item solderie].st_q9, [item solderie].st_q10, item.Stock, [item solderie].Stock, Invoice.total_cash, Invoice.total_diff, Invoice.total_discount FROM item INNER JOIN ([item solderie] INNER JOIN (Invoice INNER JOIN purchase ON Invoice.Invoice_num= purchase.Invoice_num) ON [item solderie].Article = purchase.Article) ON item.Article = purchase.Article;If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

After that i press ok i can continue with the form , i insert the size and everything work fine afer that, till i want to fill the quantity , it gives me that error:

The Microsoft Office Access database engine cannot find a record in the table 'Invoice' with key matching 'Invoice_num'

but i was working on that form before , and it was working , but i needed to do some updates , and these errors pop up , but i tried to add some data on an Invoice Number that i had created before i did the update everything works fine ! and i was surprised and i don't know why i can't create a new Invoice number and how can i fix this?

Hope some one help me . thanks guys for reading this

VB:Unfortunately, this isn't working the way that I intended. If, for example, I deactivate the workbook containing this code by switching to another open workbook, the msgbox displays the scroll row and column from the active sheet in the new workbook that is opened. How can I "catch" the scroll settings for the workbook containing the above code before it is deactivated? Thank you for your help...Workbook_Deactivate() msg = MsgBox(ActiveWindow.ScrollColumn & " " & ActiveWindow.ScrollRow) End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

month (Multiple Items)

Values

Row Sum of Net value The Growth Rate

2007 14,378,194

2008 2,762,771 #DIV/0!

2009 3,595,793 #DIV/0!

2010 624,715 #DIV/0!

Grand Total 21361473.11

I am trying to calculate the growth rate for each year by doing the following:

Value field settings ----> summarized by (sum) ----> show value as ----> % difference from (Base field:year,Base Itemrevious)

But I keep getting the error shown in the table above DIV/0

I guess because the fist year GR equals zero.

I don't how to overcome this problem.

And I don't know if this is the right way to calculate the Growth Rate to start with.

Thanks.

What I want to do is find the smallest value of a named range that is not 0. Using {MIN(NamedRange)} works fine, but wil always return 0 if there is one in the range. Is there any way of ignoring 0s?

I thought of using a combination of SMALL and COUNTIF, but COUNTIF doesn't seem to work on the named range using {}s...

Thanks in advance for any help!

Thanks,

Dave M.

so once for 83 separate columns. The formula =MEDIAN(array point 1:array

point 51500) works fine for most of the columns, but for others the system

returns a blank cell. There are data points in those arrays, so the blank

cell is confusing. Is there a way to instruct the system to look only at the

values with a whole number in the cell and to calculate the median from that

portion of the array, without sorting the column or modifying it?

Another approach would be to ask the system to return the MIN value. The

problem there lies in the fact that the system always presents zero as the

minimum, because is is. However to do the median calculation above I need

the smallest value above 0 in the array, is there a way to instruct the

system to present the smallest value above 0 in an array.

No luck finding an answer? You could always try Google.