Questions tagged [excel]

Microsoft Excel is a commercial spreadsheet program. Use this tag for any on-topic question that (a) involves Excel either as a critical part of the question or expected answer, & (b) is not just about how to use Excel.

Filter by
Sorted by
Tagged with
0
votes
0answers
19 views

How does NORM.INV function in Excel work? [closed]

The Excel function =NORM.INV(A,B,C) where A=.6827, B=0 and C=10 evaluates to 4.75. I thought I understood the NORM.INV function to give the value that has a probability of A on a normal distribution ...
1
vote
1answer
16 views

Extrapolating survey results for a larger data set

I have a set of survey results in Excel for a sample of "customers" which asks for their income. This is chosen from defined ranges such as "Less than 10k", "10k to 15k", "16 to 20k", etc. The sample ...
0
votes
1answer
16 views

How to count number of categories of an item using a pivot table in a spreadsheet? [closed]

The title may seem confusing because I did not know how to word it differently. I will best show what I am about by an example. Let us say we have a spreadsheet, and the following data in it: ...
1
vote
1answer
54 views

Manual computation for Fishers exact test for tables larger than 2 x 2 [duplicate]

I am wondering how to perform a Fishers exact test on my data which contains two columns and three rows. One of the cells contains zero. ...
0
votes
0answers
13 views

Excel ANOVA terminology: with/without replication

Excel's Analysis Toolpak enables two types of ANOVAs, referred to as with and without replication. However, it is not clear to me whether the replication is understood to be done by different ...
0
votes
0answers
15 views

Is this plot of residuals and fitted values homoskedastic?

If not, why not? The first plot shows residuals against fitted values, the second plot shows standardized residuals against fitted values. Overall my aim is to determine if a multiple linear ...
1
vote
1answer
47 views

Why does R t.test()'s confidence interval shift slightly w.r.t. alternative hypothesis?

So I've noticed something extremely confusing about t.test, which is that the confidence interval it provides w.r.t a two-sided alt hypothesis seems to be inconsistent with what it provides for a one-...
1
vote
1answer
32 views

How do you calculate or predict the probability of a hotel booking cancellation (Excel)? [closed]

based on past hotel booking transactions, what will be a good and simple way to calculate and predict the probability of cancellation of a hotel booking transaction using excel? I was thinking of ...
1
vote
0answers
18 views

Land Method for Lognormal Distribution

The Land Method is explain here. Anyone knows how I can look up the H*1-α* It says "Tables of these values are given by Gilbert (1987, Tables A-10 and A-12) and Land (1975). " Gilbert (1987) is not ...
0
votes
1answer
20 views

How do i analyze my data statistically [closed]

I need help regarding hypothesis testing. Not a statistics major but familiar with basic concepts (basic descriptive statistics, frequency, measures of: central tendency, location, dispersion) so ...
0
votes
1answer
51 views

ChiSquare is zero even though the values are not identical

I am running a Chisquare test using CHIDIST function in Excel. My observed and expected values are not identical however i get result 0 (zero). There are 75 observed values so I am using 74 freedom ...
0
votes
0answers
15 views

How to calculate covariance between stocks with different observation periods?

Let's say I have three stocks. Stock A and B I have daily prices from 1 Jan 2000 to 31 Dec 2018. Stock C I only have daily prices from 1 Jan 2005 to 31 Dec 2018. Is it possible to calculate the ...
0
votes
0answers
65 views

how to run a two way ANOVA in excel on summary data: counts of consumers

I have a table of counts of consumers, and I would like to run a 2-way ANOVA on them. There are two factors, year and age-group. Please see the following table of data. Although I usually have ...
19
votes
3answers
4k views

What is the name of this plot that has rows with two connected dots?

I've been reading EIA report and this plot captured my attention. I now want to be able to create the same type of plot. It shows the energy productivity evolution between two years (1990-2015) and ...
0
votes
1answer
19 views

Two formulas giving slightly different output

I am struggling to find a proper explanation why the two methods that I use for finding the difference in EXCEL are giving me two slightly different numbers. The idea behind the task is that we have ...
0
votes
0answers
12 views

What data to include in Weibull fit

