CKD Epi Formula in an excel formula

[joecooper98]

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 Formula

Any help is highly appreciated, thank you in advance 

[#2]

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

[#3]

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 ?

[#4]

@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?

[#5]

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 !

[#6 · Accepted 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))

[#7]

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

[#8]

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

[#9]

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

[#10]

That formula doesn't work...

[#11]

@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?

[#12]

@Hans Vogelaar 

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.

[#13]

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

[#14]

@Hans Vogelaar 

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

[#15]

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

[#16]

The image didn't come through?

[#17]

@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 

[#18]

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

[#19]

@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