I have table with age values in column 1
and weight values in the remaining columns indicating the weight centiles for a given age, simplified:

age,c10,c25,c50,c75,c90
-----------------------
1,5,6,7,8,9

2,6,7,8,9,10

3,7,8,9,10,11

So, for example age 3, weight 9 then
that would give c50 (50th centile, that is average weight for that age).

How would I do that in SQL, so giving me
c50 for values age 3 and weight 9?

RBS

The reason for your problem is that you are using column names as data.

Your centiles need to go into a separate table.

CREATE TABLE centiles (ID INTEGER PRIMARY KEY, cent TEXT);
CREATE TABLE weights  (ID INTEGER PRIMARY KEY, age INTEGER, WEIGHT INTEGER, CENT_ID INTEGER REFERENCES centiles(ID), UNIQUE(age,weight));

SELECT c.cent FROM weights w JOIN centiles c ON (c.ID = w.cent_id) WHERE w.age = 3 and w.weight = 9;

Yes, I can see that would be a solution, but that would mean I have to make those 2 tables in code. Data is always supplied in the format of the posted simplified table. I was hoping I could get the column rank with a window function or a CTE.

RBS

I think the easiest solution is to "tranpose" the table:
Centiles in column 1, field name Centile.
Ages as further field names.
Weights in table columns 2 till last.
I can then do:
Select centile from T where A3 = 9

RBS

That would be "spreadsheet thinking". Doable but arduous. Try some "database thinking" instead. There are good reasons for normalizing tables in databases. They just work so much better when used as designed.

What is preventing you from loading the non-normalized data into normalized tables? That would be the classic job of the "input" phase: convert data to the form best suited to the actual processing.

What if some day the input format is changed to a different number of precentiles or the borders change? You would have to rewrite all of the queries, as opposed to adjusting only the input conversion.

Thanks, will have a look at all the mentioned options.

RBS

Well, that's not how SQL works, but, in the interest of fun....

Are the tables ALWAYS the same layout? (i.e. have the exact same columns?)

If that is the case, then one can do something like this:

```
WITH normalized(age, weight, percentile) AS (
    SELECT age, c10, 10 FROM centiles UNION ALL
    SELECT age, c25, 25 FROM centiles UNION ALL
    SELECT age, c50, 50 FROM centiles UNION ALL
    SELECT age, c75, 75 FROM centiles UNION ALL
    SELECT age, c90, 90 FROM centiles 
)
SELECT *
  FROM normalized
;


-- And to get the specific percentile for Age 3 at weight 9 - simply chenge the end query to 

SELECT percentile
  FROM normalized
 WHERE age = 3 AND weight = 9
;


EDIT: I should add that the table "centiles" being used is constructed from the OP's given layout - resulting in:

CREATE TABLE centiles(age, c10, c25, c50, c75, c90);
INSERT INTO centiles VALUES 
 ( 1, 5, 6, 7,  8,  9)
,( 2, 6, 7, 8,  9, 10)
,( 3, 7, 8, 9, 10, 11)
;
```

This will work for any size table and can be adjusted to suit more columns.

I agree with Gunter's post - this should really be fixed in the INPUT phase, which will future proof your queries.

If you still only want to fix it as it is now, I suggest perhaps changing my suggestion, considering Gunter's comments, to be a view that can be queried in lots of places, but fixed in one place, should the need arise:

```
CREATE TABLE centiles(age, c10, c25, c50, c75, c90);
INSERT INTO centiles VALUES 
 ( 1, 5, 6, 7,  8,  9)
,( 2, 6, 7, 8,  9, 10)
,( 3, 7, 8, 9, 10, 11)
;


CREATE VIEW centiles_normed AS
  SELECT 0 AS age, 0 AS weight, 0 AS percentile UNION ALL
  SELECT age, c10, 10 FROM centiles UNION ALL
  SELECT age, c25, 25 FROM centiles UNION ALL
  SELECT age, c50, 50 FROM centiles UNION ALL
  SELECT age, c75, 75 FROM centiles UNION ALL
  SELECT age, c90, 90 FROM centiles 
;

(Note: The first select simply adds the column names, but you can do that with an actual row too if you wish not to have a Zero age row)


To later get the specific percentiles, you can have queries like this:

SELECT percentile
  FROM centiles_normed
 WHERE age = 3 AND weight = 9
;
```