I have a set of inspection data that I want to fit to a weibull distribution to understand whether further inspection would provide much value. For example, I have 2000 items of which we have ...
0
votes
0answers
9 views

CRSP/Compustat - Issue with gaps in data

I would like to test empirically CAPM for my dissertation. I have downloaded monthly data for period 01-1990 - 12-2018 for NYSE from CRSP/Compustat database using condition "exchangecode" = 1 however, ...
1
vote
1answer
43 views

How would you graph fiscal year data together with calendar year data?

I have two datasets resembling the toy example below: ...
0
votes
0answers
37 views

How to replicate the predict function from R in Excel given I have access to “summary” output from R

I have run a 3rd order polynomial regression in R and have run the "summary" function, but I need to be able to replicate the "predict" function in Excel. I have my current working code below. Thank ...
0
votes
0answers
23 views

How can I obtain hourly readings from 24 hour moving average data? [duplicate]

I have an excel dataset of 24-hour moving averages for PM10 air pollution concentration levels, and need to obtain the individual hourly readings from them. The moving average data is updated every ...
4
votes
0answers
154 views

TOST equivalence test in excel

I had a prof once refer to doing science in excel as doing watch repair with oven mitts. Nevertheless, I'm trying... I'm trying to do a TOST test using excel. I'm basing the equivalence of variances ...
0
votes
1answer
56 views

When to use Excel versus R [closed]

I've realized the hard way that plotting things like dual y-axis plots is faster in Excel than in R but calculating means for spaghetti plots is easier to do in R than in Excel. In general, when's it ...
0
votes
1answer
86 views

Statistics: what kind of test to use to test this hypothesis?

I am unsure what kind of a hypothesis test to use in my case: So we are filling 500 ml bottles with water; It's a known fact that before a max of 3% of all the bottles could be under-filled; Now I ...
2
votes
1answer
35 views

adjusted R squared for multiple exact same input variables

I was trying to understand how adjusted $R^2$ in a simple linear regression behaves when there exists multicolinearity. And realized I could not replicate the adjusted $R^2$ provided by excel data ...
3
votes
3answers
107 views

How to test for statistical difference for discrete data

I'm designing an experiment to evaluate reagents used to develop fingerprints. There will be a variety of objects with different surfaces, 2 different reagents, 3 different fingerprint donors (prints ...
0
votes
0answers
35 views

Visualization of comparison between periods of times in excel

Is there any way to generate a graph that show comparison between time periods? I have start-time and end-time of two different series in date time type. The best solution, as I see it, is a graph ...
0
votes
1answer
110 views

EXCEL 2016 Adding Extra Columns to file when Saved as a .csv File [closed]

I have this .csv file that I created from an EXCEL 2016 spreadsheet. This file does not work when I try and upload it to an online web service I use because there are extra columns at the end of the ...
0
votes
0answers
223 views

Research question: Which stain is most effective for improving tissue contrast in CT scans of the larynx?

I'm doing research into which stain out of I2KI, PMA and PTA is most effective for improving tissue contrast in CT scans of the larynx. I have 9 specimens in I2KI (CT scanned after 0, and then either ...
0
votes
1answer
49 views

Two sample T test frequency table

I am struggling with a data analysis. I have made a survey and got the following answers in the first turn, I put them in a frequency table Don't agree at all: 8 Don't agree: 6 I don't know: 16 ...
0
votes
2answers
50 views

checking of correctness of excel program for F test

i would like to express my doubt about F test performed by Analysis toolpak , let us consider following link F Test in excel everything seems ok until calculation of one tail critical value, ...
1
vote
0answers
66 views

Correlation between Likert scale question and binary question?

I am writing a report and I need to find correlation between question: how much do you trust banks (from 1-I dont trust them at all and 5-i trust them completely) and question: are you a member of ...
0
votes
1answer
746 views

Cross sectional regression in excel [closed]

Hi Can someone please let me know whether cross sectional regression is possible in Excel? I'm trying to evaluate CAPM from MacBeth regression. I need to run the cross sectional regression, but ...
0
votes
2answers
20 views

How can I determine standard deviation in excel given the probability that values will fall within a defined range?

