Getting false divide by zero in Excel
I have a column of numbers.
When I try calculating the standard deviation in Excel by using STDEV(A1:A97)
I get a '#DIV/o!' notice. But there are no zeroes or false values in that range.
All the data is formatted as numbers with 3 decimal places.
What am I missing?
1 answer

Figured it out.
The problem was some of the cells contained a carriage return after the number, so I guess Excel counted those as zero.
After using the
TRIM()
function, I was able to calculate what I needed.
See also questions close to this topic

How to develop insurance calculation in ios app
I want to integrate insurance calculation to my ios app. E.g: There are different product for user choice and accordingly there are different calculators (excel sheet). (Excel sheet contains input and output).
How best is possible to handle it. I have calculation in excel sheet. How insurance company like Reliance/LIC etc.. handle such a big calculation in there apps.
Is it possible to send the user entered input to excel and get the calculated output from excel and show to the user. How best we can solve? Please help.

share sheet if a cell matches a cell in another sheet
Let’s say I have two googlesheets (master and slave). Master googlesheet has a sheet contains two columns (usernames and passwords) and other sheets. The slave googlesheet is like a sign in sheet. If the entered values match a pair of username and password in Master googlesheet then share a certain sheet in Master googlesheet to slave googlesheet (“share” not only copy data using importrange).
Is that possible? Especially the part of sharing a sheet from a googlesheet to another googlesheet depending on a cell value?

Extracting information from text file in VBA
I'm currently trying to extract data from a text file as part of an analytics challenge in my place of work. The text file is a bunch of data in lines with each heading/entry separated by a comma.
I've looked at several examples of text extraction online but the furthest I've gotten is getting one line in a single cell and then Excel freezing. All others have just frozen Excel after I've put in my conditions.
My current attempts involve the following:
Do Until EOF #1, textLine Line Input #1, textLine Do Until Count = Len(text line) + 1 Text = Text & Mid(textLine, Count, Count) If Right(text, 1) = "," Then textImport = Left(text, Count  1) Cells(rowCount, column count) = textImport Text = "" columnCount = columnCount + 1 Loop rowCount = rowCount + 1 Loop
Can anyone advise where I'm going wrong? I can't share any of the data or the text file due to the nature of the challenge and the data involved.

Plots of prior and posterior distributions for different models
Consider the code shown below that displays graphically the prior and posterior of the BetaBinomial Model using different parameters in the prior.
colors = c("red","blue","green","orange","purple") n = 10 N = 10 theta = .2 x = rbinom(n,N,theta) grid = seq(0,2,.01) alpha = c(.5,5,1,2,2) beta = c(.5,1,3,2,5) plot(grid,grid,type="n",xlim=c(0,1),ylim=c(0,4),xlab="",ylab="Prior Density", main="Prior Distributions", las=1) for(i in 1:length(alpha)){ prior = dbeta(grid,alpha[i],beta[i]) lines(grid,prior,col=colors[i],lwd=2) } legend("topleft", legend=c("Beta(0.5,0.5)", "Beta(5,1)", "Beta(1,3)", "Beta(2,2)", "Beta(2,5)"), lwd=rep(2,5), col=colors, bty="n", ncol=3) for(i in 1:length(alpha)){ dev.new() plot(grid,grid,type="n",xlim=c(0,1),ylim=c(0,10),xlab="",ylab="Density",xaxs="i",yaxs="i", main="Prior and Posterior Distribution") alpha.star = alpha[i] + sum(x) beta.star = beta[i] + n*N  sum(x) prior = dbeta(grid,alpha[i],beta[i]) post = dbeta(grid,alpha.star,beta.star) lines(grid,post,lwd=2) lines(grid,prior,col=colors[i],lwd=2) legend("topright",c("Prior","Posterior"),col=c(colors[i],"black"),lwd=2) }
Some of the plots
How to have similar codes to the one above for the PoissonGamma model and inverse chi squaredNormal model?
What I’ve tried for the PoissonGamma is first to change the
x=rpois(n,lambda)
, and to change Beta by Gamma because here the prior has Gamma distribution. For the inverse chi squaredNormal model, would bex=rinvgamma(alpha,beta)
, here the prior and posterior both have Inverse Gamma distribution.Where I am having more difficulties is in this part
alpha.star = alpha[i] + sum(x) beta.star = beta[i] + n*N  sum(x) prior = dbeta(grid,alpha[i],beta[i]) post = dbeta(grid,alpha.star,beta.star)
I don’t know how to change it so that fits for this new model. I have the same issue for the inverse chi squaredNormal model.
Could someone please help?
I would really appreciate any help you're willing to provide.
Any suggestion in the code is welcome.
The code can be founded here https://stats.stackexchange.com/questions/70661/howdoesthebetaprioraffecttheposteriorunderabinomiallikelihood

how to convert text variable to numeric variable in r
I got the problem to convert text variable to numeric variable. I think I successful construct the dummy variable with several levels. However, when i try to read the datafile, the categories of these variable are still text..
here is my code, output, and datafile:
after that, when I try to generate the correlation value, it still shows error:
Error in cor(hpNumeric) : 'x' must be numeric
Any suggestions are appreciated !

about dummy variables in r
I am new to the R language, for my assignment, I am trying to generate several levels dummies for different variables(total in 3). however, each approach i got problem:
method1: followed by https://stats.idre.ucla.edu/r/modules/codingforcategoricalvariablesinregressionmodels/ The code:
> housing_prices2$Fuel.Type.f < factor(housing_prices2$Fuel.Type) > is.factor(housing_prices2$Fuel.Type.f) [1] TRUE > housing_prices2$Fuel.Type.f[1:10] [1] Electric Gas Gas Gas Gas Gas Oil [8] Oil Electric Gas Levels: Electric Gas None Oil Solar Unknown/Other Wood
works well. However, when I got problem in next line:
> summary(lm(write ~ Fuel.Type.f, data = housing_prices2)) Error in model.frame.default(formula = write ~ Fuel.Type.f, data = housing_prices2,: object is not a matrix
I just have no idea about this error and it doesn't make sense to me, so I decided to use another method;
method2: followed by Convert categorical variables to numeric in R
for variable Fuel.Type, it works well:
> Fuel.Type < as.factor(c("Electric", "Gas", "None", "Oil", "Solar", "Unknown/Other", + "Wood")) > Fuel.Type [1] Electric Gas None Oil Solar [6] Unknown/Other Wood Levels: Electric Gas None Oil Solar Unknown/Other Wood > unclass(Fuel.Type) [1] 1 2 3 4 5 6 7 attr(,"levels") [1] "Electric" "Gas" "None" "Oil" [5] "Solar" "Unknown/Other" "Wood"
but when I try to generate dummies for other variables, then i got this error:
> housing_prices2$Heat.Type.f[1:10] NULL Warning message: Unknown or uninitialised column: 'Heat.Type.f'.
I have clueless about what's going on about these error either... any suggestions are appreciated!
BTW, here is my sample data table:
>$ Fuel.Type : chr "Electric" "Gas" "Gas" "Gas" >$ Heat.Type : chr "Electric" "Hot Water" "Hot Water" "Hot Air" >$ Sewer.Type : chr "Private" "Private" "Public" "Private"