Thanks, will have a look at all the mentioned options.

RBS

Thanks, this works nicely indeed and moved the data to a table, holding all possible combinations of sex, months and weight.
I am trying to work out what the best way is to get the centile from the input data as the provided months and weight won't be in the table.
So for example months 6, weight 8 how would I get the centile. One option is to take the nearest months and weight and I can do that, but ideally I would make it proportional to get the more accurate centile as if it was taken from a chart. I can do this in code, but I would like to do it in SQL and not sure how to do that.

RBS

I think I have this worked out now:

DDL of table:
CREATE TABLE WEIGHT_CENT_M36(MALE INTEGER, MONTHS REAL, WEIGHT REAL, CENTILE INTEGER)

Sample data (up to 2.5 months):

MALE	MONTHS	WEIGHT	CENTILE
---------------------------------
1	0.0	2.36	3
1	0.0	2.53	5
1	0.0	2.77	10
1	0.0	3.15	25
1	0.0	3.53	50
1	0.0	3.88	75
1	0.0	4.17	90
1	0.0	4.34	95
1	0.0	4.45	97
1	0.5	2.80	3
1	0.5	2.96	5
1	0.5	3.21	10
1	0.5	3.60	25
1	0.5	4.00	50
1	0.5	4.39	75
1	0.5	4.72	90
1	0.5	4.91	95
1	0.5	5.03	97
1	1.5	3.61	3
1	1.5	3.77	5
1	1.5	4.02	10
1	1.5	4.43	25
1	1.5	4.88	50
1	1.5	5.33	75
1	1.5	5.73	90
1	1.5	5.97	95
1	1.5	6.12	97
1	2.5	4.34	3
1	2.5	4.50	5
1	2.5	4.75	10
1	2.5	5.18	25
1	2.5	5.67	50
1	2.5	6.18	75
1	2.5	6.64	90
1	2.5	6.92	95
1	2.5	7.11	97
2	0.0	2.41	3
2	0.0	2.55	5
2	0.0	2.75	10
2	0.0	3.06	25
2	0.0	3.40	50
2	0.0	3.72	75
2	0.0	3.99	90
2	0.0	4.15	95
2	0.0	4.25	97
2	0.5	2.76	3
2	0.5	2.89	5
2	0.5	3.10	10
2	0.5	3.44	25
2	0.5	3.80	50
2	0.5	4.15	75
2	0.5	4.45	90
2	0.5	4.63	95
2	0.5	4.74	97
2	1.5	3.40	3
2	1.5	3.55	5
2	1.5	3.77	10
2	1.5	4.14	25
2	1.5	4.54	50
2	1.5	4.95	75
2	1.5	5.31	90
2	1.5	5.52	95
2	1.5	5.66	97
2	2.5	4.00	3
2	2.5	4.15	5
2	2.5	4.39	10
2	2.5	4.78	25
2	2.5	5.23	50
2	2.5	5.68	75
2	2.5	6.09	90
2	2.5	6.33	95
2	2.5	6.49	97

SQL:

select round(max(min(c * (m / ?), 97), 3), 0) as cent from 
(select months as m, weight as w, abs(1 - weight / ?) as p, centile as c from WEIGHT_CENT_M36
where male = 1 and
months > (select max(months) from WEIGHT_CENT_M36 where months < ?)
and 
months <= (select min(months) from WEIGHT_CENT_M36 where months >= ?)
order by p asc limit 1)

where the first place holder is months and the second one weight and the 2 remaining ones months again.
This seems to be working OK in some light testing.

RBS

Had left the weight out of the correction and SQL should be:

select round(max(min(c * (m / ?) * (? / w), 97), 3), 0) as cent from (select months as m, weight as w, abs(1 - weight / ?) as p, centile as c from WEIGHT_CENT_M36 where male = 1 and months > (select max(months) from WEIGHT_CENT_M36 where months < ?) and months <= (select min(months) from WEIGHT_CENT_M36 where months >= ?) order by p asc limit 1)

where the second placeholder is the supplied weight.

Still, not quite right as I can see still relatively big centile differences with small provided values differences.

RBS

This seems a bit better:

