Free Microsoft Excel 2013 Quick Reference

calculate the p-value for a negative t-value

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:

Mahalo for your help,

Post your answer or comment

comments powered by Disqus
in Excel, how do you calculate the future value of a present value, given
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

Calculate the y-values for the prescribed x-values in the peicewise

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.


Excel only calculates the confidence interval for a population mean through
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?

I'm attempting to calculate the maximum value in a range of cells in 60-80
closed workbooks.

I have this in cell A1
I have this in cell A2

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.


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,



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:

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,



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!

i have tried to use the column function to get the column number for the max
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?!

Ian Smith Wrote:
> 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
> 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
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)
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:
View this thread:

Currently I have written this formula which allows me to take calculate the standard deviation for a rolling 18 week range:

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

So I need to use the last value of a column in excel for an equation. For example, say I have values in cells A1 through A5, and in cell B1 I want to calculate =A5-A1. Then I enter a value in A6 and I want the equation to now calculate =A6-A1. So something like =A(last entered value in the column)-A1.

I want to loop through data points in an x-y chart series and use the X and
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?


I am totally new to VBA and Macros, so it would seem I need an idiots step by step guide as to how and where to place the appropriate code.

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


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!

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.

I'm working with a form that contain text boxes ( Invoice_num auto number, and Invoice_date bound to the table Invoice) , and a subform contains (num (and it's an auto number also) , article, size, qty, price,....)

and here is the SQL of the subform:

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
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".
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

This one is driving me bonkers! My goal is to retrieve the scrollrow and scrollcolumn values for a worksheet just before its workbook is deactivated. I am currently using the following code:

    msg = MsgBox(ActiveWindow.ScrollColumn & "   " & ActiveWindow.ScrollRow) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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...

This is my Data:

month (Multiple Items)

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.


Hi All,

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!

I can't seem to track this down...what is the formula for returning the last value in a column? If I have data in column a1:a????, how can I have excel return the contents of the last cell to contain data??


Dave M.

I am trying to calculate the median value in a column of 51500 values, doing
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.