Forum Discussion

joecooper98's avatar
joecooper98
Copper Contributor
Jun 12, 2022

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 : 

 

CKD EPI Formula

 

 

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))
  • 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.

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        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?

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

  • joecooper98's avatar
    joecooper98
    Copper Contributor
    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))
    • KoryH81's avatar
      KoryH81
      Copper Contributor

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

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

     

    • almdll's avatar
      almdll
      Copper Contributor

      PeterBartholomew1

       

      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 

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor

        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.

Resources