select coalesce(round(max(min(c * (m / ?) * (? / w), 97), 3), 0), "") as cent from
(select
age_months as m,
weight as w,
abs(1 - age_months / ?) as p1,
abs(1 - weight / ?) as p2,
centile as c
from weight_centiles_m36
where male = ?
order by p1 asc, p2 asc limit 1)

where parameters are:
months, weight, months, weight

Still this doesn't give smooth chart-like values and I think I may need to interpolate month and weight values in the source table.

RBS

I'd like to help more, but honestly I'm at a loss what you are trying to do or achieve with the math. Taking your word the math is correct, I'm still unsure of what the problem or intended use/result is.

I think it's a typical type of problem where the result/answer is to clear to you because your mind has been involved with it intimately for a while now and the problem definition is second nature to you by now, but none of us know what you really need or why you need it, so it's all guessing.

If you give an example table with clear columns, and a definition of what you need from it and an example result that would be correct (with enough example rows to ensure only one interpretation of the query can work), we can help make that Query happen - but I'm afraid the current definition is hard to follow.

One example, quoting you:
 
> Still, not quite right as I can see still relatively big centile differences with small provided values differences.

Why are big centile differences wrong? Different to what? Should it not be? Why not? Are the calcs working correctly when not in a query? What small value differences? Provided how and why??   
You may as well have told us that you are on duty because the train station near you blew its left drum again.  It raises very many more questions than it answers.

> Why are big centile differences wrong?

This is data for weight centile charts, given an age (here in months) and a weight and thirdly male or female. They will give centiles, meaning for that give age and weight and sex x % of children will be below that weight for that age and  and the remaining % will be above that weight for that age. If for a given age the weight is slowly supplied higher and higher then the centile values should slowly go up. The data provided is only provided step-wise, so I have to work out interpolating values. This is the full original data set, so not yet normalised:

