Microsoft Excel Data Analysis - Science topic

First, you need to do the correlation between your target variable and each of your potential independent variables. After checking what independent variables are the more correlated to your target variable (as mentioned earlier coefficient correlation closest to - 1 or + 1). Once, you decide according to these correlation coefficients which variables you can select for your model, you need to ensure that there will be no multicollinearity in your model. To ensure that, for each independent variable you do correlation tests again. If two independent variables are too correlated, you should introduce only one in your model (e.g. the variable which had the higher correlation rate with your dependent variable).

Hello Dineo

Here attached the code to calculate eGFR according to the CKD -EPI 2021

gen eGFR01 = .

replace eGFR01 = 142 * (PreopCreatinine/0.9)^(-1.2) * 0.9938^Ageatdx if SexM1==1 & PreopCreatinine > 0.9

replace eGFR01 = 142 * (PreopCreatinine/0.9)^(-0.302) * 0.9938^Ageatdx if SexM1==1 & PreopCreatinine <= 0.9

replace eGFR01 = 142 * (PreopCreatinine/0.7)^(-1.2) * 0.9938^Ageatdx * 1.012 if SexM1==0 & PreopCreatinine > 0.7

replace eGFR01 = 142 * (PreopCreatinine/0.7)^(-0.241) * 0.9938^Ageatdx * 1.012 if SexM1==0 & PreopCreatinine <= 0.7

best regards.

EoQ5D is a prescribed structure, we cannot manipulate it. None will accept. In UK some health economics experts are discussed on " we needs to change EoQ5D as with time with transition of disease the method needs to modify", Professor Ric Fordham,UK is one of them.

As answer above use "Visual Analog Scale" if you wants to said QALY.

Additionally, can think about;

Likert scale:

It's a question that uses a 5 or 7-point scale. Typically, the Likert survey question includes a moderate or neutral option in its scale.

"R squared is higher" -- compared to what?

"STDEV isn't lower" -- compared to what?

The STDEV of the (absolute) differences between x- and y-values correlates with the correlation between x and y: the higher the correlation between x and y, the smaller the STDEV of their absolute differences should be. But in your case I don't see with what you are comparing your results - you have only a single sample.

I'd do this in R. It's free and sufficiently powerful to hande such tables with ease.

See www.r-project.org.

This could be an example work-flow (assuming you have a file named "my_huge_file.txt" which is a tab-delimited text file with many rows and a header row that contains the texts "ID", "value", "name" and "amount" [just for example!] as column names):

# read the file into a data.frame:

df <- read.delim("my_huge_file.txt")

# sort the rows by values in the column "name", then "value":

df <- df[order(df$name, df$value), ]

# remove all rows with dupplicate entries in the column "ID":

df <- df[!duplicated(df$ID), ]

# get the 5 rows with the largest values in the column "amount":

o <- order(df$amount, decreasing = TRUE)[1:5]

df <- df[o, ]

# getting the mean of the values in the colum "value"

# by each value in the column "name":

tapply(df$value, INDEX = df$name, FUN = mean)

The issue is that you are using art() from ARTool to fit the model and that can't handle missing values. You could use listwise deletion by passing na.omit(d.f) to the art() function - though this would potentially bias results (though no more than using na.rm=TRUE in anova() or lm().

A better solution is to use multiple imputation (e.g., with the mice package in R), though I'm not sure if that works directly with art() models or to use a different approach to handle your data (which presumably aren't suitable for linear models). You could use a transformation, a generalized linear model, robust regression etc. depending on the nature of the data.

Wolfgang Schechinger

I don't know LibreOffice but Excel. (BTW: Why do you use it instead of Microsoft Office?)

I will answer your question in terms of Excel.

See the attached workbook "Shapes in Plots.xlsx" as an example.

Your method is indeed very tedious and impractical. I suggest two better ways:

A) Manually paint a shape on the diagram.

B) Insert a calculated polygon as another point(x/y) data series into the diagram.

The disadvantage of A is that the shape does not automatically adapt to the data, but must be adjusted manually. But what is simple.

With B, on the other hand, the polygon adapts directly to the data. However, you cannot fill it with a color or a pattern.

In my example (see sheet "Parabola") I use a parabola

y = -ax*(x - xm)^2 + ym

with given maximum (xm, ym) and form factor ax. From this, the zeros are calculated and a table with x/y data is set up.

From this a diagram with the (smoothed) parabola function and additionally the interesting points is created as usual. I want to draw a rectangle between the zeros and the maximum.

