put data into tertile groups in Excel

Question

[Bine]

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

[cpad0112]

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

Answer 1

[Dunois]

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"})

[Bine]

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 •

[Dunois]

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.

[Bine]

Ok thank you.

The BMI goes from 14.7 to 48.89.

[Dunois]

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.)

[Bine]

Thank you very much :)