Sex	Agemos	P3	P5	P10	P25	P50	P75	P90	P95	P97
1	0.0	2.355451	2.526904	2.773802	3.150611	3.530203	3.879077	4.172493	4.340293	4.446488
1	0.5	2.799549	2.964656	3.209510	3.597396	4.003106	4.387423	4.718161	4.910130	5.032625
1	1.5	3.614688	3.774849	4.020561	4.428873	4.879525	5.327328	5.728153	5.967102	6.121929
1	2.5	4.342341	4.503255	4.754479	5.183378	5.672889	6.175598	6.638979	6.921119	7.106250
1	3.5	4.992898	5.157412	5.416803	5.866806	6.391392	6.942217	7.460702	7.781401	7.993878
1	4.5	5.575169	5.744752	6.013716	6.484969	7.041836	7.635323	8.202193	8.556813	8.793444
1	5.5	6.096775	6.272175	6.551379	7.043627	7.630425	8.262033	8.871384	9.255615	9.513307
1	6.5	6.564430	6.745993	7.035656	7.548346	8.162951	8.828786	9.475466	9.885436	10.161350
1	7.5	6.984123	7.171952	7.472021	8.004399	8.644832	9.341490	10.021014	10.453314	10.744924
1	8.5	7.361236	7.555287	7.865533	8.416719	9.081120	9.805593	10.514064	10.965736	11.270838
1	9.5	7.700624	7.900755	8.220839	8.789882	9.476500	10.226124	10.960172	11.428676	11.745385
1	10.5	8.006677	8.212684	8.542195	9.128110	9.835308	10.607722	11.364450	11.847633	12.174357
1	11.5	8.283365	8.495000	8.833486	9.435279	10.161536	10.954660	11.731602	12.227661	12.563083
1	12.5	8.534275	8.751264	9.098246	9.714942	10.458854	11.270871	12.065948	12.573402	12.916450
1	13.5	8.762649	8.984701	9.339688	9.970338	10.730626	11.559963	12.371453	12.889108	13.238933
1	14.5	8.971407	9.198222	9.560722	10.204418	10.979925	11.825241	12.651749	13.178670	13.534622
1	15.5	9.163180	9.394454	9.763982	10.419863	11.209555	12.069725	12.910152	13.445640	13.807244
1	16.5	9.340328	9.575757	9.951840	10.619101	11.422068	12.296170	13.149687	13.693251	14.060194
1	17.5	9.504964	9.744251	10.126434	10.804329	11.619777	12.507080	13.373106	13.924442	14.296548
1	18.5	9.658975	9.901830	10.289680	10.977527	11.804779	12.704728	13.582902	14.141875	14.519093
1	19.5	9.804039	10.050187	10.443295	11.140475	11.978966	12.891168	13.781331	14.347954	14.730343
1	20.5	9.941645	10.190823	10.588812	11.294771	12.144043	13.068254	13.970425	14.544842	14.932559
1	21.5	10.073105	10.325070	10.727592	11.441847	12.301541	13.237653	14.152010	14.734482	15.127765
1	22.5	10.199575	10.454102	10.860844	11.582978	12.452830	13.400856	14.327718	14.918606	15.317770
1	23.5	10.322062	10.578949	10.989635	11.719299	12.599135	13.559197	14.499004	15.098756	15.504178
1	24.5	10.441442	10.700513	11.114904	11.851817	12.741544	13.713860	14.667158	15.276296	15.688406
1	25.5	10.558473	10.819575	11.237473	11.981419	12.881023	13.865896	14.833316	15.452424	15.871699
1	26.5	10.673803	10.936812	11.358059	12.108888	13.018424	14.016230	14.998478	15.628189	16.055142
1	27.5	10.787982	11.052801	11.477280	12.234907	13.154497	14.165673	15.163512	15.804500	16.239671
1	28.5	10.901473	11.168034	11.595670	12.360072	13.289897	14.314932	15.329172	15.982139	16.426090
1	29.5	11.014664	11.282926	11.713683	12.484898	13.425194	14.464622	15.496103	16.161769	16.615076
1	30.5	11.127870	11.397820	11.831705	12.609827	13.560881	14.615270	15.664853	16.343950	16.807196
1	31.5	11.241348	11.513000	11.950054	12.735234	13.697379	14.767324	15.835883	16.529146	17.002914
1	32.5	11.355298	11.628692	12.068997	12.861438	13.835046	14.921166	16.009575	16.717730	17.202598
1	33.5	11.469880	11.745079	12.188748	12.988699	13.974182	15.077109	16.186239	16.910002	17.406538
1	34.5	11.585210	11.862300	12.309477	13.117232	14.115032	15.235412	16.366119	17.106191	17.614947
1	35.5	11.701371	11.980456	12.431315	13.247207	14.257796	15.396279	16.549405	17.306461	17.827972
1	36.0	11.759784	12.039910	12.492682	13.312776	14.329944	15.477724	16.642369	17.408165	17.936247
2	0.0	2.414112	2.547905	2.747222	3.064865	3.399186	3.717519	3.992572	4.152637	4.254922
2	0.5	2.756917	2.894442	3.101767	3.437628	3.797528	4.145594	4.450126	4.628836	4.743582
2	1.5	3.402293	3.547610	3.770157	4.138994	4.544777	4.946766	5.305632	5.519169	5.657379
2	2.5	3.997806	4.150639	4.387042	4.784820	5.230584	5.680083	6.087641	6.332837	6.492574
2	3.5	4.547383	4.707123	4.955926	5.379141	5.859961	6.351512	6.802770	7.076723	7.256166
2	4.5	5.054539	5.220488	5.480295	5.925888	6.437588	6.966524	7.457119	7.757234	7.954730
2	5.5	5.522500	5.693974	5.963510	6.428828	6.967850	7.530180	8.056331	8.380330	8.594413
2	6.5	5.954272	6.130641	6.408775	6.891533	7.454854	8.047178	8.605636	8.951544	9.180938
2	7.5	6.352668	6.533373	6.819122	7.317373	7.902436	8.521877	9.109878	9.476009	9.719621
2	8.5	6.720328	6.904886	7.197414	7.709516	8.314178	8.958324	9.573546	9.958480	10.215388
2	9.5	7.059732	7.247736	7.546342	8.070932	8.693418	9.360271	10.000792	10.403355	10.672801
2	10.5	7.373212	7.564327	7.868436	8.404400	9.043262	9.731193	10.395451	10.814695	11.096070
2	11.5	7.662959	7.856916	8.166069	8.712513	9.366594	10.074306	10.761063	11.196246	11.489076
2	12.5	7.931030	8.127621	8.441460	8.997692	9.666089	10.392576	11.100887	11.551451	11.855391
2	13.5	8.179356	8.378425	8.696684	9.262185	9.944226	10.688742	11.417920	11.883477	12.198293
2	14.5	8.409744	8.611186	8.933680	9.508085	10.203294	10.965321	11.714911	12.195223	12.520785
2	15.5	8.623887	8.827638	9.154251	9.737329	10.445406	11.224627	11.994379	12.489340	12.825610
2	16.5	8.823370	9.029399	9.360079	9.951715	10.672507	11.468779	12.258624	12.768249	13.115272
2	17.5	9.009668	9.217980	9.552723	10.152900	10.886386	11.699718	12.509741	13.034150	13.392045
2	18.5	9.184160	9.394782	9.733630	10.342415	11.088682	11.919211	12.749637	13.289041	13.657990
2	19.5	9.348127	9.561110	9.904140	10.521669	11.280895	12.128870	12.980037	13.534728	13.914971
2	20.5	9.502760	9.718170	10.065489	10.691956	11.464397	12.330156	13.202503	13.772841	14.164667
2	21.5	9.649162	9.867081	10.218820	10.854462	11.640434	12.524391	13.418439	14.004844	14.408584
2	22.5	9.788355	10.008874	10.365180	11.010271	11.810139	12.712769	13.629108	14.232045	14.648068
2	23.5	9.921281	10.144499	10.505533	11.160373	11.974537	12.896363	13.835635	14.455614	14.884316
2	24.5	10.048808	10.274829	10.640760	11.305666	12.134555	13.076132	14.039025	14.676585	15.118389
2	25.5	10.171734	10.400664	10.771667	11.446967	12.291025	13.252935	14.240165	14.895872	15.351221
2	26.5	10.290791	10.522736	10.898985	11.585013	12.444692	13.427531	14.439838	15.114276	15.583627
2	27.5	10.406644	10.641710	11.023380	11.720466	12.596223	13.600594	14.638729	15.332495	15.816318
2	28.5	10.519902	10.758192	11.145454	11.853924	12.746209	13.772713	14.837433	15.551131	16.049903
2	29.5	10.631115	10.872728	11.265746	11.985917	12.895172	13.944404	15.036464	15.770701	16.284906
2	30.5	10.740782	10.985812	11.384743	12.116919	13.043572	14.116113	15.236260	15.991643	16.521765
2	31.5	10.849350	11.097886	11.502878	12.247348	13.191808	14.288225	15.437192	16.214324	16.760846
2	32.5	10.957219	11.209345	11.620537	12.377573	13.340229	14.461065	15.639570	16.439044	17.002447
2	33.5	11.064747	11.320538	11.738058	12.507913	13.489134	14.634908	15.843647	16.666048	17.246809
2	34.5	11.172248	11.431774	11.855740	12.638647	13.638774	14.809981	16.049626	16.895526	17.494115
2	35.5	11.279999	11.543323	11.973842	12.770013	13.789365	14.986470	16.257667	17.127623	17.744504
2	36.0	11.334045	11.599289	12.033121	12.835999	13.865074	15.075293	16.362500	17.244688	17.870885