Method A is demonstrated and explained on sheet "With Shape". This results in "Shapes in Plots With Shape.png":

Method B is demonstrated and explained on sheet "With Polygon". This results in "Shapes in Plots With Polygon.png":

Good luck

Dear Ryan Bush

Here are some links that might be helpful.

Rasidah Mohd-Rashid I hope the answer is not late, I suppose you want to add year fixed effects to control for year to year variation, this is a perfectly normal situation for event studies. Ideally, to not have a lot of dummy variables and noise, there should be few effects to be controlled, so having few years of data is fine.

Am not sure I get what your problems is... If your leave the cell empty, I believe you should be fine.

For example

cell1 = 30

cell2 =

cell3 20, etc.

Thank you all

You have 20 variables bio1 -bio19 and elev with many of highly pairwise correlated. This means that there is redundant information and uncertainty in the data that makes it difficult to attribute and interpret the contribution of one or more variables. Knowing the pairwise linear correlation coefficients does not help to reduce redundant information and extract meaningful information for separate contributing factors.

I suggest using a principal component decomposition methodology that allows one to perform a multivariate correlation analysis and identifies redundant variables that carry little or no independent information while retaining only a few mutually uncorrelated principal variables (components) that contain practically all original information. This technique is a special case of a matrix approximation procedure called singular value decomposition. The higher the level of correlation between the columns of data of the original matrix, the fewer the number of new (principal) variables is required to describe the original data set.

You can perform principal component analysis using various statistical packages, such as R, Matlab, or Minitab. The latter requires no coding at all.

An overview of principal component analysis can be found in many books on multivariate analysis.

For a RG discussion on the meaning of the 'online' (or 'on-line') terminology at this forum:

FGS try the videos or even z-library for some examples

Best, D Booth

My admittedly rough translation to english is for a line of code towards the bottom...within the following lines :

'Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

' ' شغل الدكتور

' Dim xlsApplicaton As Excel.Application

In english this could read as…

The problem is the hours and the name of the course. It does not give me a string

Hi Naouel Athmane, did you found the information on how the track manager plugin in icy software calculates the MSD plot?

or anyone knows the information?

Hi, Aakash Gupta!

You can compare the chunk in which the number and parameter names match. Try using principal component analysis (PCA). If you throw off the dataset, I'll try to find the time interval of the error, and show it on the graph. I work in python.

For Engineering Drawing and Drafting - AUTOCAD , AUTOLISP - https://www.googleadservices.com/pagead/aclk?sa=L&ai=DChcSEwjHvdH33s3wAhWUGysKHSbgB_8YABACGgJzZg&ae=2&ohost=www.google.com&cid=CAESQeD21lFg8kobrLfbRfBP6KHILzwtk-fwgrQIfUzMvmXrvQVAq7LzESZTeGQ_xRvBYl7Ll3s6vRp-3oaq1rpHTZWp&sig=AOD64_1hcTsl_IoiqphAIy6GmEw6dHUeKA&q&adurl&ved=2ahUKEwio7cf33s3wAhXTX30KHWyGAAoQ0Qx6BAgCEAE

Use the =Countif function to count the number of times each unique entry appears in the original list.

There are some free on-line services for splitting large files, why not to try with that... Next, I did a small Python recipe to split text file of any size into multiple chunks:

Try to look for it here:

Ok, had a look.

The problem is the student is plotting Cq (a log2 metric) against concentration (a linear metric), which gives you a ridiculous skew that is impossible to fit a line to. You can fit a log-curve to it (really well, for obvious reasons), but this is not how you do generally do it.

You either linearise your Cq values, or log transform your concentrations (the latter is much easier, and arguably more informative, here).

This (a log/log plot) generates linear data, with (as expected) the gradient of -3.28.

I've attached my edited version.

I also tried to find the Excel version of STS calculator, but I still didn’t find it. There are more than 60 parameters & it’s convenient to use it on STS site.

The first step is to use an option in Excel called "export file". Next, you choose one of the possible formats for the exported file. The most commonly used are either text (*.txt) with tab or spaces delimiters , or comma delimiters (*.csv).

The result is tables of data that you can use in any plot program available. As already noted by Oya Tagit, the most suitable program for scientific charts is "Origin". If you do not have it, on the website https://www.originlab.com you can start mastering it from the free version.

Following on from David Eugene Booth 's comment, many statistical procedure allow you to weight the subsamples to take into account different sample sizes and different university populations.

Any meaningful statistical analysis aimed at comparing the efficiency of two countries in handling COVID pandemic MUST be based on dynamic data (daily or weekly) over a long period rather than on one-time data collected for one particular day, such as 14th August 2020. No meaningful analysis can be performed using only one-time data.

