SOLVED

CKD Epi Formula in an excel formula

Copper Contributor

Hello, does anyone have the formula to calculate CKD EPI which reflects the renal function in medicine in one excel formula, the CKD EPI formula being : 

 

CKD EPI FormulaCKD EPI Formula

 

 

Any help is highly appreciated, thank you in advance 

18 Replies

@joecooper98 

With gender (Male or Female) in B2, Age (in years) in C2, and Scr in E2:

 

=142*(D2/IF(D2<=0.7,0.7,0.9))^IF(B2="Female",-1.2,IF(D2<=0.7,-0.241,-0.302))*0.9938^C2*IF(B2="Female",1.012,1)

 

See the attached workbook.

Thank you for your reply, I really appreciate your help
However, I got the formula from :
https://www.mdcalc.com/ckd-epi-equations-glomerular-filtration-rate-gfr#evidence
And I tried to compare the excel values and the values from the website.
B C F G match in values however the rest of the options seem to have different values
Do you know what might be the reason ?

@joecooper98 

The formula discriminates between Scr<=0.7 and Scr<=0.9, but does not specify what to use if Scr>0.9.

The website states that normal values for Scr are 62-115, and does not accept values below 0.89.

What gives?

Yes Hans, thank your for your reply, actually the values 62-115 refer to the normal serum creatinine in umol/L, however if you click on the parallel green arrows, the normal value shows between 0.7 and 1.3, these values are in mg/dL. And I think the Scr values in the equation are in mg/dL too.
Furthermore, in the table, if I am not mistaken, if Scr > 0.9 A = 0.9 B = -1.2

I hope this reply answers your questions,
Thank you for your help Hans,
If you need anything else, don't hesitate to reach out !

best response confirmed by joecooper98 (Copper Contributor)
Solution
B1 = Scr
B2 = 0 for male, 1 for female
B3 = Age

=IF(B2=0;IF(B1>0.9;142*(B1/0.9)^-1.2*0.9938^B3;142*(B1/0.9)^-.302*0.9938^B3);IF(B1>0.7;142*(B1/0.7)^-1.2*0.9938^B3*1.012;142*(B1/0.7)^-0.241*0.9938^B3*1.012))
B1 = Scr
B2 = 0 for male, 1 for female
B3 = Age

=IF(B2=0;IF(B1>0.9;142*(B1/0.9)^-1.2*0.9938^B3;142*(B1/0.9)^-.302*0.9938^B3);IF(B1>0.7;142*(B1/0.7)^-1.2*0.9938^B3*1.012;142*(B1/0.7)^-0.241*0.9938^B3*1.012))

This solution worked best for me, thank you Hans

@joecooper98 

 

For those that can't get it to work in their version of Excel, replace the semicolons with commas :) Fixed equation is below with same variables

 

B1 = Scr
B2 = 0 for male, 1 for female
B3 = Age

 

=IF(B2=0,IF(B1>0.9,142*(B1/0.9)^-1.2*0.9938^B3,142*(B1/0.9)^-0.302*0.9938^B3),IF(B1>0.7,142*(B1/0.7)^-1.2*0.9938^B3*1.012,142*(B1/0.7)^-0.241*0.9938^B3*1.012))

@HansVogelaar I tried my data on that Excel but it gives wrong results...

That formula doesn't work...

@i_cns 

 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@HansVogelaar 

 

For example, I used your Excel file EPI. Column E has the values according to your formula. Column F has the real values manually calculated according to CKD-EPI 2021 equation.

 

i_cns_0-1703769811417.png

 

@i_cns 

The one who started this discussion, @joecooper98, posted a corrected formula (marked as the solution). I have adjusted it for the layout of the sample workbook.

@HansVogelaar 

 

hi thank you for  your offert may i have this formal but with umol/L  and change 0 to male and 1 to female cux i have a big study for GFR and really need this formal as this site result. 

 https://www.mdcalc.com/calc/3939/ckd-epi-equations-glomerular-filtration-rate-gfr#evidence 

 

 

as an excel sheet thank you

@joecooper98 

To my mind, this is an application that is absolutely crying out for an Excel 365 Lambda function!

CKD.Epi
= LAMBDA(Scr, age, [isFemale],
    LET(
      A, IF(isFemale, 0.7, 0.9),
      B, IF(Src/A <= 1, IF(isFemale, -0.241, -0.302), -1.2),
      142 * (Src/A)^B * 0.9938^age * 1.012^isFemale
    )
  )

The worksheet formula (one for the entire table) is then

= CKD.Epi(Src, Age, Gender="Female")

 

The image didn't come through?

image.png

@Peter Bartholomew

 

thank you for your respond but dose not work for me anymore 

 

for me i want only to change the unit for umol/l , for gender i can fix it later 

@almdll 

I am sorry the solution did not work out for you.  Do you use Excel 365; that is essential.

I am absolutely convinced, however, that the Lambda function offers the only sensible way forward in the longer term.

 

Thank you also for the link; it is good to improve my education even now.  I had rather assumed it was a linear regression model but it was good to be able to confirm it.

@Peter Bartholomew 

thank you for your kind respond 

yes im using 365 Excel 

 

could you find me i way cuz i need it for my big study?

 

thank you  

 

1 best response

Accepted Solutions
best response confirmed by joecooper98 (Copper Contributor)
Solution
B1 = Scr
B2 = 0 for male, 1 for female
B3 = Age

=IF(B2=0;IF(B1>0.9;142*(B1/0.9)^-1.2*0.9938^B3;142*(B1/0.9)^-.302*0.9938^B3);IF(B1>0.7;142*(B1/0.7)^-1.2*0.9938^B3*1.012;142*(B1/0.7)^-0.241*0.9938^B3*1.012))

View solution in original post