Hopefully this makes it clearer.

RBS

There is something I overlooked in the provided data:
These files contain the L, M, and S parameters needed to generate exact percentiles and z-scores.
Got these files from here:
https://www.cdc.gov/growthcharts/percentile_data_files.htm
Not sure how these L, M, and S parameters
but it looks they are needed to get the missing values, so will have a look into this.

RBS

I made things difficult by keeping the months column as real and looking at lower and higher months rows.
just providing an integer month, ranging from 0 to 37 makes things a lot easier and this simple SQL:

select centile from
weight_centiles_m36
where male = ? and
age_months = ?
order by abs(? - weight)
limit 1

With the supplied parameters sex, months and weight gets the centile nearest to the provided data. This is for now good enough.
Will have a look at making the centile more accurate, based on the difference between the provided weight and picked weight from the normalized table (made as suggested by Ryan Smith).

I couldn't make the formula as in the quoted file work to get the outcome it should give:

To obtain the z-score (Z) and corresponding percentile for a given measurement (X), use the following equation:

    ((X/M)**L) – 1
Z = ————————-, L≠0
    LS

or

Z = ln(X/M)/S ,L=0

where X is the physical measurement (e.g. weight, length, head circumference, stature or calculated BMI value) and L, M and S are the values from the appropriate table corresponding to the age in months of the child (or length/stature). (X/M)**L means raising the quantity (X/M) to the Lth power.
For example, to obtain the weight-for-age z-score of a 9-month-old male who weighs 9.7 kg, we would look up the L, M and S values from the WTAGEINF table, which are L=-0.1600954, M=9.476500305, and S=0.11218624. Using the equation above, we calculate that the z-score for this child is 0.207. This z-score corresponds to the 58th percentile.

