I have two tables where I want to match age and height to the percentile they fall within (according to WHO guidelines). So if the ages in table_percentile and table_height match, find the percentile column in table_percentile that the height falls in from table_height. The percentiles (columns P3, P15, P50, P85, P97) contain heights (cm). I think I may need to create a min and max column for each percentile so there is a set range for the heights to fall between. So if the age is 0 days and the height 49 cm, the percentile would be P15 as it is > 47.217 and <49.148.
NB I have tried this in R with fuzzy join however R keeps crashing so I am trying to see if excel processing may be different. The dataset is almost 300,000 observations. I have cut them down by gender and age but its still crashing.
table_percentile
Age P3 P15 P50 P85 P97
1 0 45.644 47.217 49.148 51.078 52.651
2 1 45.808 47.383 49.317 51.250 52.825
3 2 45.971 47.549 49.485 51.422 53.000
4 3 46.134 47.714 49.654 51.594 53.175
5 4 46.297 47.880 49.823 51.766 53.349
6 5 46.461 48.046 49.992 51.938 53.524
7 6 46.624 48.212 50.161 52.110 53.698
table_height
Age Height
1 0 49.0
2 1 50.4
3 2 48.8
4 2 51.5
5 4 52.0
6 6 46.8
7 6 49.0
Output that I'd like to get
Age Height Percentile
1 0 49.0 P15
2 1 50.4 P50
3 2 48.8 P15
4 2 51.5 P85
5 4 52.0 P85
6 6 46.8 P3
7 6 49.0 P15
