put data into tertile groups in Excel
1
0
Entering edit mode
9 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 • 1.7k views
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$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

1
Entering edit mode
9 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"})

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.

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.

1
Entering edit mode

Ok thank you.

The BMI goes from 14.7 to 48.89.

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

1
Entering edit mode

Thank you very much :)