Not sure what exactly is meant with LS as in the first formula.

Nearly there though.


RBS

All sorted now and although this has little to do with SQLite now, I might as well show how this was done. This is coding in B4A, for Android phones.
As it turns out I only need this clinical data:

Sex	Agemos	M	S
------------------------------------------
1	0	3.530203168	0.152385273
1	1	4.003106424	0.146025021
1	2	4.879525083	0.136478767
1	3	5.672888765	0.129677511
1	4	6.391391982	0.124717085
1	5	7.041836432	0.121040119
1	6	7.630425182	0.1182712
1	7	8.162951035	0.116153695
1	8	8.644832479	0.114510349
1	9	9.081119817	0.113217163
1	10	9.476500305	0.11218624
1	11	9.835307701	0.111354536
1	12	10.16153567	0.110676413
1	13	10.45885399	0.110118635
1	14	10.7306256	0.109656941
1	15	10.97992482	0.109273653
1	16	11.20955529	0.10895596
1	17	11.4220677	0.108694678
1	18	11.61977698	0.108483324
1	19	11.80477902	0.108317416
1	20	11.9789663	0.108193944
1	21	12.14404334	0.108110954
1	22	12.30154103	0.108067236
1	23	12.45283028	0.108062078
1	24	12.59913494	0.108095077
1	25	12.74154396	0.108166005
1	26	12.88102276	0.108274705
1	27	13.01842382	0.108421024
1	28	13.1544966	0.108604769
1	29	13.28989667	0.108825681
1	30	13.42519408	0.109083423
1	31	13.56088113	0.109377581
1	32	13.69737858	0.109707646
1	33	13.83504622	0.110073084
1	34	13.97418199	0.110473238
1	35	14.1150324	0.1109074
1	36	14.25779618	0.111374787
1	37	14.32994444	0.111620652
2	0	3.39918645	0.142106724
2	1	3.79752846	0.138075916
2	2	4.544776513	0.131733888
2	3	5.230584214	0.126892697
2	4	5.859960798	0.123025182
2	5	6.437587751	0.119840911
2	6	6.967850457	0.117166868
2	7	7.454854109	0.11489384
2	8	7.902436186	0.112949644
2	9	8.314178377	0.11128469
2	10	8.693418423	0.109863709
2	11	9.043261854	0.10866078
2	12	9.366593571	0.10765621
2	13	9.666089185	0.106834517
2	14	9.944226063	0.106183085
2	15	10.20329397	0.105691242
2	16	10.4454058	0.105349631
2	17	10.67250698	0.105149754
2	18	10.88638558	0.105083666
2	19	11.08868151	0.105143752
2	20	11.28089537	0.105322575
2	21	11.46439708	0.10561278
2	22	11.64043402	0.106007025
2	23	11.81013895	0.106497957
2	24	11.97453748	0.107078197
2	25	12.13455528	0.107740346
2	26	12.2910249	0.108477009
2	27	12.44469237	0.109280822
2	28	12.59622335	0.110144488
2	29	12.74620911	0.111060814
2	30	12.89517218	0.112022758
2	31	13.04357164	0.113023466
2	32	13.19180827	0.114056316
2	33	13.34022934	0.115114952
2	34	13.48913357	0.116193337
2	35	13.63877446	0.11728575
2	36	13.78936547	0.118386847
2	37	13.86507382	0.118939087

And then a conversion table to convert Z-Score to centile