I'm interested in this question too!

Thank you Professor Vallejo. If I will reduce the levels in the first variable it will not show any significant response. But I will increase the levels in 2nd variable.

Lacey Valois , if your time series are simply recorded at two different frequencies and are thus not synchronised, you can for example use the Hayashi-Yoshida approach to compute a covariance, without removing any observation:

Hayashi, T., & Yoshida, N. (2005). On covariance estimation of non-synchronously observed diffusion processes. Bernoulli, 11(2), 359-379.

In addition, if you have some doubts on the accuracy of the times of the recording, you can increase the time step when computing the correlation (taking for example into account one observation in two). By doing so, you should see an increase of the correlation (known as Epps effect).

Thank you.

The calculation of the correlation is done based on the time invariant data pairs (xi,yi). For the calculation of the returns the derivative between 2-time steps is considered, right. This is in principle something different and here we see a nice example that this can lead to interesting results.

The correlation of the day by day return is zero. Conclusion: there is no intraday correlation between these two values.

But there might by a correlation between day i and day i+n. So try to correlate (xi,yi+1), (xi, yi+2),… and you will observe an interesting result 😉. Hope that helped – and good luck.

Some improvements can be made to Ian's solution especially if you are working with students learning programming and/or VBA.

That might be overkill for business analysts or people who just need something done in Excel, but an important step (I think!) for people wanting to learn better coding skills that may be applicable to other languages.

Suggestions welcome!

The issues of the use of information contained in Big Data database systems for the purposes of conducting Business Intelligence analyzes are described in the publications:

I invite you to discussion and cooperation. Greetings

It really depends what the nature of the data is, and what hypothesis you want to test. For example, a t-test may appropriate, or Wilcoxon-Mann-Whitney, or Mood's median test, and so on.

Hello Haseeb Shah

Consider that the data [x1 x2 x3 x4 x5 x6 x7] are in columns A2:A8

and the data [y1 x2 y3 y4 y5 y6 y7] are in columns B2:B8

and we wish to fit the quartic polynomial y = a*x^4+ b*x^3 +c*x^2 +d*x + e

to the two data sets

The command to obtain the coefficients a, b, c, d, e is

LINEST(B2:B8,A2:A8^{1,2,3, 4},TRUE,FALSE)

To determine coefficients of a a pentic plonomial we should write ^{1,2, 3, 4, 5} instead

Hello Praveen,

Scilab has the opportunity with xls_read and xls_open to import files from excel. By using write_csv you are able to write data in a an excel file.

With best regards,

Thomas

Index-Match can get multiple columns.

You can add a third sheet, and paste this formula in all the cells (A1:D20) assuming you have the names in Table 1 range A1:A20, and the cells to be matched at Table 2 range A1:D20.

The formula is:

=INDEX('Table 2'!$A$1:$E$20,MATCH('Table 1'!$A1,'Table 2'!$A$1:$A$20,-1),COLUMN())

I attached an excel file with the formula.

Thank you so much, Alexandra!

You can do this easily by adding a Table at SciNote project (say to the results), then copy data from Excel and paste to it. If you want to copy larger data or a full Table, you can Import it by "Add file" button, or you can go to "Samples" Menu, then "Import" Button, and point to your excel file. (The excel file should include header row with data names followed by the data)

Joseph, the resolution of screenshots of the spreadsheets is (more than) correct (3600x1080) but RG doesn't scale those images correctly. Download them to your computer and open in any image viewer.

I'm not convinced if HISTOGRAM will do what is required by Sebastian.

Dear Muhammad,

I use R statistical program. Although I don't know if you use R, I am posting a script I have developed which gives sums of monthly rainfall from your data. It may be useful for others also who may need a quick way of doing this. It is appended/pasted below..

setwd("D:\\IMTR\\AMTC16_STUDENT PROJECTS\\Victor_Bible") ## set your own directory/path in your computer drive where you saved your data--Data_solved--

yourdata<-read.csv("Data_solved.csv",header=TRUE) ### Note and Ensure you save the Data_solved file in comma seperated (.csv) excel format

head(yourdata) # to view your data

##we are interested in Column 2, 3 and 4 aka Year, Month and Value

# You can use the function tapply, list and sum to get your solution as indicated below

monthlysum<-tapply(yourdata$Value, list(year=yourdata$Year, month=yourdata$Month), sum) # this function sums the dailies into monthly totals in each year

monthlysum<-as.data.frame(monthlysum) # convert to a table

