Develop an Excel formula using the WHO CVD Risk Chart 2019

[Dr Lingkan]

Can anyone develop two separate Excel formulas to calculate cardiovascular risk in an individual? One formula for 'People with diabetes' and another for "People without diabetes'. Here, I have added a chart for these two groups of population. Usually, using this chart I can manually calculate cardiovascular risk. But I want an Excel formula that will automatically calculate the risk. In the chart, each number indicates the risk which can be estimated/calculated using the information: gender (men/women), smoker/nonsmoker, age, systolic blood pressure in mmHg, & total cholesterol level. For example, if a woman has diabetes, is a nonsmoker, is 63 years of age, and has a systolic blood pressure of 140 mmHg & total cholesterol of 6.5 mmol/l, her cardiovascular risk will be 9%. I will manually check the accuracy of the formula using the attached risk chart.

 

[vletm]

Dr Lingkan​

How did You get 9%? ... Isn't it 15?
Here is one formula for both cases...
Make needed selection with drop-downs and You'll see it.
... You can add those values there; that sheet is protected without password.

 

[Dr Lingkan]

Dr Lingkan​

How did You get 9%? ... Isn't it 15?
Here is one formula for both cases...
Make needed selection with drop-downs and You'll see it.
... You can add those values there; that sheet is protected without password.

Yes. It will be 15%. Thank you.

 

[Dr Lingkan]

Dr Lingkan​

How did You get 9%? ... Isn't it 15?
Here is one formula for both cases...
Make needed selection with drop-downs and You'll see it.
... You can add those values there; that sheet is protected without password.

Can't generate result. Is it possible to set equations for those with diabetes and those without?

 

[vletm]

Dr Lingkan​

As written ...
... You can add those values there (from Your PDF); that sheet is protected without password.
After that, You can get Your %
Before that, You can get position of Your %.
Use selections (dropdowns) to select needed selection.
If You've selected 1st selection With diabetes and
after that You'll do selection Without diabetes.
You can see both results.
One people ...can ... or cannot ... have both in same time.

 

[Dr Lingkan]

Dr Lingkan​

As written ...
... You can add those values there (from Your PDF); that sheet is protected without password.
After that, You can get Your %
Before that, You can get position of Your %.
Use selections (dropdowns) to select needed selection.
If You've selected 1st selection With diabetes and
after that You'll do selection Without diabetes.
You can see both results.
One people ...can ... or cannot ... have both in same time.

I have a data set where with other data, age, sex, smoking use, blood pressure and blood cholesterol are recorded. I want to add a column of cardiovascular risk. I want an equation which I want to set in that column, so that the cardiovascular risk will automatically calculated without any manual input. The excel file you have added, I have to manually put the values to calculate risk.

 

[vletm]

Dr Lingkan​

As I have written,
You have to apply those 1400 values to my sample file someway.
I have only possible to do that manually myself.
Even I would do it, You should verify each of those 1400 values manually.

After You have applied those 1400 values, I could do something as You would like to have in Your Book1.xlsx-file.

Please, next time - You should send that kind of Excel-file, which You would like to use at once.

 

[vletm]

Dr Lingkan​

Here next sample based Your Book1.xlsx-file.
Note: There are two sample values in Data-sheet.
After You've added those 1400 real values to Data-sheet,
You'll able to use List-sheet to get Your expected results.
Both sheet has protected (without password).

 

[Dr Lingkan]

Dr Lingkan​

Here next sample based Your Book1.xlsx-file.
Note: There are two sample values in Data-sheet.
After You've added those 1400 real values to Data-sheet,
You'll able to use List-sheet to get Your expected results.
Both sheet has protected (without password).

I can't see the cell of Excel. It looks as attached. Sorry for the ignorance.

 

[vletm]

Dr Lingkan​

There is an another sheet named List as You've given.

Note: Those right side values (99 & 98) are test values that I could verify that my formula works ( finds correct place ):
For Data-sheet, You Dr Lingkan should someway add those 1400 values from Your given Lab_chart.pdf.
( As I've written few times. )
After You have take care Data-sheet, You can start to use List-sheet as You've wished.
What is missing?
What cannot You see?

 

[Monty]

Can anyone develop two separate Excel formulas to calculate cardiovascular risk in an individual? One formula for 'People with diabetes' and another for "People without diabetes'. Here, I have added a chart for these two groups of population. Usually, using this chart I can manually calculate cardiovascular risk. But I want an Excel formula that will automatically calculate the risk. In the chart, each number indicates the risk which can be estimated/calculated using the information: gender (men/women), smoker/nonsmoker, age, systolic blood pressure in mmHg, & total cholesterol level. For example, if a woman has diabetes, is a nonsmoker, is 63 years of age, and has a systolic blood pressure of 140 mmHg & total cholesterol of 6.5 mmol/l, her cardiovascular risk will be 9%. I will manually check the accuracy of the formula using the attached risk chart.

Creating precise cardiovascular risk formulas requires a detailed understanding of the specific risk factors and their corresponding weights in the chart. Since I can't see the chart, I can guide you on creating a formula based on general principles.

Assuming you have columns for Gender, Diabetes Status, Smoking Status, Age, Systolic Blood Pressure, and Total Cholesterol, you could use nested IF statements with appropriate coefficients. Here's a simplified example:

For People with Diabetes:


=IF(AND(Gender="Woman", Diabetes="Yes", Smoking="No", Age>=50, SystolicBP>=140, Cholesterol>=6.5), 9, 0)


For People without Diabetes:


=IF(AND(Gender="Woman", Diabetes="No", Smoking="No", Age>=50, SystolicBP>=140, Cholesterol>=6.5), 5, 0)


This is a basic template. Adjust the conditions and coefficients based on the specifics of your chart. Be cautious with the age range, blood pressure, and cholesterol thresholds, as these might vary in actual risk assessment models.

 

[Dr Lingkan]

Creating precise cardiovascular risk formulas requires a detailed understanding of the specific risk factors and their corresponding weights in the chart. Since I can't see the chart, I can guide you on creating a formula based on general principles.

Assuming you have columns for Gender, Diabetes Status, Smoking Status, Age, Systolic Blood Pressure, and Total Cholesterol, you could use nested IF statements with appropriate coefficients. Here's a simplified example:

For People with Diabetes:


=IF(AND(Gender="Woman", Diabetes="Yes", Smoking="No", Age>=50, SystolicBP>=140, Cholesterol>=6.5), 9, 0)


For People without Diabetes:


=IF(AND(Gender="Woman", Diabetes="No", Smoking="No", Age>=50, SystolicBP>=140, Cholesterol>=6.5), 5, 0)


This is a basic template. Adjust the conditions and coefficients based on the specifics of your chart. Be cautious with the age range, blood pressure, and cholesterol thresholds, as these might vary in actual risk assessment models.

Could you add an excel file with this formula?

 

[vletm]

Dr Lingkan​

You asked based #11 reply that formula.
... if You've an idea to use it, You'll need after that still 1399 formulas.

 

[p45cal]

It's far from easy to reverse engineer such tables to create a formula. I wouldn't like to do it; I'd search out an algorithm on the interweb first.
Instead, in the attached is a single table created on Sheet1 (with the help of a macro) from the pdf file where each of the 1400 values is on its own row, then that table is used to lookup the values in columns A to F of Sheet2 (formula in column G). So far, cursory checks have been OK.

 

[vletm]

Dr Lingkan​

Here is sample which will work will many Excel-versions.
You can verify/compare Your values with same kind of layout than Your given PDF-file.