Category Archives: Excel

Latex to Excel

Converting LaTeX tables to Excel is a pain; here are some steps I found useful for reformatting and removing persistent hidden whitespace characters:

  • remove the \\ symbols and import the latex text document in Excel with the ampersand symbol (&) set as a delimiter (simple and good idea from this blog post – thank you Ever Barbero!)
  • replacing intervening table code (e.g. \multicolumn{5}{c} ) and remaining curly brackets with nothing
  • and replacing sub-/super-script (eg. ^2) and other symbol codes (eg. \chi) with their equivalent unicode characters (e.g. χ²)
  • using the ‘TRIM(cell reference)’ formula in Excel to remove space characters except for single space between words/values…. However in itself I found this did not work satisfactorily.  Through trial and error I found that the embellishment of the TRIM() function as TRIM(CLEAN(cell reference)) removes hidden characters which trim alone may not. I had hidden spaces screwing up alignment of table items, and expanding this formula —actually, IF(ISBLANK(cell reference,””,TRIM(CLEAN(cell reference))) , so empty cells didnt’ return as errors — to reference the tables contents, and then pasted as values over the top solved this problem.

Whilst writing this post I found a StackOverflow post covering this and a few other tips which would have saved me some trial and error time if I found it sooner.

Table of two-sided P-values for the chi-squared distribution (d.f. = 1)

Frustrated at being unable to find a detailed chi-squared distribution for only 1 degree of freedom, as used for results of the Mantel Haenszel chi-squared test, to quickly reference your test statistic for the approximate two-sided P-value? I was, so delved into Excel to construct the following table – colour coded for glory! – using the formula “CHISQ.DIST.RT(x,deg_freedom)” for results from 0.000 (P-value = 1.00) to 15.975 (P-value = 0.000064) in increments of 0.025. Critical values are in bold.

To use, cross-reference your Mantel-Haenszel chi-squared result first with the whole numbers at the top of the columns and then with the approximate decimal point to find the corresponding approximate P-value. For example for MHX2 of 8.43 you would cross reference 8 at the top with 0.450 on the side to find the two-sided P-value of 0.0037.

Enjoy the rainbow of probability! It can be downloaded by clicking on the tools icon “>>” in the upper right-hand corner and selecting “download”.

Download the PDF file .

You can also make your own table of areas in the upper tail of the standard normal distribution (one-sided P-value) in Excel using the formula “=1-NORMSDIST(‘Z-score array’!D5)” Where ‘z-score array’ refers to a seperate worksheet (called ‘z-score array’ with an the arbitrarily demonstrated cell ‘D5’ which is one of many in an array of z-score values from 0 to 3.99 (or higher, I went to 4.49, but that’s a pretty small area in the tail there….), arranged with x.x down the left column and -.-x across the top column. Ha, or you could just look it up in a book, hey? 🙂

Download the PDF file .