head(monthlysum) # view the out put

write.csv(monthlysum, "Monthlyraintotal.csv") # rename and save the final out put in the working directory as "Monthlyraintotal"

Hi,

I am attaching another article with the formulations that I used in the Excel file.

You can see there how CI and CR are calculated.

They can be 0 when consistency is perfect (like in the initial matrices when all the choices are 1). Usually in real life choices it will not happen.

BR

Elli

Anyone with a similar question might also want to consider Meta-Essentials, a set of free and simple workbooks for Microsoft Excel. Among many other features, it also produces forest plots with subgroups. Have a look at www.meta-essentials.com if you like.

Good luck!

Henk

I would say this is difficult to do in Excel and quite convoluted. You can do this simply using python by downloading your data as a csv. You can read your data in pandas, then just use regular expressions to look for these names. I can help you with this. Feel free to contact me by leaving a message on my site(arindampaul.me)

Do you mean a problem like http://www.ualberta.ca/~cneuman/mgtsc461/Lectures/Lecture%204%20Sep%2030/lecture4a.ppt (slides 4, 5 and 7)? Just define it as a binary linear problem in the way compatible with Solver.

@Gerhard

Q: All of "January" values in just one column (I mean 12 months)?

Ans: the Kleomenis`s suggestion works well.

or several column`s of January (According to the length of your data)? 

Ans: May "Pivot table" be a solution.

Hello, I'm not sure what you mean by a "dataset containing separate no. of column and row". R doesn't worry about this. This new problem looks the same as the first one posted.

Read the data from a file into a table:

> data<-read.table(file="c_pr.csv",sep=",",row.names=1,header=TRUE)

The row labels from column 1 of the .csv file become row names in the R object "data" so its elements are purely numerical values. That is, there are no columns of metadata, only columns of data.

The analysis with the zyp package follows by

> test.output<-zyp.trend.dataframe(data, metadata.cols=0, method="zhang",conf.intervals=TRUE, preserve.range.for.sig.test=TRUE)

and is viewed by

> test.output

