Help creating formula - Seattle Heart Failure Score

[Azraeliev]

The Seattle Heart Failure Score is calculated by the following:

Seattle HF Score = (age/10)*ln(1.09) + male*ln(1.089) + (NYHA class)*ln(1.6) + (100/(ejection fraction))*ln(1.03) + (if ischemic etiology)*ln(1.354) + (SBP/10)*ln(0.877) + (diuretic dose)*ln(1.178) + (if using allopurinol)*ln(1.571) + (if using statins)*ln(0.63) + (if using ACE inhibitors) + (if using beta-blockers)*ln(0.66) + (if using angiotensin receptor blockers)*ln(0.85) + (if using K-sparing diuretics)*ln(0.74) + (if using biventricular pacemaker) + (if using implantable cardioverter-defibrillator)*ln(0.73) + (if using biventricular implantable cardioverter-defibrillator)*ln(0.79) + (138 – sodium)*ln(1.05) + (100/(total cholesterol, mg/dL))*ln(2.206) + (hgb score) + ((% lymphocytes)/5)*ln(0.897) + (uric acid, mg/dL)*ln(1.064),

where diuretic dose, mg/kg = (furosemide + 2*torsemide + 26.7*bumetanide + 40*metolazone + 3.2*hydrochlorothiazide)/(weight, kg)


If hemoglobin, g/dL <16 g/dL,

hgb score = (16 – hemoglobin)*ln(1.124)

If hemoglobin, g/dL ≥16 g/dL,

hgb score = (hemoglobin – 16)*ln(1.336)

Note:

• Lymphocyte values >47% are set to 47%.
• Uric acid values <3.4 mg/dL are set to 3.4 mg/dL.
• SBP values >160 mm Hg are set to 160 mm Hg.
• Sodium values >138 mEq/L are set to 138 mEq/L.

With so many If's for the life of me I cannot come up with a solution to make it work, please can someone help?

Also I have obne other question, I am trying to use the MAGGIC score for Heart Failure which gives a value to a parameter following the Table 1, and returns a score that relates as is the Table 2

How can I formulate a score cell to lookup the value for each parameter, add them up and fetch from table to the corresponding 1 and 5 year mortality and place it in the approppiate cells?

Table 1

Table 2

Integer risk score	1-year probability of death	3-year probability of death
0	0.015	0.039
1	0.016	0.043
2	0.018	0.048
3	0.020	0.052
4	0.022	0.058
5	0.024	0.063
6	0.027	0.070
7	0.029	0.077
8	0.032	0.084
9	0.036	0.092
10	0.039	0.102
11	0.043	0.111
12	0.048	0.122
13	0.052	0.134
14	0.058	0.146
15	0.063	0.160
16	0.070	0.175
17	0.077	0.191
18	0.084	0.209
19	0.093	0.227
20	0.102	0.247
21	0.111	0.269
22	0.122	0.292
23	0.134	0.316
24	0.147	0.342
25	0.160	0.369
26	0.175	0.397
27	0.191	0.427
28	0.209	0.458
29	0.227	0.490
30	0.248	0.523
31	0.269	0.556
32	0.292	0.590
33	0.316	0.625
34	0.342	0.658
35	0.369	0.692
36	0.398	0.725
37	0.427	0.756
38	0.458	0.787
39	0.490	0.815
40	0.523	0.842
41	0.557	0.866
42	0.591	0.889
43	0.625	0.908
44	0.659	0.926
45	0.692	0.941
46	0.725	0.953
47	0.757	0.964
48	0.787	0.973
49	0.816	0.980
50	0.842	0.985

 

[Peter Bartholomew]

I don't think IFs are needed. You either write out a long formula of the sort

= (age/10)*LN(1.09)
  + (gender="male")*LN(1.089)
  + ischemic*LN(1.354)
  + (SBP/10)*LN(0.877)
  + statins*LN(0.63)

trying not to create errors as you go, or you do a bit of data prep on the worksheet to generate usable arrays

Formula
= age/10
= SIGN(gender="male")
= SIGN(ischemic)
= SBP/10
= SIGN(statins)

with the calculation expressed as an array formula

