# Extract and Analyse data from multiple excel files and sheets

29 views (last 30 days)
Stormero on 27 Mar 2021
Extract and Analyse data from multiple excel files and sheets.
Hi all,
I have a dataset in Excel collected from 6 different treatments. Each treatment produces a dataset of N-by-P rows and columns. While N (rows) varies with the treatments, the number of parameters produced (P) is the same. My challenge is to:
1. Import these data into MATLAB.
2. With reference to a particular parameter, select only rows (N) that don’t have NaN or rows that have values above a particular threshold value of N.
3. Produce an N*-by-6 dataset to be compared for each parameter, P. Thus, I will have P subsets of data to analyse, each on its own (where N* is the upper limit of N as dictated by the treatment that produced the highest N. Each P-subset of data is on its own.
4. For each of the above subsets, I need to copy into 2 blocks of data to be analysed a bit differently For Block 1: Find the basic statistics – mean, median, range, IQR, standard deviation and SEM for each column representing each treatment. For Block 2: Find the IQR for each treatment and discard any data outside the IQR. From what remains, find basic statistics – mean, median, range, IQR, standard deviation and SEM for each column representing each treatment.
5. Do basic plot for each parameter in both blocks: Box-and-whiskers, bar chart of mean with SEM and N indicated for each treatment.
6. Using the mean of the control group as threshold, divide each P-subset into 2 datasets – a dataset containing only values in each column (treatment) above the mean of the control group and those below as another dataset. Do basic plotting for each - Box-and-whiskers, bar chart of mean, 2-column bar chart of fractions that fall above and below mean of control group, and maybe a Pie chart of either of the fractions for each treatment.
7. Conduct a Normality test for each P-subset in each Block. Choose between One-way ANOVA and Kruskal Wallis test. Run the appropriate test followed by a post-hoc test.
8. (Replot the Box-and-whiskers, bar chart of mean with SEM, N and p-value indicated against each treatment).
If I can have one function or programme to do all these, great. But if you can only help with sections, please by all means do help!
Thank you!

Sulaymon Eshkabilov on 27 Mar 2021