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.
449 questions
3
votes
3
answers
369
views
Correct method for Chi-square testing for yes/no data
I have following data:
I am trying to analyze it by applying Chi-square test in Excel with CHITEST(Data B, Data E) function:
I also tried with using only the &...
- 10.4k
2
votes
2
answers
236
views
Approximating a Compound Poisson Distribution
I work as an engineer at a large factory, and I am trying to model the costs due to rejects each year. Given that the number of rejects ($N$) can be modelled as a Poisson distribution, and the cost of ...
9
votes
3
answers
730
views
Two-sample t-test with hypothesized mean difference: a bug in Excel?
I have two independent samples (i.e., wait times at Starbucks and Dunkin) as follows:
Starbucks
Dunkin
7.2
5.8
6.5
6.2
8.1
6.6
7.4
7.2
7
5.9
8.3
6
6.8
6.5
7.9
5.6
8
6.2
7.1
5.8
6.9
6.3
8.2
5.7
7.5
6.1
...
- 275
3
votes
2
answers
223
views
Data preparation for binary logistic with households as a unit of analysis
I am trying to run a binary logistic on different factors, to establish which factors shape the study phenomena. This data is for the people above a specified age. This means the number of people in ...
- 371
0
votes
2
answers
98
views
Compare frequencies - which approach? (Excel-based)
I'd like to check if two groups differ significantly in proportions.
To make this more concrete: I have sub-group A that represents X% of group 1, and sub-group B that represents Y% of another group 2....
- 129
4
votes
1
answer
186
views
Clarifying the default "standard error" for error bars in Microsoft Excel/Powerpoint plots (calculated without N or SD) [closed]
I have noticed that Excel allows you to toggle "error bars" for any given plot and one of the options is to have the error bars denote standard errors. This is peculiar since if you do a ...
- 1,464
2
votes
0
answers
65
views
Unsure as to what tests, how, and potential in a research scenario
There are three classrooms of students of different grades. I want to analyze what variables of these influenced likelihood of scoring 0 on a test. The factors are: grade (8, 9 or 10) and stress level ...
- 21
0
votes
0
answers
81
views
Transferring a regression with ARIMA error model from R to Excel (formula help)
I'm trying to translate an ARIMA model into an Excel spreadsheet for calculations for something I'm studying.
The model is a ARIMA(4,1,0) that I used the ...
- 1
0
votes
0
answers
71
views
Generate weighted median from activity per timespan
I have a set of data where I have a number of observations over the course of a year per individual. Generally speaking, I want to know the average activeness of the individuals that participated in ...
- 107
2
votes
0
answers
65
views
Testing time series data stationarity
I am working with time series and want test different forecasting methods but first I need to test if my time series (sales) data is stationary or not. So I have been learning about KPSS and Dickey-...
- 31
1
vote
1
answer
98
views
What is the best model for this case?
I have the following problem:
A data set, which is about the soft drink consumption of people, that
covers 300 subjects are available to us. Using Excel tabulations and
graphing capabilities only:
...
- 103
0
votes
1
answer
152
views
Problems with a constant in regression analysis in Excel [duplicate]
I am using regression analysis in excel for a dataset. If I take into account the constant, then I get terrible r-squared values, but the indicators of t-statistics and p-values indicate that it is ...
0
votes
0
answers
55
views
A good variable to make a regression model for gas usage over the years for a city
so I'm new to statistics, I'm trying to make a regression model in Excel, explaining why, or due to what variable, does the gas usage change over the years. I tried using a basic Y variable - Time - ...
- 1
1
vote
1
answer
82
views
Linear Regression Excel Question [closed]
Let's say I am comparing the impact on features for a housing market. If I were to take the data for all houses in a region with a value between USD 200,000 and USD 500,000 and compare how individual ...
- 11
2
votes
1
answer
179
views
What is a numerically stable way to generate an exponential distribution that properly yields very large, low-probability values in Excel and C++?
I have sets of sampled data with the following statistics:
Because the mean is so close to the min, and because of our understanding of the process that generated the samples, we are treating the ...
- 331
0
votes
0
answers
105
views
Average of Percents, or Sum Total / Sum Total?
I have some data and need to know the average commission of all the items. For each row, I calculate the percent of items sent out. If I wanted to know, "What is the average commission", ...
- 103
1
vote
0
answers
145
views
Plotting 3 points per data set in lollipop-type chart [closed]
I am wanting to plot a graph where I have multiple data points per category of data. For some context, I have done some analysis on different samples and now have up to 3 3 data points for each sample ...
- 11
0
votes
0
answers
65
views
Can I do a multiple linear regression analysis with a mixture of raw data and index data?
I'm trying to do a multiple linear regression analysis in Excel using the Analysis Toolpak and I am not good at math, let alone stats. So please excuse my total ignorance. I'm using the following ...
- 1
0
votes
1
answer
114
views
Is a chi-square test sufficient to test for a significance difference for my data?
I am working on a project about what behaviour humpback whales show in a certain area along the coast of Mozambique. Now I am trying to test the significance, whether this behaviour is random through ...
4
votes
2
answers
641
views
Comparing pre and post treatment data - best statistical test?
I am analysing a range of clinical outcomes for a drug treatment and need to compare the values pre and post treatment. I’m using Excel to do this and have found that for most of my outcomes the data ...
- 41
0
votes
1
answer
64
views
Data normalisation for a newbie
I am new to data analysis and I have been given a task to compare safety of trams compared to buses. Since there are far more buses than trams, I was introduced to the concept of normalisation by ...
2
votes
1
answer
158
views
Comparison of Data that looks at correlation and absolute values
I am looking for a way to compare two sets of data in order to find out how similar they are to each other.
My application: I try to compare multiple Measurement methods that both measure the sound ...
- 23
4
votes
1
answer
115
views
Is the variance a suitable measure of variability in this context?
A rather basic question, prefaced by the fact that I know just enough about statistics to be dangerous.
I work with Air Force Technical Training. I am trying to compare the ebb and flow of students ...
- 41
0
votes
0
answers
43
views
I have two data signals with respect to time how can I plot them against each other and eliminate time if the data points are not equal?
I have two signals each have been plotted against time is there any way to eliminate the time and plot the signal1 vs signal2. The problem I am facing is the number of data points is not equal signal ...
0
votes
0
answers
162
views
Excel using confidence intervals when forecasting
My understanding is that when forecasting if you want to quantify the level of uncertainty of your model, one would typically use predictive intervals. However in Microsoft Excel, when using the '...
- 1
1
vote
1
answer
3k
views
Calculation of the GINI coefficient,Accuracy and AUROC for credit scoring using Python code
I have the following data and I want to compute the GINI and Accuracy for model validation purposes. But I tried to calculate the GINI and Accuracy using Python code, but it seems incorrect. I would ...
- 1,869
1
vote
0
answers
65
views
Approximate X given 5 function values and y values
Given
5 Lines(table)
X values and corresponding Y1,Y2,...Y5.
How can I calculate the approximate X value given the corresponding Y's?
How can I tweak the formula if I want to weight to bias the ...
1
vote
1
answer
1k
views
Why is the mean of the means not equal to the grand mean (average)? [duplicate]
I'm using Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20374) 32-bit
I have 31 numbers from 6 different centers.
I need an average for each center and an average for all.
If ...
BlackWidowWeb
1
vote
0
answers
90
views
What does the NORM.INV function in excel actually do? [closed]
I understand that it does the opposite of NORMDIST function and returns a normal cumulative distribution. But is there a formula that it uses? Is it using the Quantile function?
- 11
0
votes
1
answer
89
views
t-test shows significant differences when graph doesn't
I've made t-test in excel with my samples and the graph showing mean values with standard deviation. t-test shows significant difference, when standard deviation lines cross on the graph. Could you ...
0
votes
1
answer
58
views
Multivariate analysis for subjective decision making
I am trying to find the best US state using 25 columns of normalized data (best = 1, worst = 0) such as crime rate, GDP, house prices, and others. This results in a 50x25 Excel table. Afterward, each ...
Neil Peña Martínez
4
votes
1
answer
2k
views
Why does the same data get different $R^2$ using three methods (`r2_score` & `fit trendline` in Excel & linear regression in SPSS)?
For the same set of data
x1=1, y1=3
x1=2, y1=2
x1=3, y1=1
calculated by r2_score:
from sklearn.metrics import r2_score
...
- 41
0
votes
0
answers
61
views
In a specifically ordered set of binary data (ones and zeros), how can you organize them in patterns and from there build a probabilistic network? [duplicate]
So I have a set of 1s and 0s. They are listed in a column on excel. They are listed in a specific order. I do not wish to change their order.
So they appear as (1, 0, 1, 0, 0, 1 and so on... 0).
Here ...
0
votes
1
answer
165
views
Comparing the results of multiple regressions of the sample sample for different year
For a period of ten years, I have to identify, if there are timely variations of impact of the independent variables on the dependent variable.
So far, I have run an RE regression for the entire ...
- 1
0
votes
1
answer
92
views
What is the proper test to determine if a result is above the normal set of values?
I'm a chemist who has been asked to put my statistics hat on, and I'm looking for some help.
Situation: Every month we get a report with the number of guest complaints for each item we sell. Each ...
- 1
0
votes
0
answers
660
views
What kind of distribution to use for data between a known max and min?
This is probably a stupidly simple question, but I am absolutely a layperson when it comes to most of this stuff and I understand just very surface level stuff. Everything I can find online searching ...
- 101
4
votes
3
answers
1k
views
How to get standards errors of the parameters of a non-linear model (R and Excel)
I am working on the movement of fish species from the centre of a protected area to a non-protected area. Based on the article by R. Abesamis, itself inspired by the work of B. Kaunda-Arar (page 91), ...
- 97
2
votes
1
answer
1k
views
Calculate Power Level in Excel for 2 sample proportional test
I have a need to calculate statistical power (the chance of making a Type II error) within Excel for a 2 sample proportional Z test.
Here's a example to better explain. Say I have two unequal samples ...
- 73
-1
votes
1
answer
120
views
Simple OLS on Excel shows wrong R^2 value?
I'm trying to make an OLS regression on Excel on those 2 values :
Y X
4 2
5 3
According to wikipedia definition, R^2 = SSR/SST
Here if you compute the ...
- 250
2
votes
1
answer
112
views
Visualizing Numerical Data with 2 Independent Variables on a Single Visualization
This is how my data looks like:
...
- 123
0
votes
0
answers
53
views
How to determine statistical significance of a study with multiple A/B questions?
I ran a survey that gives participants multiple A/B pairs (16 pairs). For each of the pairs, I ask the participant to select Option A or Option B. Therefore, I have 16 responses per participant. The ...
- 1
2
votes
1
answer
6k
views
How can I choose between homoscedastic and heteroscedastic?
I want to calculate the p-value between subgroups of my samples. For that, I am using the T.TEST function of Excel. But I do not understand the last parameter, type:
Paired
Two-sample equal variance (...
- 121
2
votes
1
answer
74
views
Change in arrival from year to year
I am using to Excel to analyze a dataset I have. I'm looking at bird migration. I have the date the birds were seen and the number of birds (abundance). I have a dataset of 10 years and I am trying to ...
- 21
0
votes
0
answers
46
views
Calculate item and customer wise distribution list based on min and max
I initially tried to do this directly in SQL Server but it seems like it can't be possible through query so I want to calculate this "Distribute" column in Excel. Below is the details of the ...
1
vote
1
answer
175
views
Help with Excel's Regression Output
I'm a junior engineer at a small biotech company and have some (real) data from a fractional factorial DoE (3 factors, 2 levels, 4 test conditions with six replicates each). Currently, we use excel to ...
0
votes
3
answers
792
views
Statistical Data Analysis using "Sum" Function
Most commonly when I hear descriptive data analysis using statistics these following functions are often inclded:
Mean
Standard Deviation
Variance
Range
Mode
Median etc.
Is the function "Sum&...
- 163
0
votes
1
answer
883
views
Get Standard Deviation and Variance of log 10 data in Excel
In Excel, my data is between 0.000001 and 0.005 and treated as "logarithmic" so I transformed by log 10 (log10(x)) which are all negative.
The Excel Var.P() and Stdev.p() functions can only ...
0
votes
1
answer
38
views
How to Analyze Data to optimize committee Allocations? [closed]
On Google Sheets, I have collected responses from my team members to assign them to be committee members in any of the 4 following committees: Internal, External, Membership, Speaker Management. ...
- 3
1
vote
0
answers
227
views
How to perform two-sample t-tests in Excel by inputting sample statistics rather than the raw data? [closed]
I have large data sets that I can easily summarize in Excel Pivot tables. I would like to be able to use that summary data (Means, St. Dev, n) to write a formula to get the t-value and if possible ...
0
votes
1
answer
1k
views
Logistic Regression using Excel Solver
Suppose there is a problem where a business analyst works for an energy company and they want to find out the customer probability that a given set of customers will churn and move over to other ...
- 3