Assuming a normal distribution with a mean of zero, if I am told that 68.2% of the population values are within +/- 4.7, I can conclude that the standard deviation is also 4.7. But what if I am told ...
1
vote
0answers
19 views

Can I directly obtain standard deviation using an Excel formula knowing only median error rate, but no sample size? [closed]

I have access to statistical data with a median error rate of 4.7%. This means half of values fall within an error rate of 4.7%, and half fall outside this range. The sample size is not provided, but ...
0
votes
1answer
13 views

index and base year

I am creating a league table of a group of people with the aim to track their performance on 5 KPIs using indices. Base month value is set to 100. Out of 5 KPIs, an increase in 3 indices means good ...
0
votes
0answers
71 views

Transforming raw scores to scaled scores using a proprietary scale

I'm working with questionaire answers from the Child Behavior Checklist (CBCL) for my master's thesis. The 113 items on the questionaire are organized into several subscales for different areas of ...
0
votes
0answers
21 views

Testing forecast accuracy in Excel [duplicate]

I'm looking for some advice on how to determine how accurate a forecast is. Basically, what I have is 23 years of competitive results for a particular sport (10000+ matches). The matches are broken ...
1
vote
0answers
38 views

Interpretation of linear regression intercept

I am aware this is a very basic question. I just need clarification (for my sanity) For this regression: Is it true to say that: The regression outturn records the intercept as significant. (If ...
0
votes
1answer
48 views

Statistical analysis, repeated measures t test or anova? Trouble with input when running any test

I've tried using google sheets/excel, but I can't figure how to run a 2 factor without repetition ANOVA (if that's the appropriate test for this data set). I think it's the way I set the spreadsheet ...
0
votes
4answers
2k views

How to: Analysing 5-Point Likert Scale with ANOVA

I am very confused since I could not find an understandable answer for it: I must analyse my Likert Scale data with ANOVA to get the significance (using the ANOVA is the requirement...). I have to ...
2
votes
2answers
86 views

How would you go about modelling a sport with a fixed maximum score line (e.g. volleyball or tennis?)

In short, I don't have a great maths background but have always mucked about with modelling sports and making predictions (as I enjoy it). I have always used the Hal Stern "The Probability of Winning ...
0
votes
1answer
265 views

How does Excel interpolate / imputate missing values in time-series when fitting a line to a plot?

I have a scatter plot in Excel (upper part of the screenshot) of time-series data. In-between the values that I plot (to the left), are some missings. I fit a (linear) line to those values and display ...
0
votes
0answers
22 views

Weighted Standard Deviation in Excel [duplicate]

I have a dataset in excel that looks like this: ...
1
vote
1answer
580 views

Different regression coefficients in R and Excel

Asking a separate question because whilst this has been answered for polynomial regression the solution doesn't work for me. I'm performing a simple linear regression. Both R and Excel give the same ...
0
votes
1answer
171 views

Monte Carlo simulation--Have I applied Bernoulli distribution properly? [closed]

I am trying to run a monte carlo simulation and I just wanted to make sure that I set it up properly. A salesman visits 100 different homes. Someone answers the door 80% of the time. Of that ...
1
vote
0answers
42 views

Find correlation between names and score

I'm new to data mining and hope you can help me with my task :) First some details about my (input data) I've got an excel table with the following structure: ...
2
votes
1answer
26 views

Assessing pre-post survey data with change scores?

I have about 50 pre & post-surveys for an intervention. I am limited to using excel and was told to only calculate the change scores. For example: ...
0
votes
0answers
2k views

Standard Deviation of Discrete Random Variables in Excel?

I am looking for the Standard Deviation of the Discrete Random variable. I understand how to calculate it by hand but when I use the excel STDDEV function, I am getting a different answer. Is there a ...
0
votes
1answer
220 views

read.csv() columns NULL [closed]

This is a very simple question I hope, but just can't seem to solve myself. I'm trying to read a .csv file of integers into R using read.csv(), however for analysis reasons I need to convert all the ...
1
vote
1answer
37 views

Terminology Question (ANOVA/t-test) - With and Without Replication?

Coming from a Psychology background, I have always been taught statistics terms as "within subjects" and "between subjects". Now using excel I see they are using the terms Anova - 2 Factor With ...