R (https://cran.r-project.org/) has a lot of options of plots vizualization. There is R package for Matlab and others.

Dr. Mohammad,

You`ve provided a comprehensive list. Let me share another interesting ones.

QURʾAN in Excel.

Dear, You may find useful information in the attached link

   

@NaIr; You can use Mann-Whitney Test. It's available on SPSS.

 Dear Mustafa,

A direction in MS-excel!

The BLUE line:

INTERCEPT= INTERCEPT(known_y's,known_x's)

SLOPE= SLOPE(known_y's,known_x's)

To estimate Y=50 use:

For the RED line, follow as above.

I do hope it will be what you want. (an example is attached).

Raoof

The following are a couple of excellent articles on how to do non-linear fitting in Excel. These will allow/teach you to use any formula you want for the fit, so you are not limited by build-in formulas.

Kemmer, G. and S. Keller (2010). "Nonlinear least-squares data fitting in Excel spreadsheets." Nature Protocols 5(2): 267-281.

http:\\dx.doi.org\10.1038/nprot.2009.182

Brown, A. M. (2001). "A step-by-step guide to non-linear regression analysis of experimental data using a Microsoft Excel spreadsheet." Computer Methods and Programs in Biomedicine 65(3): 191-200.

http:\\dx.doi.org\10.1016/S0169-2607(00)00124-3

Note that if you find the appropriate formula to fit your calibration curve, you are not limited to the linear part of the calibration curve. Of course the error will be higher the closer you get to the plateau.

Mohammad, try the attached file. Basically I removed all unnecessary columns then re-ordered the TRF's from lowest to highest. You only need three columns (sample ID, TRF, area). You should probably remove rows based on the size standard that you used...typically you will remove data from trf's below 100 and above 500. Keep in mind that your first TRF sample point has to be in the B3 cell (column B row 3). You can add data from another sample in the same file, just skip a row and continue with the same format. Hope this helps.

Dear  Jessica,

I think, with the three answers you have received so far, you have found a clue foryour question. If you have some data relevant to the surface net radiation, you might find another simple solution for your question. As you know, surface net radiation is the subtract of Net Shortwave (SWi-SWo) radiation from Net Longwave radiation (LWi-LWo). Rnet=(SWi-SWo)-(LWi-LWo). LWi can be determined from the relationship, if you access above radiation components.

i have one such template. i hope it helps.

Rayala, maybe then you need to formulate your question more precisely - or provide more context - so those who want to help you can give you a more specific response.

I was looking for a method to do something similar. I found two workable solutions.

The best was to use the Publish or Perish software (http://www.harzing.com/pop.htm). It cycles through the pages of a Google Scholar search results list and copies the basic information for each result to a results list that can be copied in CSV or Excel format.

The other method was to use Zotero (www.zotero.org) as an add-on to Firefox. You can use it to save the results on a single page of a Google Scholar results list (maximum 20 items). The drawback of this is that there doesn't seem to be a way to cycle automatically through the entire results list.

There is actually nothing really impossible in Excel, but many things are really really hard to do with Excel in an automated way (adding fake data serias to show the connecting lines, and more fake data series to show the p-values; calculation of the correct positions is cumebrsome and the correct formatting is hell). I would add the extra annotations simply as graphical objects (lines and text), either in Excel itself with a graphics software in the exported graphic file. (Many people here do it in Powerpoint, but that's not my favorite because it gets messy when you have to prepare a figure for publication in a given file format (say, tiff), size, and resolution... this is better done in appropriate software like Photoshop and alikes).

Regarding the "normalized gene expressions", and without knowing much about your specific experiment and data: I hope you are aware that such measures ([relative/normalized] expression) are usually not normal distributed, and that therefore "mean", "SEM" and "t-test" are usually not sensible. Often, it is reasonable to assume a log-normal distribution here, so you should do all your statistics on the log-expression values (where you can sensibly calculate means and SEMs and t-tests).

Also note that a bar plot is the (unfortunately most common but) worst possible way to present expression data. If your y-axis is "expression", note that the uncertainty is not symmetric around the location measure, so showing only one-sided error bars would be wrong (or at best incomplete). In any case, make sure that the bars extend to a meaningfur reference value (this may or may not be 1 for "expression" and 0 for "log expression", depends on the normalization and meaning of the data). A far better alternative is to show dots instead of bars (either a single dot/symbol at the position of the location measure or showing the complete data with each dot representing an individual measurement. You can then still use error bars to indicate SEMs or (better!) confidence intervals. Wheras bar plot highlight the absolute (mean) values compared to the reference (where often simply 0 is used, as in the attached example), the dot plots highlight the differences between the groups, what is typically the main research question. If you have >20 or so values per group you could also go for a box(-and-whisker-)plot (unfortunately not really available in Excel; workarounds and tricks known to me fail when values are negative). I attached an example showing the same data as "1d scatterplot" (individual data), "dot plot" (with error bars) and "box plot".

You can install and use the data analysis "add in" and use the regression command.

Dear Cansus,

The following links may be helpful. Have a try.

hello

you can used the artificial neural network ANN tools. it is very good for normalize and regression.

also you can transform this black box tools to an open tools by used the wight from ANN with Excel sheet. every think in this way it is clear .

According to this, Prism offers the Holm correction: http://www.graphpad.com/guides/prism/6/statistics/index.htm?stat_holms_multiple_comparison_test.htm.

Hi Mohsen,

If I understand you correctly you cultures 1 bacterial strain under 8 conditions and now you want to compare these conditions for 35 VOCs (this is quite little for GCMS analysis of a culture, how did you do peak detection, alignment and matching?).

Normally you would just use a kruskall wallis (dependent: multiple groups, independent: not-normally distributed variable; voc concentration) and the ones that have a p-value below 0.05 you can perform post-hoc tests (you can choose this in SPSS). However, since you test multiple vocs you will have to take care of multiple comparison by lowering alpha (p-value treshold).

Several additional questions arise: how many duplicates per culture condition do you have, this effects the effect size you can discover. Can you convert the 8 categories into subcategories? This increases power etc.

Could you provide additional information?

Lieuwe

Totally agree with Clementine., but with small modification,

if you plot y vs. x,

then convert y to Y=log(y)

then plot the new Y vs. x

Dear Slawomir,

Thanks for your useful links.

In your opinion, can we summarize the several questions in just ONE?

Agree with Justinsir:

First one - STDEV indicate standard deviation - a deviation within observations of a particular group from the mean value, while

Second one - STDEV/SQRT (number of iteration) - indicates SEM - Standard Error of Mean - It is the standard deviation of the sample-mean's estimate of a population mean.

This is a stripplot, done in Stata, with superimposed boxplots. The stripplot command, written by Nick Cox, will also superimpose means and confidence intervals (these can be calculated as normal, binomial or poisson). You can install it in Stata by typing

. ssc install stripplot

Excel is quiet a powerful instrument often used in biomedical research as a support for datasets. It does contain some basic statistical analysis and could serve for simple inferences.