Can you create acronyms from a list in Excel Sheet?

Can you crate the acronyms in Excel? If you are given a set of Strings and be asked to create the acronyms how will you proceed. My today's post is related to creating acronyms!!

Lets consider a list of string like one given here  (and adopted from here):

The formula we need to built will work by identifying the the characters followed by spaces, we will add a space to the start of each string. If the strings are present in Column A, the new string will be:

=" "&String

... and the table will look like:

You can see the extra space at the start of the string in Column D, now we can use this extra space as a delimiter and convert text-to-column operation, the operation results in words being placed in the individual columns:

Now we can get the first character of each individual word in the cell by using the formula =PROPER(LEFT(Cell_Ref)). We can simply put it in a cell below the table and drag to right and down to get another table. Here it is...

We can use now, the built in concatenate or any udf available from the internet. If we just keep it an ampersand sign, we can do it like this to get the desired result.

=TRIM(E18&F18&G18&H18&I18&J18&K18)

Obvisouly if it is a repetitive task, you can create a udf of your own or a macro to finish the task. This was just a quick tutorial to show how to do it. If you like it do share and comment. 

Survival Analysis with Kaplan-Meier Method

My today’s post is about Survival Analysis with Kaplan-Meier Method.

This post uses an example for a popular multivariate analysis textbook to emulate the problem and hence providing and excel based solution to it. For this post our point of focus will be how to emulate the analysis on Excel rather than theory. For theory there is several standard text available that can used for understanding theory behind the method.

The method we are discussing is Kaplan-Meier Method. The key is to make a table with survival data and then process with the calculations involved.

Interval Start: 

The first Cell is entered manually; rest equals the end interval of the upper group. Say for group 2, the start interval is equal to end interval of group 1. (A3 = B2)

Interval End: 

Start interval of the corresponding group multiplied by 2 to give end interval. For first group it is manually entered. 

Entered and Dropped: 

These are from your observational data. This basically described how many patients entered the time bracket we are considering for example for the first group, time bracket is from 0 to 1.2 month. For this group 7 in Entered means those 7 patients were admitted at the start of this group. Whereas 1 in dropped means that by the end of 1.2 months, 01 patient was lost or withdrew from the observation, thus only 6 patients entered the next time bracket o f 1.2 - 2.4 months. 

Proportion Dropped: 

This is calculated by no. of dropout divided by total number entered for a an interval. For our case it is D2/C2

Proportion Surviving: 

This is simply 1-(D2/C2) or for out sheet 1-E2, theoretically it is ..

$$Proportion Surviving=\frac{Total Patients - Dropouts}{Total Patients}$$

Cumulative Proportion: 

This stands for cumulative proportion surviving and this is of actual interest to most of the people. Formula for this is:

$$Cumulative Proportion=\frac{Patients Survived for till an Interval}{Total Dropouts}$$

For our case the formula is =C2/SUM($D$2:$D$11)

Creating a Graph:

For K-M Estimator, the standard graph is a stair-case like graph, but for the sake of simplicity and ease of use, a simple scatter plot can do the trick. 

With this chart you can add a trend. For trend lines, if our data starts with zero, we cannot use power and logarithmic trends lines, hence I have used polynomial line with 3 – degree and it is displayed as equation in the graph.

In the next post I will explain how to calculate the hazard function and density function and standard error for the survival analysis. You can download workbook from this link.