put data into tertile groups in Excel
1
0
Entering edit mode
12 months ago
Bine ▴ 40

Dear all,

I have the following problem: I want to put my data into tertiles:

BMI  Tertile Group 
20   Low 
33   Medium 
45   High 
32   Medium`

`

Currently I only have the column "BMI". How do I do that? I can only find a quartile function in excel and this only tells me the number of the quartile but it doesnt put data into quartiles/tertiles.

Thank you so much for any hint!

Bine

excel • 2.1k views
ADD COMMENT
0
Entering edit mode

May be you can use BMI quantiles. Any thing below 25% is low, any thing above 75% is high and medium is between 25%-75%. Or you can use ntile option from dplyr to divide the data into 3 groups.

> BMI_range=c(14.7,48.89)
> Quan=quantile(BMI_range)
> df=read.csv("file.txt", sep="\t")
> df$group=ifelse (df$BMI<Quan[2],"Low",ifelse(df$BMI>Quan[4],"High","Medium"))
> df
  BMI Tertile.Group  group
1  20           Low    Low
2  33        Medium Medium
3  45          High   High
4  32       Medium` Medium
ADD REPLY
1
Entering edit mode
12 months ago
Dunois ★ 2.0k

Use a bunch of if-else and appropriate conditions? (E.g., if(VAL < 33) {VAL2 = "low"} else if(VAL >= 33 & VAL < 66) { VAL2 = "medium" } else if (val >= 66) {VAL2 = "high"})

ADD COMMENT
0
Entering edit mode

Thank you, I tried it but it gives me error with this

=IF((R2< 33) {S2 = "low"} ELSE IF(R2 >= 33 & R2 < 66) { S2 = "medium" } ELSE IF (R2 >= 66) {S2= "high"})

      R       S
  1  BMI  Tertile Group 
  2  20   Low 
  3  33   Medium 
  4  45   High 
  5  32   Medium

Also my tertile are not <33 , >66.. I am not sure how to calculate them for my data.

ADD COMMENT • link • edit •

ADD REPLY
1
Entering edit mode

Ah that example was just pseudocode. You'd have to nest multiple IF statements in Excel to achieve the if-else effect (see here).

As for the breakdown of the tertiles, What are the minimum and maximum values of your data? Hopefully, if the data just go from 0-100 or something, then it's just the maximum value divided by 3 to get the upper limit for the lowest bin, 2x this value for the 2nd bin, and so forth.

ADD REPLY
1
Entering edit mode

Ok thank you.

The BMI goes from 14.7 to 48.89.

ADD REPLY
1
Entering edit mode

The range is (48.89 - 14.70)

The lower limit for your BMI is 14.70.

The first tertile would be lower limit + 0.33 * range. That is 14.70 + 0.33 * (48.89 - 14.70) ~ 25.98. The second one would be lower limit + 0.66 * range ~ 37.27. You should be able to bin your data into three with these values as breakpoints.

(Also take a look at the solutions presented here.)

ADD REPLY
1
Entering edit mode

Thank you very much :)

ADD REPLY

Login before adding your answer.

Traffic: 1152 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6