Forum Discussion
CKD Epi Formula in an excel formula
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 :
Any help is highly appreciated, thank you in advance
- 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))
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.
- joecooper98Copper Contributor
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 ?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?
- i_cnsCopper Contributor
HansVogelaar I tried my data on that Excel but it gives wrong results...
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?
- joecooper98Copper ContributorB1 = 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))- KoryH81Copper Contributor
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))
- i_cnsCopper ContributorThat formula doesn't work...
- PeterBartholomew1Silver Contributor
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")
- PeterBartholomew1Silver Contributor
The image didn't come through?
- almdllCopper Contributor
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
- PeterBartholomew1Silver Contributor
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.