= SUM(coef * LN(weight))

The lookup to the final table (Table2) can be done with a simple INDEX formula or XLOOKUP would work for non-integer values.

 

[Azraeliev]

I know I tried to do it that way, but something got screwed along the long formula and there's a difference between my formula and the official calculator on MedCalc.

I used
=((E4/10)*LN(1,09))+(D4*LN(1,089))+(M4*LN(1,6))+((100/N4)*LN(1,03))+(P4*LN(1,354))+(((MÍNIMO(O4;160))/10)*LN(0,877))+(((Q4+(40*R4)+(3,2*S4))/H4)*LN(1,178))+((U4*LN(1,571)))+((V4*LN(0,63)))+W4+((Y4*LN(0,66)))+((X4*LN(0,85)))+((Z4*LN(0,74)))+((SE(AA4=1;1;(SE(AA4=2;(1*LN(0,73));(SE(AA4=3;(1*LN(0,79));0)))))))+((138-MÍNIMO(AB4;138))*LN(1,05))+((100/AC4)*LN(2,206))+(Seattle!B2)+(((MÍNIMO(AE4;47))/5)*LN(0,897))+((MÁXIMO(AF4;3,4))*LN(1,064))

Returning 1.31



Where in MedCalc, same values, same units



Is there any way I can send the data on my sheet to that online calculator and it returns the value to my specific table?

 

[Azraeliev]

Nevermind, solved it ->
=((E4/10)*LN(1,09))+(D4*LN(1,089))+(M4*LN(1,6))+((100/N4)*LN(1,03))+(P4*LN(1,354))+(((MÍNIMO(O4;160))/10)*LN(0,877))+(((Q4+(40*R4)+(3,2*S4))/H4)*LN(1,178))+((U4*LN(1,571)))+((V4*LN(0,63)))+((W4*LN(0,77)))+((Y4*LN(0,66)))+((X4*LN(0,85)))+((Z4*LN(0,74)))+((SE(AA4=1;1;(SE(AA4=2;(1*LN(0,73));(SE(AA4=3;(1*LN(0,79));0)))))))+((138-MÍNIMO(AB4;138))*LN(1,05))+((100/AC4)*LN(2,206))+(Seattle!B2)+(((MÍNIMO(AE4;47))/5)*LN(0,897))+((MÁXIMO(AF4;3,4))*LN(1,064))

thanks for the other tip too

 

[Peter Bartholomew]

@Azraeliev, I am glad you have succeeded, it must have taken some persistence!
Some observations would be that your formula is made to appear more complicated by the presence of surplus pairs of parentheses. We are separated by our language settings, but I finished up with something more like

= E4 / 10 * LN(1.09) +
    D4 * LN(1.089) +
    M4 * LN(1.6) +
    100 / N4 * LN(1.03) +
    P4 * LN(1.354) +
    MIN(O4, 160) / 10 * LN(0.877) +
    (Q4 + 40 * R4 + 3.2 * S4) / H4 * LN(1.178) +
    U4 * LN(1.571) +
    V4 * LN(0.63) +
    W4 * LN(0.77) +
    Y4 * LN(0.66) +
    X4 * LN(0.85) +
    Z4 * LN(0.74) +
    IFS(
        AA4 = 1, 1,
        AA4 = 2, LN(0.73),
        AA4 = 3, LN(0.79),
        1,       0
    ) +
    (138 - MIN(AB4, 138)) * LN(1.05) +
    100 / AC4 * LN(2.206) + B2 +
    MAX(AE4, 47) / 5 * LN(0.897) +
    MAX(AF4, 3.4) * LN(1.064)

Alt/Enter is used to separate the lines. I would also tend to use defined names in place of cell references but most users disagree with that practice! Something that may be of interest is the IFS statement that I have used to replace the nested IFs with their multiple parentheses. If you were to try the solution approach that processes each factor individually and then combines them as an array, this logical structure would have to be repeated within both the calculation of the coefficients and of the weights.

The most important thing, though, is do not lose the good work you already have by overwriting it. A formula that works correctly is many times more valuable than one that looks good but does not return correct results!