Z_Score,Centile
----------------
-2.326,1
-2.054,2
-1.881,3
-1.751,4
-1.645,5
-1.555,6
-1.476,7
-1.405,8
-1.341,9
-1.282,10
-1.227,11
-1.175,12
-1.126,13
-1.08,14
-1.036,15
-0.994,16
-0.954,17
-0.915,18
-0.878,19
-0.842,20
-0.806,21
-0.772,22
-0.739,23
-0.706,24
-0.674,25
-0.643,26
-0.613,27
-0.583,28
-0.553,29
-0.524,30
-0.496,31
-0.468,32
-0.44,33
-0.412,34
-0.385,35
-0.358,36
-0.332,37
-0.305,38
-0.279,39
-0.253,40
-0.228,41
-0.202,42
-0.176,43
-0.151,44
-0.126,45
-0.1,46
-0.075,47
-0.05,48
-0.025,49
0,50
0.025,51
0.05,52
0.075,53
0.1,54
0.126,55
0.151,56
0.176,57
0.202,58
0.228,59
0.253,60
0.279,61
0.305,62
0.332,63
0.358,64
0.385,65
0.412,66
0.44,67
0.468,68
0.496,69
0.524,70
0.553,71
0.583,72
0.613,73
0.643,74
0.674,75
0.706,76
0.739,77
0.772,78
0.806,79
0.842,80
0.878,81
0.915,82
0.954,83
0.994,84
1.036,85
1.08,86
1.126,87
1.175,88
1.227,89
1.282,90
1.341,91
1.405,92
1.476,93
1.555,94
1.645,95
1.751,96
1.881,97
2.054,98
2.326,99

B4A code is then like this:

Sub btnCentilesCalculate_Click
	
	Dim iSexType As Int
	Dim iMonths As Int
	Dim dWeight As Double
	Dim dMedian As Double
	Dim strSQL As String
	Dim dGCV As Double 'Generalized Coefficient of Variation
	Dim dZ As Double 'Z-Score
	Dim iCentile As Int
	Dim RS1 As ResultSet
	
	If chkCentilesMale.Checked Then
		iSexType = 1
	Else
		iSexType = 2
	End If
	
	iMonths = edtCentilesMonths.Text
	dWeight = edtCentilesWeight.Text
	
	strSQL = "select M, S from weight_data_m36 " & _
			 "where sex = ? And agemos = ?"
	RS1 = General.cConn.SQL1.ExecQuery2(strSQL, _
	Array As String(iSexType, iMonths))
	RS1.Position = 0
	dMedian = RS1.GetDouble2(0)
	dGCV = RS1.GetDouble2(1)
	
	dZ = Logarithm(dWeight / dMedian, cE) / dGCV
	strSQL = "select centile from zscore2centile " & _
			 "order by Abs(? - z_score) Asc limit 1"
	iCentile = General.cConn.SQL1.ExecQuerySingleResult2(strSQL, _
			   Array As String(dZ))
	
	lblCentile.Text = iCentile

End Sub

RBS

Thank you, these subsequent posting were a lot clearer.

Nice going sorting it out, and thanks for posting, it's always fun to see a mystery solved. :)

The general problem is "interpolation" and "extrapolation".

You need the two data points left and right of the given weight for the specified age. You then assume that the graph is a straight line in between the two data points and compute the percentile coordinate from the weight coordinate.

E.g. for age 3 and weight 8.75 you find (25,8) and (50,9); since 8.75 is 3/4 the way from 8 to 9, the result should be 3/4 of the way from 25 to 50 which is 43.75

In the case of an outlier, you would need to extrapolate using the two closest entries (which both lie on the same side of the given weight) and pretend the graph is a straight line there too.

E.g. for age 3 and weight 11.25 you find (75,10) and (90,11); since 11.25 is 1/4 past 11, the result should be 1/4 the difference between 75 and 90 higher, yielding 93.75.

This would be "linear interpolation" and "linear extrapolation". Adding more data points improves the accuracy of the result. Or you can take advantage of the fact that you are dealing with statistics, which means that the age/weight distribution follows the classical "bell curve". The difference is that it makes the math of interpolation more difficult, but yields better results for a smaller number of input points (median and standard deviation suffice).

This all sorted now and interpolation is not done linear as you can see from the posted formula:
dZ = Logarithm(dWeight / dMedian, cE) / dGCV
so this is using natural logarithm (hence cE) and that will give the typical upwards curving graph of these early (first 36 months) weight charts.

RBS